Re: Check Constraints and pg_dump

Hello again,

A project I am working on has been having problems with pg_dump's output, using 7.3. Our project's database includes functions that do constraint checking for us, as well as circular dependencies.

We heard about the changes on the pgsql HEAD/7.5, and have given it a try. It fixed nearly all our problems; however, there is one that is cropping up that we feel should be reviewed: check constraints do not get deferred when loading the data back in to the database using pg_dump's default script.

I have written a script which should be able to reproduce the problem we are encountering. We do not have binary data, so we just use the regular SQL output of pg_dump. The functions and tables create just fine, but when it gets to the COPY part of the sql script, it tries to load tables in what really is the wrong order. The check constraint is making sure there is a "plan" before there is a "contract", yet pg_dump is trying to load the contract table before there is anything in the plan table. This may seem weird at first, as the plan table is referencing the contract table's PK. Our intention is to make sure that EVERY contract has at least one plan.

Please feel free to ask me about this script and associated files. You will most likely want to edit the Test file, and make it point to the correct HEAD/7.5 run environment. I do not specify PGPORT/PGHOST etc in the file, so you will need to make sure you have those set in your environment.

Jonathan Scott

--
Jonathan Scott, Programmer, Vanten K.K.
jwscott(at)vanten(dot)com Tel: 03-5919-0266
http://www.vanten.com Fax: 03-5919-0267

Jonathan Scott <jwscott(at)vanten(dot)com> writes:
> The functions and tables create just fine, but when it gets to the
> COPY part of the sql script, it tries to load tables in what really is
> the wrong order. The check constraint is making sure there is a "plan"
> before there is a "contract", yet pg_dump is trying to load the
> contract table before there is anything in the plan table.

Shouldn't you be using a foreign key for that?

I don't see any reasonable way that pg_dump can be expected to
understand that a check constraint expresses a relationship between two
tables. The semantics of check constraints aren't right for it anyway.

All else being equal, I think the tables will be loaded in OID order,
so a possible workaround is to create the plan table first. But an
FK seems like a better answer.

regards, tom lane

Tom,

I have another instance of a possible function being used as a check constraint: a function that makes sure there is one row, and only one row in a table.

At table creation, and the creation of the constraint, there are no rows in the table. So, even if the constraint is a valid one to have, it will fail.

We use check constraint functions in a few places, and they work just fine for us (minus the case of trying to restore the database from a pg_dump archive). Is it impossible to treat check constraints similar to other constraints and make them deferrable, specifically in the restoration from a pg_dump archive? Is there a specific reason check constraints are handled differently from other constraints?

Jonathan Scott

On Thu, 26 Feb 2004 08:54:59 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jonathan Scott <jwscott(at)vanten(dot)com> writes:
> > The functions and tables create just fine, but when it gets to the
> > COPY part of the sql script, it tries to load tables in what really is
> > the wrong order. The check constraint is making sure there is a "plan"
> > before there is a "contract", yet pg_dump is trying to load the
> > contract table before there is anything in the plan table.
>
> Shouldn't you be using a foreign key for that?
>
> I don't see any reasonable way that pg_dump can be expected to
> understand that a check constraint expresses a relationship between two
> tables. The semantics of check constraints aren't right for it anyway.
>
> All else being equal, I think the tables will be loaded in OID order,
> so a possible workaround is to create the plan table first. But an
> FK seems like a better answer.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--
Jonathan Scott, Programmer, Vanten K.K.
jwscott(at)vanten(dot)com Tel: 03-5919-0266
http://www.vanten.com Fax: 03-5919-0267

On Thu, 26 Feb 2004, Tom Lane wrote:

> Jonathan Scott <jwscott(at)vanten(dot)com> writes:
> > The functions and tables create just fine, but when it gets to the
> > COPY part of the sql script, it tries to load tables in what really is
> > the wrong order. The check constraint is making sure there is a "plan"
> > before there is a "contract", yet pg_dump is trying to load the
> > contract table before there is anything in the plan table.
>
> Shouldn't you be using a foreign key for that?

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.

> I don't see any reasonable way that pg_dump can be expected to
> understand that a check constraint expresses a relationship between two
> tables. The semantics of check constraints aren't right for it anyway.

What other constraint could one use for a situation like this?

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?

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

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

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

On Monday 01 March 2004 22:59, Curt Sampson wrote:
> 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.
>
<snip>
> > 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.
>

Wouldn't a FK on both tables be the appropriate schema? With the FK on
contract being deffered?

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Tue, Mar 02, 2004 at 09:45:03 -0500,
Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> wrote:
>
> Wouldn't a FK on both tables be the appropriate schema? With the FK on
> contract being deffered?

No, since he only cares that there is at least one plan for a contract,
not a particular plan. You can do something like this as a hack where
when the plan referenced by the contract gets deleted that the contract
has to be changed to reference another plan or be deleted. But I think
using triggers is a better way to do this.

On Tue, 2 Mar 2004, Robert Treat wrote:

> Wouldn't a FK on both tables be the appropriate schema? With the FK on
> contract being deffered?

Unfortunately, it appears that an FK must reference a unique column. So this:

ALTER TABLE contract ADD CONSTRAINT contract_must_have_a_plan
FOREIGN KEY ( contract_id ) REFERENCES plan ( contract_id )
INITIALLY DEFERRED;

produces the error message:

UNIQUE constraint matching given keys for referenced table "plan" not found

Since a plan may have more than one 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