Re: Check Constraints and pg_dump

On Mon, 1 Mar 2004, Tom Lane wrote:

> 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.

You create the contract and its first plan at the same time, inserting
the plan first. It works fine.

> > 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)....

Certainly, but do we not already pay that price for all non-check
constraints? And it slows loads a lot more when you have to edit your
dumps because they are otherwise unloadable. At any rate, this being a
database, I'm inclined more towards correctness than speed.

> 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).

I agree that it's more readable, yes. But again, do we not already pay
that price for all non-check constraints?

> 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.

I'm having great difficulty understanding why this is a "misuse." What
is the proper way to check that a contract cannot exist without at least
one plan?

> 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.

So how do we handle it?

> 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.

I would happily settle for an inefficent implementation; that would give
me the choice of correctness versus efficiency, rather than having no
choice at all.

> 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.

That's not an adequate check; it would allow you later to delete the
plan without deleting the contract.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.NetBSD.org
Don't you know, in this new Dark Age, we're all light. --XTC