Re: Check Constraints and pg_dump

Curt Sampson <cjs(at)cynic(dot)net> writes:
> Can you explain how to do this? There is no reference to a plan in the
> contract table; the constraint just checks to see that, if a contract
> exists, there is at least one plan referencing that contract.
> There is of course a foreign key constraint used in the plan table to
> make sure that the contract exists.

If so, how would it be possible to create a plan before creating the
contract? I don't think the OP's requirements are clearly thought out.

> At any rate, I am not sure why pg_dump has to know or care what check
> constraints do; if it simply treated them as it does all the other
> constraints, and applied them after all the data are loaded, wouldn't
> the problem just go away?

If we did that we'd be slowing bulk loads (since each added check
constraint would incur an additional scan over the table) and
decreasing legibility of the dumped schema (surely you will agree
that it's more readable to keep the constraint in the CREATE TABLE
command).

There is code in CVS tip pg_dump to split out a check constraint from
the parent table when this is the only way to break a circular
dependency. But I'm disinclined to apply that transformation all the
time, especially when the only reason to do so is to support a misuse
of check constraints. Check constraints are not intended to handle
cross-table checks, and I'm unwilling to buy into any suggestion that
we should consider that a supported use.

We have talked in the past about supporting SQL's "CREATE ASSERTION"
command, which *is* intended to describe cross-table conditions.
I don't recall that anyone had good ideas about a reasonably efficient
implementation though.

In the meantime, if what's wanted is a one-time check at row insertion,
the right way to express that behavior is with an ON INSERT trigger.

regards, tom lane