Postgre Database Apply Query Changes Error

[sherryliu]

Hi All,

I finanlly had a successful connection to Postgre, and when I load data I got this. I can see the preview with data fine. Not sure why I am getting this error. Can someone help? Thanks

[marks17]

I have hit the same bug when starting work with PowerBI/Postgres yesterday - it seems to have been introduced in Npgsql-3.2.3.  I can use Npgsql-3.2.2 just fine - so downgrade for now I guess?

[v-qiuyu-msft]

Hi @sherryliu,

1. Did you connect to PostgreSQL use built-in PostgreSQL data source in Power BI desktop?

2. Which desktop version did you run? Please try the latest version 2.48.4792.721 64-bit (July 2017).

3. Which steps did you perform in Query Editor? Please share detail information so we can try to test it.

Best Regards,
Qiuyun Yu

[jeffwinchell]

Power BI's lack of TESTED, WORKING MICROSOFT PROVIDED software to connect to PostgreSQL is PATHETIC.

PostgreSQL is the FOURTH most popular DBMS on the planet. There is NO EXCUSE for this pathetic "support" from Microsoft.

I say that as a database industry veteran of 3 decades working for database vendors, media companies, large companies' IT organizations and startups. I can't believe how crappy this is just to get a hello world level Power BI visualization using PostgreSQL. 

SSRS can do it. Tableau can do it. But Microsoft's wunderkind is spending too much time on nonsense like inverted pyramids rather than basic functionality to significant customer bases.

Time to escalate this to sr mgmt because I'm sure they have no idea what their underlings are messing up.

[sherryliu]

Hi Qiuyun,

1. I used built-in PostgreSQL database connector to connect a local postgre database

2. I am currently running 64 bit July release PBI desktop

3. I can preview the table without any issue. However, once I clicked on "Load" in data navigator screen or (if I chose edit in data navigator) then when hit  "Close &Apply" , it will give me an error specified cast is not valid. I tried tables with data and without, it did not make any difference 

4. I tried to create a new database in postgre. Created one test table with one test column as numeric and one row of data. Failed

Feedback Type:
Frown (Error)

Timestamp:
2017-07-26T13:41:39.4123673Z

Local Time:
2017-07-26T09:41:39.4123673-04:00

Product Version:
2.48.4792.721 (PBIDesktop) (x64)

Release:
July 2017

IE Version:
11.0.9600.18698

OS Version:
Microsoft Windows NT 6.3.9600.0 (x64 en-US)

CLR Version:
4.6.1 or later [Release Number = 394271]

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Peak Working Set:
444 MB

Private Memory:
286 MB

Peak Virtual Memory:
33.6 GB

Error Message:
Specified cast is not valid.

