Uncaught exception in an afterCommit callback with a transaction causes org.postgresql.util.PSQLException: Cannot rollback when autoCommit is enabled

[maxqch]

Issue

The problem happens after the following sequence of events

• Start a transaction (txn_a)
• Register an afterCommit callback that runs in a transaction (txn_b)
• Do some work in txn_a, commit
• txn_b executes as a part of afterCommit, but throws an exception
• txn_b is rolled back. as a part of the rollback, LocalTransactionHandler#restoreAutoCommitState is called which sets autoCommit to true
• txn_b's exception is propagated, and caught by txn_a (LocalTransactionHandler#inTransaction)
• txn_a attempts to roll back (even though it is already committed!), and fails because autoCommit is now true

It seems, at least in our code, that things do end up working out okay after this. The exception is suppressed, the rollback doesn't actually happen, and we do get the afterCommit exception. However it still seems not ideal, and perhaps there are some other scenarios / settings where this may cause more problems.

Thoughts on a fix: possibly add a didTxnCommit flag, and set that to true after the commit but before afterCommit hooks. Then check for that flag in the catch before calling rollback, skipping the rollback if we've already committed?

Exception

	Suppressed: org.jdbi.v3.core.transaction.TransactionException: Failed to rollback transaction
		at org.jdbi.v3.core.transaction.LocalTransactionHandler$BoundLocalTransactionHandler.rollback(LocalTransactionHandler.java:143)
		at org.jdbi.v3.core.Handle.rollback(Handle.java:566)
		at org.jdbi.v3.core.transaction.LocalTransactionHandler$BoundLocalTransactionHandler.inTransaction(LocalTransactionHandler.java:218)
		... 5 more
	Caused by: org.postgresql.util.PSQLException: Cannot rollback when autoCommit is enabled.
		at org.postgresql.jdbc.PgConnection.rollback(PgConnection.java:1003)
		at com.zaxxer.hikari.pool.ProxyConnection.rollback(ProxyConnection.java:385)
		at com.zaxxer.hikari.pool.HikariProxyConnection.rollback(HikariProxyConnection.java)
		at org.jdbi.v3.core.transaction.LocalTransactionHandler$BoundLocalTransactionHandler.rollback(LocalTransactionHandler.java:141)
		... 7 more

Example code

import javax.sql.DataSource;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.jdbi.v3.core.Handle;
import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.core.result.ResultIterable;

/**
 * Very simple table:
 * <pre>
 *      create table jdbi_test (
 *          foo varchar not null,
 *          bar varchar not null
 *      );
 * </pre>
 *
 * <pre>
 *      postgres=# \d jdbi_test;
 *      Table "public.jdbi_test"
 *      Column |       Type        | Collation | Nullable | Default
 *      --------+-------------------+-----------+----------+---------
 *      foo    | character varying |           | not null |
 *      bar    | character varying |           | not null |
 * </pre>
 */
public class JdbiTest {
    private static final String k = "k";
    private static final String v1 = "v1";
    private static final String v2 = "v2";

    public static void main(String[] args) {
        final Jdbi jdbi = Jdbi.create(getDatasource());

        jdbi.inTransaction(handle -> {
            handle.execute("truncate table jdbi_test");
            return null;
        });

        jdbi.inTransaction(handle -> {
            handle.afterCommit(() -> doAfterCommit(handle));
            handle.execute("insert into jdbi_test values (?, ?)", k, v1);

            // Contrived example of an exception thrown by afterCommit, which causes a rollback
            // in its transaction.
            //
            // The rollback calls restoreAutoCommitState and sets autoCommit to true on the handle
            // The exception is then caught by LocalTransactionHandler#inTransaction line 216.
            // Even though the original transaction has already committed, a rollback is attempted.
            // However, now autoCommit is now true, and we get a suppressed exception:
            //    org.postgresql.util.PSQLException: Cannot rollback when autoCommit is enabled.
            handle.execute("insert into jdbi_test values (?, ?)", k, v2);
            return null;
        });
    }

    private static void doAfterCommit(final Handle handle) {
        handle.inTransaction(innerHandle -> {
            final ResultIterable<String> result = innerHandle.select("select bar from jdbi_test where foo = ?", k)
                    .mapTo(String.class);
            if (result.stream().anyMatch(s -> s.equals(v2))) {
                throw new RuntimeException("Oh no");
            }
            return null;
        });
    }

    private static DataSource getDatasource() {
        final var cfg = new HikariConfig();
        cfg.setSchema("public");
        cfg.setJdbcUrl("jdbc:postgresql://localhost:5432/postgres");
        return new HikariDataSource(cfg);
    }
}