Bug #506: serializable transaction does may commit in jdbc

Bram Kivenko (postgres@kivco.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
serializable transaction does may commit in jdbc

Long Description
Sequence of events:
(using the jdbc driver 7.1.3 on linux 2.4.x, jdk2 1.3.1

    commit
    set transaction isolation level serializable
    autocommit(false)
    commit

    select * from non_existing_table
    ... catch exception ...
    create table non_existing_table (...);
    commit
    autocommit(true)
    set transaction isolation readable

It seems that the table cannot be created once a failed
query occurs inside the serializable transaction.

This does _NOT_ occur when running psql and doing much
the same thing -- it only happens with the JDBC driver.

Sample Code
public class ... {
     Connection conn;

protected int getIsolation() throws Exception {
  int origIsolation;

  try {
    origIsolation = conn.getTransactionIsolation();
    if ( origIsolation != conn.TRANSACTION_SERIALIZABLE ) {
      conn.commit();
      conn.setTransactionIsolation(conn.TRANSACTION_SERIALIZABLE);
      conn.setAutoCommit(false);
      conn.commit();
    }
  } catch ( SQLException e ) {
    throw new Exception(e.toString());
  }
  return origIsolation;
}

protected void resetIsolation(int origIsolation)
          throws Exception {
  try {
    if ( origIsolation != conn.TRANSACTION_SERIALIZABLE ) {
      conn.commit();
      conn.setAutoCommit(true);
      conn.setTransactionIsolation(origIsolation);
    }
  } catch ( SQLException e ) {
    throw new Exception(e.toString());
  }
}

protected void init() throws Exception {
  int origIsolation = getIsolation();
  try {
    ResultSet rs;
    rs = conn.createStatement().executeQuery(
      "SELECT * " +
      "FROM " + tableName + " " +
      "WHERE 1 = 0; ");
    rs.close();
  } catch ( SQLException e ) {
    try {
      conn.createStatement().executeUpdate(
        "CREATE TABLE " + tableName +
        " ( id int ); ");
      commit();
    } catch ( SQLException e2 ) {
      throw new Exception(e2);
    }
  } finally {
    resetIsolation(origIsolation);
  }
}

No file was uploaded with this report