Re: [HACKERS] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Constraints pg_dump
Josh Berkus [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Constraints pg_dump
Josh Berkus [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Constraints pg_dump
Josh Berkus [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Constraints pg_dump
Josh Berkus [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
