Concurrency issue with psycopg2, Redshift, and uni

问题:

I am in Python 2.7, using psycopg2 to connect to an Amazon Redshift database. I have unit tests, and in the setUp and tearDown methods for this test class, I drop the tables that were created for the purpose of this test. So the scheme is:

def setUp(self):
    drop_specific_tables()
    create_specific_tables()

def tearDown(self):
    drop_specific_tables()

The reason for dropping in the setUp as well as tearDown is in case a test exits unsafely and skips tearDown we can still know that whenever it runs again it will still begin with a clean slate.

This is the drop_specific_tables method, and rcur is a psycopg2 cursor pointing to our redshift database.

def drop_specific_tables(rcur):
    rcur.execute("BEGIN;")
    rcur.execute("SELECT table_name "
                 "  FROM information_schema.tables "
                 "  WHERE table_schema='public' ")
    tables = [row for row, in rcur]
    rcur.execute("DROP TABLE IF EXISTS " + ", ".join(tables) + " CASCADE;")
    rcur.execute("END;")

When an individual test is run, it passes. But when the whole class is run, certain tests error in the setUp or tearDown (it's non-deterministic which tests error and which drop_specific_tables), in drop_specific_tables(), on the line with

rcur.execute("SELECT table_name "
             "  FROM information_schema.tables "
             "  WHERE table_schema='public' ")

yielding the error ProgrammingError: Relation with OID 1454844 does not exist. I printed out the OID of 'information_schema.tables' and it is not the same OID as the one in the error message.

Why might this be happening? I understand what it means for a relation to not exist, but what relation is this query looking for that it can't find? And why would it sometimes not be there, causing the query to error?

UPDATE: I also printed out the OID of each table before dropping, and none of them are the OID in the error message either!

回答1:

You are using DROP with the CASCADE option. So any drop of tables having referential integrity will also drop the child table associated with the parent table.

To troubleshoot if this is actually what is happening, before running your code take a snapshot of existing tables with their OID ( i think pg_tables or pg_relations should have this information). Run the code and check the OID of the error message with the snapshot for the table name.

Edit: It might be because how plans are being cached in PostgreSQL (so in Redshift) for functions. This is a documented bug till 8.2 so you might want to search for a fix for it. The plan will be cached according to the first execution of the function but for the second execution some of the objects would have got new OIDs because of getting recreated. http://merlinmoncure.blogspot.ie/2007/09/as-previously-stated-postgresql-8.html

http://www.postgresql.org/message-id/[e-mail]