lundi 2 mars 2015

PostgreSQL information_schema.tables and TRANSACTION ISOLATION LEVEL



In PostgreSQL, I'm facing race conditions. My tables and schemas may be deleted by separate processes in the system. Using idiom if schema and table exists, then read the contents hence does not work in general, because the table may cease to exist in the middle of the statement.


One thing I don't understand is why SET TRANSACTION ISOLATION LEVEL SERIALIZABLE does not help. I'd suppose that I may expect consistent view of schemas and tables during my transactions, but I don't. Below is my Java code:



pgConnection = DriverManager.getConnection(/* ... */);
pgConnection.setAutoCommit(false);

PreparedStatement statement = pgConnection.prepareStatement(
"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;");
statement.execute();

statement = pgConnection.prepareStatement(
"SELECT ('myschema','config') IN " +
"(SELECT table_schema,table_name FROM information_schema.tables);");

ResultSet result = statement.executeQuery();
result.next();
if(result.getBoolean(1)) {
statement = pgConnection.prepareStatement("SELECT key,value FROM myschema.config;");

result = statement.executeQuery(); // here I'm often getting an exception

/* ... */
}


The exception I'm getting is:



org.postgresql.util.PSQLException: ERROR: relation "myschema.config" does not exist


How is that possible? I thought ISOLATION LEVEL SERIALIZABLE will protect me from such conditions. Is that because dropping schemas is too specific operation to keep the isolation? Or am I doing something fundamentally wrong?




Aucun commentaire:

Enregistrer un commentaire