Folks,

Last month, there was a discussion about deferring constraints that use 
user-defined functions to the end of the pg_dump file, like we do with FK 
constraints.    Did this go anywhere, or is it still a TODO in search of an 
owner?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Mon, 15 Mar 2004 14:14:53 -0800
Josh Berkus <josh@agliodbs.com>
Constraints & pg_dump
Josh Berkus <josh@agliodbs.com> writes:
> Last month, there was a discussion about deferring constraints that use 
> user-defined functions to the end of the pg_dump file, like we do with FK 
> constraints.    Did this go anywhere, or is it still a TODO in search of an 
> owner?

Isn't this already solved by dumping in dependency order?

			regards, tom lane

Mon, 15 Mar 2004 17:59:00 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Constraints & pg_dump 
Tom,

> Isn't this already solved by dumping in dependency order?
>
> 			regards, tom lane

Nope.   Problem is, the table depends on the function, and the function 
depends on the table.   pg_dump (in 7.4.1, at least) will dump the table 
first, *with the constraint*, and then the function ... causing table 
creation to fail.

And this isn't come cross-table function either; the constraint that they're 
implementing is partial uniqueness, which is appropriate for a constraint.  

I personally think that the simplest way to do this ... and deal with most 
custom-function-constraint issues ... is to push all constraints containing a 
user-defined  function to the end of the file with the foriegn keys.

(this is for the Bricolage project)

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Tue, 16 Mar 2004 23:19:49 -0800
Josh Berkus <josh@agliodbs.com>
Re: Constraints & pg_dump
Josh Berkus <josh@agliodbs.com> writes:
>> Isn't this already solved by dumping in dependency order?

> Nope.   Problem is, the table depends on the function, and the function 
> depends on the table.   pg_dump (in 7.4.1, at least) will dump the table 
> first, *with the constraint*, and then the function ... causing table 
> creation to fail.

Um ... by "already" I meant "in CVS tip", not "in 7.4.*".  So I'm not
quite sure whether your issue is still live or not.  But I'll guess
anyway:

> And this isn't come cross-table function either; the constraint that they're 
> implementing is partial uniqueness, which is appropriate for a constraint.  

Is it?  Our present handling of CHECK constraints cannot reasonably be
thought to support anything but row-local constraints.  If they're using
a function to make an end-run around the check that prohibits subselects
in CHECK constraints, then their problems are much more serious than
whether pg_dump dumps the database in an order that manages to avoid
failure.  That kind of constraint just plain does not work, because it
won't get rechecked when the implicitly referenced rows change.

			regards, tom lane

Wed, 17 Mar 2004 02:36:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Constraints & pg_dump 


Tom Lane wrote:

>Our present handling of CHECK constraints cannot reasonably be
>thought to support anything but row-local constraints.  If they're using
>a function to make an end-run around the check that prohibits subselects
>in CHECK constraints, then their problems are much more serious than
>whether pg_dump dumps the database in an order that manages to avoid
>failure.  That kind of constraint just plain does not work, because it
>won't get rechecked when the implicitly referenced rows change.
>  
>

Ouch. Two days ago I saw someone on IRC (I think from this list) 
actually advising someone to use this end-run. Maybe we need to beef up 
the docs on this point?

cheers

andrew


Wed, 17 Mar 2004 07:23:59 -0500
Andrew Dunstan <andrew@dunslane.net>
Re: Constraints & pg_dump
Tom,

> Is it?  Our present handling of CHECK constraints cannot reasonably be
> thought to support anything but row-local constraints.  If they're using
> a function to make an end-run around the check that prohibits subselects
> in CHECK constraints, then their problems are much more serious than
> whether pg_dump dumps the database in an order that manages to avoid
> failure.  That kind of constraint just plain does not work, because it
> won't get rechecked when the implicitly referenced rows change.

Hmmm ... damn, you're correct.     It does seem, philosophically, like that is 
the appropriate topic for a constraint.    However, I can see how it would be 
difficult to implement as one ....

What about table-level check constraints?   Seems like one of those should be 
able to be used to check a vertical assertion within a table.  Or do we need 
SQL ASSERTION for this?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Wed, 17 Mar 2004 08:53:32 -0800
Josh Berkus <josh@agliodbs.com>
Re: Constraints & pg_dump
Josh Berkus <josh@agliodbs.com> writes:
> What about table-level check constraints?   Seems like one of those should be
> able to be used to check a vertical assertion within a table.  Or do we need 
> SQL ASSERTION for this?

Seems like it would be smart to maintain a syntactic distinction between
row-local checks and global checks, so I'd be inclined to insist on
using ASSERTION.  I am not sure what the SQL spec has to say about this
though --- they probably have some obscure phraseology that bears on the
question.

AFAIR, whether a constraint is syntactically attached to a column or is
"loose" in the table definition is not supposed to have any semantic
consequences, but I might be wrong about that too.

			regards, tom lane

Wed, 17 Mar 2004 12:03:35 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Constraints & pg_dump 
Tom,

> AFAIR, whether a constraint is syntactically attached to a column or is
> "loose" in the table definition is not supposed to have any semantic
> consequences, but I might be wrong about that too.

Well, a table-level CHECK constraint can attach to more than one column, so in 
that way *is* different, regardless of whatever else the spec says about it.   
For example,

table a (
	col1 INT,
	col2 INT,
	CONSTRAINT less_than CHECK (col1 < col2)
);

The fact that the constraint is implemented as a function shouldn't make a 
difference for us as long as all of the columns are named:

table users (
	username text,
	active boolean,
	CONSTRAINT username_is_unique CHECK cf_user_unique(username, active)
);

in this case, the constraint should be triggered whenever either of the named 
columns is updated.  BTW, the above is basically Bricolage's problem ... they 
want only active user names to be unique.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Wed, 17 Mar 2004 09:12:59 -0800
Josh Berkus <josh@agliodbs.com>
Re: Constraints & pg_dump
Josh Berkus <josh@agliodbs.com> writes:
> BTW, the above is basically Bricolage's problem ... they 
> want only active user names to be unique.

Oh, why didn't you say so?  Seems like the correct tool to solve that is
a partial unique index, not a constraint at all.

			regards, tom lane

Wed, 17 Mar 2004 12:20:57 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Constraints & pg_dump 
Tom,

> Oh, why didn't you say so?  Seems like the correct tool to solve that is
> a partial unique index, not a constraint at all.

Hmmm .... we support that?    Darn, how do I miss these things.   When did we 
start supporting it?   Bric still has a lot of users who use 7.2.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Wed, 17 Mar 2004 09:25:17 -0800
Josh Berkus <josh@agliodbs.com>
Re: Constraints & pg_dump
Josh Berkus <josh@agliodbs.com> writes:
>> Oh, why didn't you say so?  Seems like the correct tool to solve that is
>> a partial unique index, not a constraint at all.

> Hmmm .... we support that?    Darn, how do I miss these things.   When did we
> start supporting it?   Bric still has a lot of users who use 7.2.

Looks like it was added (back) in 7.2.

			regards, tom lane

Wed, 17 Mar 2004 12:41:48 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Constraints & pg_dump 