PostgreSQL information_schema.tables and TRANSACTION ISOLATION LEVEL

Question

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?

Answer 1

The SQL statement set transaction isolation level . . . doesn't start a transaction. (Not in the sense you're interested in, anyway.)

Following your code, you'd write SQL statements either like this

set transaction isolation level serializable;
begin transaction;
...

or like this.

begin transaction isolation level serializable;
...

Relevant PostgreSQL docs

But you don't need serializable transactions for this. You can test by running psql in two terminal sessions.

sandbox=# begin transaction;
BEGIN
                                          sandbox=# begin transaction;
                                          BEGIN

sandbox=# select * from foo for update;
 foo_id 
--------
      1
(1 row)
                                          sandbox=# drop table foo;
                                          [waits . . .]
sandbox=# update foo set foo_id = 2;
UPDATE 1
sandbox=# select * from foo;
 foo_id 
--------
      2
(1 row)

sandbox=# commit;
COMMIT
                                          DROP TABLE
                                          sandbox=# commit;
                                          COMMIT

sandbox=# select * from foo;
ERROR:  relation "foo" does not exist
LINE 1: select * from foo;

Having said all that, designing a database in which tables and schemas might be regularly be dropped and (I presume) created seems like a bad idea.