4

Question

The org.postgresql JDBC driver for Postgres has a new release for JDBC 4.2 (JEP 170). The old package with classes for dynamically determining the state of a Connection’s transaction has disappeared. What to use now?

Background

Strange but true, JDBC has no way to identify the state of a transaction. So for example, when getting a Connection from a pool, you cannot verify if there is a pending transaction left hanging from previous usage.

As a workaround, you can call on Postgres-specific methods in the JDBC driver to determine if the state is idle (no txn), open (pending txn), or failed (error in txn).

In the previous version, 9.4-1201-jdbc41 (note the 41 vs 42, meaning JDBC 4.2), you could write code like this.

if ( conn instanceof org.postgresql.jdbc2.AbstractJdbc2Connection ) {
    // Cast from a generalized JDBC Connection to one specific to our expected Postgres JDBC driver.
    org.postgresql.jdbc2.AbstractJdbc2Connection aj2c = ( org.postgresql.jdbc2.AbstractJdbc2Connection ) conn; // Cast to our Postgres-specific Connection.

    // This `getTransactionState` method is specific to the Postgres JDBC driver, not general JDBC.
    int txnState = aj2c.getTransactionState();
    // We compare that state’s `int` value by comparing to constants defined in this source code:
    // https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/core/ProtocolConnection.java#L27
    switch ( txnState ) {
        case org.postgresql.core.ProtocolConnection.TRANSACTION_IDLE:
            stateEnum = DatabaseHelper.TransactionState.IDLE;
            break;

        case org.postgresql.core.ProtocolConnection.TRANSACTION_OPEN:
            stateEnum = DatabaseHelper.TransactionState.OPEN;
            break;

        case org.postgresql.core.ProtocolConnection.TRANSACTION_FAILED:
            stateEnum = DatabaseHelper.TransactionState.FAILED;
            break;

        default:
            // No code needed.
            // Go with return value having defaulted to null.
            break;
    }
} else {
    logger.error( "The 'transactionStateOfConnection' method was passed Connection that was not an instance of org.postgresql.jdbc2.AbstractJdbc2Connection. Perhaps some unexpected JDBC driver is in use. Message # 47a076b1-ba44-49c7-b677-d30d76367d7c." );
    return null;
}

The current driver is JDBC42 Postgresql Driver, Version 9.4-1203. In this new driver the org.postgresql.jdbc2 package seems to have gone away. What is the replacement?

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • You shouldn't go poking around in the innards of the driver's internal implementation like that. Use undocumented internal methods, your code breaks on upgrade. This is expected. What's the problem you're trying to solve with this? You shouldn't need to care if there's a dangling transaction because (a) there shouldn't be one, that's a bug in the app, and (b) that's why you use `DISCARD ALL` when handing connections back to a connection pool after use. – Craig Ringer Sep 23 '15 at 00:17
  • If you really *must* abuse the internal methods, the jdbc2 package was folded into the jdbc3 package when support for jdbc2 was removed. – Craig Ringer Sep 23 '15 at 00:23
  • @CraigRinger Thanks for your assistance. As for (a), how is one to find these bugs if not by examining the state of the transaction? As for (b), the [doc](http://www.postgresql.org/docs/current/static/sql-discard.html) says `DISCARD ALL` “cannot be executed inside a transaction block”. Simon Riggs pointed out this conundrum back in [this discussion in year 2007](http://grokbase.com/t/postgresql/pgsql-hackers/07a4pb85sp/connection-pools-and-discard-all). There Neil Conway even suggests “just check what the transaction status of the connection is”, the same as I am trying to do. – Basil Bourque Sep 23 '15 at 02:02
  • Good point re `DISCARD ALL`. I was mistaken in thinking that it aborted an open transaction if one was present. – Craig Ringer Sep 23 '15 at 02:25
  • Any suggestions or workarounds? This seems like such a basic need that I'm surprised there is no direct facility in SQL/JDBC/Postgres. – Basil Bourque Sep 23 '15 at 02:45
  • I have been using `select count(*) from pg_locks where pid = pg_backend_pid() and locktype = 'transactionid' and mode = 'ExclusiveLock'` without problems for quite some time now (if the count is > 0, then there is an open transaction) –  Sep 23 '15 at 05:59
  • @a_horse_with_no_name Perhaps you should make your Comment an Answer here. – Basil Bourque Jun 26 '17 at 03:31
  • @a_horse_with_no_name Unfortunately, Postgres also decides to ignore any commands in an open transaction that triggered an error: https://stackoverflow.com/a/13103690/484293 So your `select` would just throw an exception in cases where it is absolutely vital to perform a `rollback` :-( – blubb Nov 18 '17 at 12:49
  • `PGConnection.getNotifications(int)` works differently depending on the tx state as well, having features like that, but not being able to determine what the tx state is is just not cool. – Pawel Veselov Aug 03 '23 at 14:32

0 Answers0