No, you can't change the underlying state in cleanup() - see comment in destroy().
Any test cases + patches should be submitted against the 1.2 branch, and not the 1.0 branch, as it is for approved backports only.
I suggest you look for inspiration in pgjdbc/ConnectionTest.java at master · pgjdbc/pgjdbc · GitHub for your test case that identifies this issue. There are a number of JDBC related test cases in IronJacamar already to look at. Also include the full configuration of your datasource, the JDBC driver version, and server version in your next post.
PostgreSQL JDBC driver version is 9.3-1103-jdbc41
PostgreSQL server version is 9.3.6
Full data source configuration:
<datasource jndi-name="java:/H2DS" pool-name="H2DS">
The problem only happens with PostgreSQL, H2 for instance allows to change isolation level in the middle of the transaction, so it's not problem there.
I implemented a test, with mocked JDBC driver that simulates PostgreSQL behaviour: [BZ-1195079] Cannot change transaction isolation · 8973720 · TomasHofman/ironjacamar · GitHub
So, if we cannot reset autocommit state during cleanup, can we reset it when the connection is being obtained from a pool? This would do the job: [BZ-1195079] Cannot change transaction isolation · 1a008b7 · ironjacamar/ironjacamar · GitHub
You are jumping ahead, start by doing a test case for the PostgreSQL driver - f.ex. by adding to their existing test case. Then test using your installation and latest versions.
If you are saying that other databases doesn't complain (don't count H2) then it sounds more like a JDBC driver problem -- missing reset of state.
> "You are jumping ahead, start by doing a test case for the PostgreSQL driver - f.ex. by adding to their existing test case."
Of course I was running that test of mine also with real PostgreSQL driver, with the ds configuration I posted above, just didn't commit it. However, I don't think I can test this inside *their* (pgjdbc) test suite, since I'm testing IronJacamar behaviour...
> "sounds more like a JDBC driver problem -- missing reset of state"
My point is when is that reset supposed to happen? As I see it, IronJacamar never tells the driver to set autocommit back to true between returning the connection to a pool and retrieving it from the pool again. That doesn't matter for the application code, since application is working with WrappedConnection, which is checking and fixing autocommit during all operations like prepareStatement() and others, but ValidConnectionChecker operates with physical JDBC connection directly and therefore can open undesired transaction if autocommit is left disabled in physical connection.
I was testing MariaDB, Oracle and Firebird databases. From those, only Oracle doesn't allow changing transaction isolation during transaction, though it's not as strict as PostgreSQL:
- Oracle only throws an exception during conn.setTransactionIsolation() if there was already some update or insert statement in the transaction, it's OK with only select statements executed.
- PostgreSQL throws an exception even if there were only select statements in the transaction.
- MariaDB and Firebird doesn't complain about changing transaction isolation during transaction at all.
Also note that documentation of Connection.setTransactionIsolation() says: "If this method is called during a transaction, the result is implementation-defined."
So I can reproduce the problem with PostgreSQL and Oracle, although with Oracle it requires a little weird setting of check-valid-connection-sql to an update statement. That of course is unlikely to be used in practice, but at least it demonstrates that autocommit of an jdbc driver is left disabled.
I committed separate tests for PostgreSQL and Oracle alongside the original one:
Tested JDBC driver version were:
PostgreSQL 9.3-1103-jdbc41, 9.4-1201.jdbc41