2

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.

1 Answer 1

6

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.

4
  • 2
    Thanks - I added the command used to the OP, it was with a schema-only flag to pg_dump rather than data-only though
    – baxx
    Jan 9, 2023 at 9:48
  • A schema-only dump will not generate that warning. Jan 9, 2023 at 10:16
  • This was generated using the command that I've pasted into the OP (the pg_dump command) - is there any more information that I can add to clarify?
    – baxx
    Jan 9, 2023 at 16:33
  • You could provide a complete self-contained example, so we can reproduce that. Jan 9, 2023 at 17:03

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.