ReadWriteSplittingPlugin does not close the reader connection

[firebike]

Describe the bug

When ReadWriteSplittingPlugin switches back to the writer connection, the reader connection is still held and is not being closed. In effect, the reader connection is permanently bound to the writer connection and is not returned to the internal reader connection pool.

Expected Behavior

ReadWriteSplittingPlugin closes the reader connection after switching back to the read-write mode.

What plugins are used? What other connection properties were set?

awsSecretsManager,readWriteSplitting,failover,efm

Current Behavior

Our application has started reporting OutOfMemoryErrors. Heap dump analysis shown that the memory has been occupied by PGConnection instances, in particular by PSQLWarnings accumulated by the reader connections.

When an aws-wrapper jdbc connection is switched from write to read mode, the backing internal reader connection is initialized using the following SQL statement SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED

Aurora executes the statement but also returns a warning unrecognized node type: 378:

[2023-07-12 13:26:08] manual transaction mode ON
[2023-07-12 13:26:16] Connected
postgres.public> SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
[2023-07-12 13:26:16] [01000] unrecognized node type: 378
[2023-07-12 13:26:16] completed in 201 ms

These warnings are kept by the PGConnection.

Reproduction Steps

In our project we name the Hikari connection pools core-writer and core-reader.
We use internal connection pooling

        HikariPooledConnectionProvider connectionProvider = new HikariPooledConnectionProvider(
                (hostSpec, originalProps) -> getHikariConfigForDriverInternalReaderConnectionPool(originalProps));
        ConnectionProviderManager.setConnectionProvider(connectionProvider);

Here are the stats of our Hikari pools after a long running stimulus followed by a cool-down period:

DEBUG com.zaxxer.hikari.pool.HikariPool - core-writer - After cleanup  stats (total=18, active=0, idle=18, waiting=0) - {} 
DEBUG com.zaxxer.hikari.pool.HikariPool - core-reader - After cleanup  stats (total=27, active=17, idle=10, waiting=0) - {}

It’s visible, that the reader connections were not returned to the pool.

Possible Solution

Close the reader connection after switching back to the writer connection.

1. Without internal connection pool: The PGConnection instance memory will be recycled.
2. With internal connection pool: HikariCP cleans up the SQL warnings once the connection is returned to the pool.

I've patched the ReadWriteSplittingPlugin

With that change in place the reader connections are properly returned to the pool

DEBUG com.zaxxer.hikari.pool.HikariPool - core-reader - After cleanup  stats (total=19, active=0, idle=19, waiting=0) - {}

Additional Information/Context

We use Aurora for Postgres version 14.7

The AWS Advanced JDBC Driver version used

2.1.2, 2.2.2

JDK version used

17.0.7

Operating System and version

Linux pop-os 6.2.6-76060206-generic

[crystall-bitquill]

Hi @firebike,

Thanks for reaching out and raising this issue, as well as for creating a PR.

We'll take a look at this and keep you updated as we investigate and review your proposed changes.

Thank you for your patience!

[crystall-bitquill]

Hi @firebike,

I've left a suggestion on your PR.

Also, regarding the warning message you've been getting about the unrecognized node type, it seems to have been an issue with Aurora PostgreSQL, in at least version 14.7. It was addressed in versions 14.8 and 15.3.

[crystall-bitquill]

Hi @firebike,

We've merged your PR and the fix is now available in the latest snapshot build. To confirm, did the fix resolve the out of memory errors, or does the error persist?

[firebike]

Hi @crystall-bitquill I'm on vacation now. @datenbrille could you please answer this question?

[crystall-bitquill]

Closing this ticket as the fix has been confirmed to work in this comment.