Re: pg_dump table ordering bug [8.0.1]

On Thu, 2005-03-24 at 17:27 -0500, Tom Lane wrote:
> Andreas Lange <anlan(at)ida(dot)liu(dot)se> writes:
> > Our upgrade from 7.4.6 to 8.0.1 only had one small glitch. Two tables
> > got dumped in the wrong order (before their dependecies) and had to get
> > their contents added manually after the restore. I've atleast isolated
> > the part where things go wrong.
>
> I don't think this is a pg_dump bug: the problem is you are abusing
> check constraints to emulate foreign key constraints. pg_dump has no
> way to know what those functions are doing and therefore no way to
> realize that the check constraints impose a data load ordering
> dependency. Furthermore, the check constraints are fundamentally wrong
> anyway because they don't create a two-way relationship --- that is,
> altering the referenced tables won't raise an error if the check is now
> violated for something in the referencing table.

(Sorry for the long delay here!)

Could this be resolved simply by having pg_dump write out all constraint
statements after all insert and trigger statements?

Then no data-order-dependent constraints will be triggered when the dump
is loaded, and even constraints that aren't met when when the dump is
taken won't be triggered when the data is re-loaded.

(I would say that would be a feature not a bug, since as I understand it
the point of pg_dump is to replicate a db setup, with it also being a
separate sanity checker merely a possible benefit. And in any event, if
a few "special" rows don't meet constraints, having had to have been
entered before the constraints were put into place, those rows could
still be restored without problems. Whether that's indicative of poor
schema design is a separate issue.)

Are there any downsides to changing the order of pg_dump output with
respect to constraints? (Versus requiring users to alter their schema
design.)

--
Mark Shewmaker
mark(at)primefactor(dot)com