Andreas Lange <anlan (AT) ida (DOT) 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.

It would be best to find a way to express these relationships with
ordinary foreign keys. Maybe you could add a column to form_a_int
that is a foreign key reference to both of form_instance.fid and
form_q.fid, for example?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Thu, 2005-03-24 at 17:27 -0500, Tom Lane wrote:
(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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq