Re: Proposed patch: make pg_dump --data-only consider FK constraints

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