1

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?

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.