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