Stack Trace:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Specified cast is not valid. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Specified cast is not valid. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Specified cast is not valid. ---> System.InvalidCastException: Specified cast is not valid. ---> System.InvalidCastException: Specified cast is not valid.
at Microsoft.OleDb.Serialization.PageWriter.WriteSchema(DataTable schemaTable)
at Microsoft.Mashup.Evaluator.RemotePageReader.<>c__DisplayClass7.<RunStub>b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass7.<TryReportException>b__6()
at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemotePageReader.RunStub(IEngineHost engineHost, IMessageChannel channel, Func`1 getPageReader)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass1e`1.<OnBeginGetResult>b__1b()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetDataReaderSource(IMessageChannel channel, BeginGetDataReaderSourceMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.MessageBasedInputStream.ReadNextChunkAndCheckIfClosed()
at Microsoft.Mashup.Evaluator.MessageBasedInputStream.ReadNextChunk()
at Microsoft.Mashup.Evaluator.ChunkedInputStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.BinaryReader.FillBuffer(Int32 numBytes)
at System.IO.BinaryReader.ReadInt32()
at Microsoft.Mashup.Evaluator.ITableSourceSerializationExtensions.ReadITableSource(BinaryReader reader)
at Microsoft.Mashup.Evaluator.RemotePageReader.CreateProxy(IEngineHost engineHost, IMessageChannel channel, ExceptionHandler exceptionHandler)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.DataReaderSourceRemoteEvaluation.GetResult(Boolean enableFirewall)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation.Evaluate(Boolean enableFirewall)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation`1.TryCompleteWithException(Exception exception)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation.Evaluate(Boolean enableFirewall)
at Microsoft.Mashup.Evaluator.EvaluatorThreadPool.EvaluatorThread(Object state)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart(Object obj)
--- End of inner exception stack trace ---

Server stack trace:

Exception rethrown at [0]:
at Microsoft.OleDb.AsyncResult`1.get_Value()
at Microsoft.Mashup.OleDbProvider.DataHost.NonBufferingMashupEvaluator.MashupEvaluation.OnEvaluationComplete(AsyncResult`1 result)

Exception rethrown at [1]:
at Microsoft.OleDb.AsyncResult`1.get_Value()
at Microsoft.Mashup.OleDbProvider.MashupRowset.get__Rowset()
at Microsoft.OleDb.Rowset.Microsoft.OleDb.IRowset.AddRefRows(DBCOUNTITEM cRows, HROW* nativeRows, UInt32* nativeRefCounts, DBROWSTATUS* nativeRowStatus)
at Microsoft.Mashup.OleDbProvider.TracingRowset.Microsoft.OleDb.IRowset.AddRefRows(DBCOUNTITEM cRows, HROW* nativeRows, UInt32* nativeRefCounts, DBROWSTATUS* nativeRowStatus)

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.ClientShared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace)
at Microsoft.Mashup.Client.ClientShared.FeedbackErrorInfo..ctor(String message, Nullable`1 errorCode, String requestId, Exception exception)
at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass3.<SendAndMarshalExceptions>b__0(Object null)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.ClientShared.WindowManager.ShowDialog[T](T form, IWin32Window owner)
at Microsoft.Mashup.Client.ClientShared.Ux.FloatingDialog.FloatingDialog.ShowDialogWithTimeout(IWin32Window owner, Nullable`1 showTimeout)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.StartEvaluationAndShowDialog(IEnumerable`1 queriesToLoad, Nullable`1 showTimeout, Boolean requireFullDataRefresh)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.TryShowDialogForQueries(Report report, IWin32Window ownerWindow, WindowsHost windowsHost, IQueryServices queryServices, IEnumerable`1 queriesToLoad, LocalizedString title, Boolean requireFullDataRefresh)
at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.LoadQueries(IEnumerable`1 queriesToLoad)
at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.OnImportSuccess(IEnumerable`1 addedQueries, DataImportDestination destination)
at Microsoft.Mashup.Client.ClientShared.DataImporter.ProcessImportedQueries(IEnumerable`1 importedQueries, DataImportDestination destination)
at Microsoft.Mashup.Client.ClientShared.DataImporter.ImportNavigationSource(Query query)
at Microsoft.Mashup.Client.ClientShared.DataImporter.OnGetPreviewResult(PreviewResult preview, Query query, String sourceID, String formulaTitle, Nullable`1 explicitImportDestination, Boolean isNewQuery, Boolean isFromEditor)
at Microsoft.Mashup.Client.ClientShared.DataImporter.<>c__DisplayClass1b.<OnQuerySettingsResolved>b__18()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.ClientShared.WindowManager.ShowDialog[T](T form, IWin32Window owner)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClassd.<Main>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

User ID:
ed841862-003c-462c-b79d-816d389e9131

Session ID:
7827b53a-53fd-4e38-bb7a-a6e8e2c1e930

Telemetry Enabled:
True

Model Default Mode:
Import

Snapshot Trace Logs:
C:\Users\xliu\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot553622787.zip

Performance Trace Logs:
C:\Users\xliu\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_numericSlicerEnabled
PBI_SpanishLinguisticsEnabled
PBI_PbiServiceLiveConnect
PBI_daxTemplatesEnabled
CustomConnectors

Enabled DirectQuery Options:
PBI_DirectQuery_Unrestricted

Cloud:
GlobalCloud

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

section Section1;

shared #"public userpickerfilterrole" = let
Source = PostgreSQL.Database("jira", "jiradb"),
public_userpickerfilterrole = Source{[Schema="public",Item="userpickerfilterrole"]}[Data]
in
public_userpickerfilterrole;

[v-qiuyu-msft]

Hi @sherryliu,

Which Npgsql provider version did you run? I installed Npgsql version 3.1.8 and I can import data to Power BI desktop whether click Load or Edit-> Apply and Close. Please try to run the same provider as ours to see if the issue related to Npgsql provider.

If not, please enable desktop trace log files, then repeat the steps to reproduce the issue and share log with us.

@marks17 Did you get the exactly same issue as @sherryliu?

Best Regards,
Qiuyun Yu

[jeffwinchell]

NPGSQL should be a MICROSOFT product! Why are you relying on unpaid volunteers to connect Microsoft's most important BI product to the 4th most popular database in the world??? Would any program manager be still working in that group if they made DB2 users use some shareware from a few unpaid volunteers (and definitely without a blue badge)?

[marks17]

---

@marks17 Did you get the exactly same issue as @sherryliu?

---

@v-qiuyu-msftYes, exactly the same failure.  I tried 3.1.9 and a few other versions and the last working one was 3.2.2.  3.2.3 - 3.2.5 all fail in exactly the same way on my very simple dataset (date/time with multiple numerical values against each row, with 200k+ rows).  I am connecting to Postgres 9.6.3.

Regards,

MarkS

[sherryliu · Accepted solution]

I even tried to migrate jira database (postgresql) to Azure sql but failed. Azure sql has certain limitations in creating database admin account. Plus, Atlassian will not support Azure. Finally I looked back and figured this out. 

1. Edit queries before load 

2. Select each table and all columns, remove errors

3. Load without issues. 

Hopefully, this works for you.