Re: [HACKERS] PostgreSQL JDBC driver Connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE)

All set. I dowloaded drivers from jdbc.postgresql.org and that fixed the
problem.
Apparently the drivers that came with my distribution were not up to date.

Many thanks.

Dave

Barry Lind wrote:

> Dave,
>
> I am moving this to the jdbc mail list.
>
> I can't reproduce your problem. You indicate that you are using the
> beta1 driver for 7.3. Can you retry with the latest 7.3 driver from
> jdbc.postgresql.org? Your test case works fine for me with the
> current development driver running against a 7.3.2 database.
>
> thanks,
> --Barry
>
> Dave Tenny wrote:
>
>> I'm using 7.3.3 of the server, and pg73b1jdbc2.jar for the JDBC driver.
>>
>> Various permutations of calls to the setTransactionIsolation all fail
>> to yield a driver state that will tell me that it is set up for
>> serializable transactions.
>>
>> Help!?
>>
>> I googled, I grepped. I find mention of problems with respect to
>> JDBC tests failing and patched, but see no reports of this as a bug,
>> or any fixes for it.
>>
>> Here's the output from the attached program:
>>
>> javac -classpath .:/usr/share/pgsql/pg73b1jdbc3.jar TxnIsolation.java
>> && java -classpath .:/usr/share/pgsql/pg73b1jdbc3.jar TxnIsolation
>> supportsTransactions(): true
>> Default transaction isolation is 2
>> supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED):
>> false
>> supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED):
>> true
>> supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ):
>> false
>> supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE):
>> true
>> Current transaction isolation is 2
>> Transaction isolation after setting to TRANSACTION_SERIALIZABLE is 2
>> ^^^^^^^^^^^^^^^^^^^^ HAVE A PROBLEM ^^^^^^^^^^^^^^^^^^^^
>> Current auto commit is true
>> After settting auto commit to false, it is false
>> Transaction isolation after setAutoCommit() is 2
>> Current connection readonly status is false
>> Connection readonly after setting it to be readonly is true
>>
>> ---------------------------------------------------------------
>>
>> Moving the setTransactionIsolation call to before the setAutoCommit
>> call also results in failure.
>>
>> To run the program, you'll need to change the database and userid
>> parameters in the DriverManager.getConnection call to something that
>> works for you.
>>
>>
>> ------------------------------------------------------------------------
>>
>> import java.sql.* ;
>> import java.io.* ;
>> import java.util.* ;
>>
>> /**
>> * Test JDBC transaction/connection stuff. In particular isolation
>> levels.
>> */
>>
>> 
>> class TxnIsolation
>> {
>> public static void main(String[] args) {
>> try {
>> // ---------------- MySql driver -----------------
>> // -classpath
>> .:/usr/java/mysql-connector-java-2.0.14/mysql-connector-java-2.0.14-bin.jar
>> // RH9: -classpath
>> .:/home/dave/mysql-connector-java-3.0.8-stable/mysql-connector-java-3.0.8-stable-bin.jar
>> //Class.forName("com.mysql.jdbc.Driver") ;
>> //Connection conn =
>> DriverManager.getConnection("jdbc:mysql:///test", "dave", "") ;
>> //String innodb = " TYPE=INNODB" ;
>>
>> // --------------- postgres 7.3.2 distribution JDBC2 driver
>> linked as /usr/java/postgresql-jdbc.jar
>> // -classpath .:/usr/java/postgresql-jdbc.jar
>> Class.forName("org.postgresql.Driver") ;
>> Connection conn =
>> DriverManager.getConnection("jdbc:postgresql:test", "dave", "") ;
>> String innodb = "" ;
>>
>>
>> // Here we go.
>>
>> // Metadata
>> DatabaseMetaData md = conn.getMetaData() ;
>> System.out.println("supportsTransactions(): " +
>> md.supportsTransactions()) ;
>> System.out.println("Default transaction isolation is " +
>> md.getDefaultTransactionIsolation()) ;
>>
>> System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED):
>> " +
>>
>> md.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED))
>> ;
>>
>> System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED):
>> " +
>>
>> md.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED))
>> ;
>>
>> System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ):
>> " +
>>
>> md.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ))
>> ;
>>
>> System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE):
>> " +
>>
>> md.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE))
>> ;
>>
>> // transactionIsolation
>> // Theoretically important to call BEFORE setAutoCommit
>> System.out.println("Current transaction isolation is " +
>> conn.getTransactionIsolation()) ;
>>
>> conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) ;
>> System.out.println("Transaction isolation after setting to
>> TRANSACTION_SERIALIZABLE is "
>> + conn.getTransactionIsolation()) ;
>> if (conn.getTransactionIsolation() !=
>> Connection.TRANSACTION_SERIALIZABLE)
>> System.out.println("^^^^^^^^^^^^^^^^^^^^ HAVE A PROBLEM
>> ^^^^^^^^^^^^^^^^^^^^") ;
>>
>> // autoCommit
>> System.out.println("Current auto commit is " +
>> conn.getAutoCommit()) ;
>> conn.setAutoCommit(false) ;
>> System.out.println("After settting auto commit to false, it is
>> " + conn.getAutoCommit()) ;
>> System.out.println("Transaction isolation after setAutoCommit()
>> is "
>> + conn.getTransactionIsolation()) ;
>>
>>
>> // readOnly
>> System.out.println("Current connection readonly status is " +
>> conn.isReadOnly()) ;
>> conn.setReadOnly(true) ;
>> System.out.println("Connection readonly after setting it to be
>> readonly is " + conn.isReadOnly()) ;
>>
>> }
>> catch (Exception e) {
>> e.printStackTrace() ;
>> }
>> } // main()
>> }
>>
>>
>> ------------------------------------------------------------------------
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>
>
>
>