Concurrency Issue With Psycopg2, Redshift, And Unittest

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 w

Solution 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/eea51fdb0708170848w77e27daarfc375ad5c7bc1e09@mail.gmail.com