How to determine which foreign keys are circular? As highlighted by warning from pg_dump

Question

When carrying out pg_dump on a (timescale) database running within a local container I have the following:

pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump:   hypertable

Command that I ran for the above:

pg_dump --schema-only -h localhost --port 5432 --dbname db --username user > schema.sql

I'm not sure how to check using postgres to see which keys are circular.

I could go through all the tables and manually trace which keys point to which I guess - but I was hoping there would be a more straightforward approach to determining which keys are circular and where, so that I could go about fixing them.

Answer 1

You get this warning only with a data-only dump, and since only a single table is listed, that table must have a foreign key referencing itself.

You can find all such foreign key constraints with

SELECT conrelid::regclass AS table_name,
       conname AS constraint_name
FROM pg_constraint
WHERE contype = 'f'
  AND conrelid = confrelid;

There are several things you can do:

• Don't perform a data-only dump. If you dump the DDL statements as well (and perhaps use the --clean option), the circular foreign key is no problem, because the data will be restored before the constraint is created.

• Drop the foreign key before restoring the dump and create it afain afterwards.

• Disable all foreign keys ant triggers on the table before restoring the dump:

  ALTER TABLE tab DISABLE TRIGGER ALL;
  

  Afterwards, you can reactivate them with

  ALTER TABLE tab ENABLE TRIGGER ALL;
  

  You need to be a superuser to do that, since it can break consistency.