| Mark Shewmaker 2005-05-22, 8:24 pm |
| On Thu, 2005-03-24 at 17:27 -0500, Tom Lane wrote:
> Andreas Lange <anlan@ida.liu.se> writes:
>
> 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@primefactor.com
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|