Thread: Constraints & pg_dump
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 BerkusAglio Database SolutionsSan Francisco
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
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
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
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
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 BerkusAglio Database SolutionsSan Francisco
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
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 BerkusAglio Database SolutionsSan Francisco
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
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 BerkusAglio Database SolutionsSan Francisco
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