I had a need to be able to handle table partition management. Unfortunately the particular framework + ORM I'm using just will not handle it. So I decided to use a PostgreSQL trigger function to handle creating, updating, deleting of table partitions based on trigger actions against a tracking table.
Super cool, right?
Not so much when pytest is running and everything you do is a monstrous transaction. I wanted to flow to be like this:
- Create a partition.
- Insert some records
- Delete the records by dropping the correct containing partition.
Works great in autocommit mode. Transactionally, it'll stomp those operations flat if you use deferred foreign key constraints (which my app database is loaded with). You end up with some variation of this error (as reported by python and psycopg2):
psycopg2.errors.ObjectInUse: cannot DROP TABLE "reporting_awscostentrylineitem_daily_summary_2017_01" because it has pending trigger events
CONTEXT: SQL statement "DROP TABLE reporting_awscostentrylineitem_daily_summary_2017_01 ;"
PL/pgSQL function trfn_partition_manager() line 359 at EXECUTE
Holy cow! If you've forgotten that you have deferred constraints (as I did) you'll have about 15 minutes of panic before you realize that there is actually a simple answer for this. You can set all or a specified set of constraints to IMMEDIATE for the current transaction. The easy, big-hammer form of this is:
SET CONSTRAINTS ALL IMMEDIATE;
But please take a few minutes to read the docs. This forced the deferred constraint triggers to fire and allowed me to execute the DDL for ALTER, TRUNCATE, DROP on the affected table partition.
What a lifesaver!
Hope this helps someone out there.
Top comments (0)