Tom Lane wrote:
Okay, I got tired of seeing people complain about foreign-key constraint violations in data-only dumps. While it's true that the problem can't be solved in the general case (because of potentially circular references), we could certainly make pg_dump at least *try* to order the tables according to foreign key relationships. It turns out not to even require a whole lot of new code. Accordingly I propose the attached patch. It will order the tables safely if it can, and otherwise complain like this:
+1
pg_dump: WARNING: circular foreign-key constraints among these table(s): pg_dump: master pg_dump: child pg_dump: You may not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
WARNING feels a bit too strong. I realize that that message isn't going to the postmaster's log, bloating it, but if a user does that regularly, always disabling triggers as instructed, or there is in fact never circular references in the data with a particular schema, seeing that big fat warning every time is going to become a bit tiresome. Perhaps "NOTE: ..." ?
How about printing that notice at the top of the dump file as well? Most people probably don't look at the dump files, but if someone needs to deal with a data-only dumps that contain circular constraints, and also those that don't, it would be invaluable information.
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
