Bug #506: serializable transaction does may commit in jdbc

Bram Kivenko (postgres(at)kivco(dot)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