Postgresql: Drop detached partitions

Question

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 :

1. I start with several tables partitioned by date with foreign keys (referencing each other and other tables as well)
2. After a few days I detach the old partitions (in the right order to avoid issues with foreign keys) and move them to an archive schema
3. I export everything from this schema to another storage system for long term archiving
4. 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?

Answer 1

I found a solution!

First of all I did not fully understand the reason why I couldn't drop the foreign keys. It appears that my foreign keys were inherited but their parent were not on the former parent table; it was another foreign key on the very same table. This seems to happen whenever my foreign key references a partitioned table (I do not know if this is standard pg or linked to the use of pg_partman).

Let's say for instance that I have two tables A and B with partitions A1, A2... and B1, B2... and that A references B. When considering A1, I ended up (after detaching it from A) having a "main" foreign key referencing B but also a "child" foreign keys referencing B1, another referencing B2 and so on. Because of this internal inheritance, I was not able to drop the foreign key to B1 but I am able to drop the foreign key to B which drops all its children.

So the solution is to list all foreign keys without parent :

    SELECT con.conname
    FROM pg_catalog.pg_constraint con
             INNER JOIN pg_catalog.pg_class rel
                        ON rel.oid = con.conrelid
             INNER JOIN pg_catalog.pg_namespace nsp
                        ON nsp.oid = connamespace
    WHERE nsp.nspname = <schema>
      AND rel.relname = <table>
      AND coninhcount = 0
      and contype='f';

and drop them individually in separate transactions in order to avoid deadlocks.

alter table <schema>.<table> drop constraint <fkey_name>;

At that point my table should not have any foreign key left so I can safely drop it too.

drop table <schema>.<table>;

If anyone knows why pg uses this internal inheritance of constraints I would be very interested in hearing the explanation. But as far as my initial problem is concerned I think we can consider it solved.