DB Writer opening too many sessions ?

Hello,

My execution of several knime workflows has the unpleasant effect of crashing my postgres database (“too many clients already”). I do use the “db connection closer” node after each insert or writer node ; yet I have the impression that more sessions are opened each time than just the number of db connection nodes.

Could it be that Knime opens a new session for each batch ? Why wouldn’t they be closed by the connection closer ? Is it something else ? Anyone with the same experience here ?

Thanks !

Hello mpranchere,
each DB Connector node opens a single connection to the database which gets closed once the workflow is closed or the DB Connection Closer node is executed. All other DB nodes use the connection provided by the DB Connector node. However if you execute a workflow with one DB Connector node in parallel each of the executed workflows will acquire its own connection to the database. Also we do use the JDBC API to close the connection by calling the corresponding close method depending on the operation that has been performed in the DB the actual closing of the session might take some time e.g. for the db to perform some cleanup.
Bye
Tobias

2 Likes

Thank you for your answer, Tobias. Although I do know that the DB Connector is supposed to open a single connection, I have observed, by monitoring the number of sessions opened on postgres during the execution of a single workflow, that much more sessions where opened by Knime than the number of DB connector node.

I.e wokflows with 2-3 DB Connector nodes happen to open like 20 or 30 sessions on postgresql. And I do not run them in parallel : they are executed sequentially by looping on a “Call workflow” node. Although when I close a Knime workflow executed “traditionnally”, these sessions do get closed, when executed from a “call workflow” node they do not, hence my saturation problem.

Do you really have no idea about what could cause these multiple sessions to open ?

Hello mpranchere,
I’m sorry to hear this. I’m 100% certain that the DB Connector nodes only opens one connection via the appropriate JDBC command. However this might cause several sessions to be opened in Postgres but I need to investigate this in detail. To do so can you please give me the following information so that I can try to reproduce the problem:

  • KNIME AP version
  • Postgres version
  • JDBC driver version
  • JDBC parameter
  • What kind of statements you execute e.g. SELECT, INSERT, UPDATE, etc.

Regarding the problem with the Call workflow node, does the node execute the workflow locally or on a KNIME Server? I assume you use the Call Workflow (Table Based node) right? Could you send a screenshot of the node dialog or let me know which settings you were using. I have a hunch that this might be related to when a job gets disposed on the KNIME Server which ultimately triggers the connection closing.
If you do not want to share all this information publicly in the forum let me know and I will contact you via private message.

Thanks for your help.
Bye
Tobias

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.