Refresh error root cause identification (postgres connector)

[zenisekd]

Hi, 

Could anybody help me out with some advanced advice on how to track down the root cause of my error in refresh. I have spent a lot of time on this and still no result. 

I am connecting a postgres db, my power bi file is 80mb, so nothing bing and no unusual connector. 

Over the time I keep receiving errors of:
Data source error: DataSource.Error: An error happened while reading data from the provider: 'Connection is not open'. DataSourceKind = PostgreSQL. DataSourcePath = ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo. . The exception was raised by the IDbCommand interface. Table: public mrp_bom_line.

and
Data source error: DataSource.Error: PostgreSQL: Exception while reading from stream. DataSourceKind = PostgreSQL. DataSourcePath = ecoprodukt-main-db-ro.rds.orgis.tools:5432;odoo. Message = Exception while reading from stream. ErrorCode = -2147467259. . The exception was raised by the IDbCommand interface. Table: public account_invoice.

 

However, the tables referenced by the errors vary. I get these errors occasionally, sometimes more often, sometimes days go by without it appearing. 

I did check all the columns and relations if there is any mistype or anything else, but the error keeps appearing. When I asked my DB guy, he says that the Power BI disconnects during the refresh, according to logs, not the other way around. 

I also tried to use the "analyze my refresh" tool made by Phil Seamark https://dax.tips/2021/02/15/visualise-your-power-bi-refresh/

There are some more demanding queries, which take longer time. I did my best to optimize it for quer...

I am honestly out of ideas, about what causes the errors. Could somebody advise me on how to approac...

[lbendlin]

Create a proof of concept.  New PBIX, single table connection to the PostGreSQL database. Refresh this a couple dozen times.  If no error, choose a larger table/more complex query. Rinse and repeat.

Also - play with the Command timeout parameter.  But ideally your queries should start returning results immediately.

Power Query PostgreSQL connector - Power Query | Microsoft Learn