You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
When running a apoc.load.jdbcUpdate operation as the second statement in an apoc.periodic.iterate, the connections to the database (postgres) should be closed after they have been executed.
Actual Behavior (Mandatory)
It does not close the connections, and it leaves an idle connection for each statement. When the postgres database reaches max connections the statement fails.
How to Reproduce the Problem
Steps (Mandatory)
Create a table in a postgres database: CREATE TABLE nodes ( id serial PRIMARY KEY, my_id integer );
In you neo4j database, create some nodes: WITH range(0, 100) as list UNWIND list as l CREATE (n:MyNode{id: l})
Load the postgres driver: CALL apoc.load.driver("org.postgresql.Driver")
Try to use apoc.period.iterate to insert into the postgres table:
CALL apoc.periodic.iterate("MATCH (n:MyNode) return n", "WITH n, apoc.text.format('insert into nodes (my_id) values (\\\'%d\\\')',[n.id]) AS sql CALL apoc.load.jdbcUpdate('jdbc:postgresql://my_user:my_password@localhost:5432/my_database',sql) YIELD row AS row2 return row2,n", {batchsize: 10,parallel: true}) yield batches,total,timeTaken,committedOperations,failedOperations,failedBatches,retries,errorMessages, operations, failedParams, updateStatistics return batches,total,timeTaken, committedOperations,failedOperations,failedBatches,retries,errorMessages,operations,failedParams, updateStatistics
Check postgres for connections:
select client_addr, state from pg_stat_activity to see the hanging connections from the neo4j server. They can be killed with select pg_terminate_backend(pid) from pg_stat_activity where state = 'idle' but a pg_cancel_backend will not work.
NOTE: running the same experiment on neo4j 5.18 works fine, it 5.19 that introduces this problem.
Specifications (Mandatory)
Currently used versions
Versions
OS: Ubuntu 22.04.4
Neo4j: 5.19.0
Neo4j-Apoc: 5.19.0
pg driver: postgresql-42.7.3.jar
Postgres version: 16.2
The text was updated successfully, but these errors were encountered:
The problem does not seem to be caused by apoc.periodic.iterate, in fact even doing apoc.load.jdbcUpdate directly many times, idle connections are left.
However it would seem not to be an apoc problem, as PreparedStatement.close() executes correctly without errors, rather it could depend on Postgres itself leaving connections idle for a certain amount of time, as mentioned here and here.
Even with the latest JDBC driver version, the problem still seems to be present.
Please feel free to reopen the issue, if you have more info to share.
The problem isn't related to the apoc.load.csv one.
Expected Behavior (Mandatory)
When running a apoc.load.jdbcUpdate operation as the second statement in an apoc.periodic.iterate, the connections to the database (postgres) should be closed after they have been executed.
Actual Behavior (Mandatory)
It does not close the connections, and it leaves an idle connection for each statement. When the postgres database reaches max connections the statement fails.
How to Reproduce the Problem
Steps (Mandatory)
Create a table in a postgres database:
CREATE TABLE nodes ( id serial PRIMARY KEY, my_id integer );
In you neo4j database, create some nodes:
WITH range(0, 100) as list UNWIND list as l CREATE (n:MyNode{id: l})
Load the postgres driver:
CALL apoc.load.driver("org.postgresql.Driver")
Try to use apoc.period.iterate to insert into the postgres table:
CALL apoc.periodic.iterate("MATCH (n:MyNode) return n", "WITH n, apoc.text.format('insert into nodes (my_id) values (\\\'%d\\\')',[n.id]) AS sql CALL apoc.load.jdbcUpdate('jdbc:postgresql://my_user:my_password@localhost:5432/my_database',sql) YIELD row AS row2 return row2,n", {batchsize: 10,parallel: true}) yield batches,total,timeTaken,committedOperations,failedOperations,failedBatches,retries,errorMessages, operations, failedParams, updateStatistics return batches,total,timeTaken, committedOperations,failedOperations,failedBatches,retries,errorMessages,operations,failedParams, updateStatistics
select client_addr, state from pg_stat_activity
to see the hanging connections from the neo4j server. They can be killed withselect pg_terminate_backend(pid) from pg_stat_activity where state = 'idle'
but apg_cancel_backend
will not work.NOTE: running the same experiment on neo4j 5.18 works fine, it 5.19 that introduces this problem.
Specifications (Mandatory)
Currently used versions
Versions
The text was updated successfully, but these errors were encountered: