Postgres Connection left in OPEN transaction when autoCommit=false and maxLifetime is reached

[kevinchan-rl]

Hi,

We noticed our connection pool becomes unusable after maxLifetime has been reached with runtime exception PSQLException: Cannot change transaction isolation level in the middle of a transaction when trying to set the isolation level the first time after getting the connection from the datasource. We are using exposed 0.51 in kotlin.

We have autoCommit=false and default schema set on the hikari datasource.

Attached are the postgres logs for when autoCommit=false and autoCommit=true and you can see the connection init for both the first time and when maxLifetime is reached. Notice that the transaction is left open when autoCommit=false when maxLifetime is reached.

[kevinchan-rl]

auto_commit_false.txt
auto_commit_true.txt

[kevinchan-rl]

potentially a duplicate of #2144

[quaff]

potentially a duplicate of #2144

It's a duplicate and should be fixed at PostgreSQL JDBC Driver side, see pgjdbc/pgjdbc#3005

[brettwooldridge]

This is disturbing. I do suspect it is caused by the referenced pgjdbc issue. I'm not sure what the pool can do to compensate for this behavior; I don't want to automatically Connection.commit() every time the connection is returned to the pool or the schema is changed.

@kevinchan-rl Can you post your pool configuration?