I am connecting to a Postgre SQL server and running a custom SQL statement. Using KNIME 4.6.3, PostgreSQL connector and DB Query Reader node. The DB query just keeps running, never failing or completing. No error messages. I can run the exact same custom SQL in dBeaver, Excel or Tableau Prep in about 5 minutes and it pulls down ~700,000 lines.
I have also tried the DB SQL Executor node and the DB Table Selector node to run the query and am having the same issue. Is there some other setting that I need to check in the connection or query reader?
If you attach a different DB Query Reader node to your postgres connector, such as a query to simply return current date, does it execute successfully?
If you hover the mouse over the “status bar” of the executing DB Query Reader node (ie the bar just beneath the node that shows either the red/yellow/green or the “processing animation”), what does it say? (e.g. “executing”, or “n rows retrieved”…?)
Yes other nodes run/complete from the same postgres connector. The status bar just keeps saying executing and it runs until I stop it. I let it run for about 12 hours lastnight and it was still executing when I checked it this morning.
That is very odd. Unfortunately I’m not that familiar with postgres, but I’ll ask some more questions anyway if I may, in the hope it might lead us (or somebody) to some more ideas!
Does the query have an order by clause in it?
If you limit the amount of data to be returned to, say, 100 rows (e.g. adding a clause such as LIMIT 100 , or fetch first 100 rows only), does it return? And if so, what if you increase the limit to 1000, 10000 and so on?
Having stopped the db query reader node, have you then reset the PostgreSQL Connector node before trying to execute again? (I have Oracle connectors where I find if I leave them “connected” for too long, Oracle chops the connection invisibly behind the scenes (I’m guessing) and a subsequent use of a DB Reader on that connector exhibits “execute forever-until killed” behaviour).