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