apoc.load.jdbcUpdate inside apoc.periodic.iterate leaves idle connections from 5.19 and forward

[nielsjansendk]

Guidelines

Expected Behavior (Mandatory)

This is issue #4074, but since I cannot reopen it, I am opening a new issue.

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.

This specifically happened when 5.19 was released. It still works in 5.18.

The previous PR suggested that this was a driver problem and not a problem with apoc. But changing the postgres driver doesn't do anything: in 5.18 everything works fine and from 5.19 onwards it does not work. So a change that breaks this functionality was introduced in 5.19. It should be possible to isolate the breaking change.

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)

1. Create a table in a postgres database:
   CREATE TABLE nodes ( id serial PRIMARY KEY, my_id integer );
2. In you neo4j database, create some nodes:
   WITH range(0, 100) as list UNWIND list as l CREATE (n:MyNode{id: l})
3. Load the postgres driver:
   CALL apoc.load.driver("org.postgresql.Driver")
4. 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
5. 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.

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