I am trying to implement an automatic archiving system for some partitioned tables in pg14.
The process I am trying to implement is as follow :
- I start with several tables partitioned by date with foreign keys (referencing each other and other tables as well)
- After a few days I detach the old partitions (in the right order to avoid issues with foreign keys) and move them to an
- I export everything from this schema to another storage system for long term archiving
- I drop the old partitions from the schema
I am facing an issue at step 4 because of deadlocks. After some investigation it appears that pg takes an access exclusive lock on referenced table when dropping foreign keys in order to delete a trigger. Since my tables include several foreign keys to tables that are quite heavily used I end up deadlocking. I already commit between each drop so my issue arise from the fact that I have several foreign keys to drop for a given table.
My first instinct was to drop these foreign keys one by one in separate transactions to avoid deadlocks. But I encountered what I think is a bug in pg. When I try to drop the constraints, I receive an error
cannot drop inherited constraint <constraint_name> from relation <table_name>. My tables (former partitions) do not have any inheritance relationship to any table and have been detached from the main table so this should not happen. But if I check
pg_catalog.pg_constraint it appears that column
coninhcount is still set to
1 for most of my foreign keys.
Does anybody have an idea how I could drop these tables without locking my system because of the foreign keys?