Re: [HACKERS] subquery in CHECK constraint
> I think the basic problem would be what the check constraint subquery > meant to the user, and how useful that is expected to be in general. A > subquery in a check constraint would presumably involve checking the > subquery using an existing snapshot of the command that required the > constraint to be verified (say, an INSERT). But why should that > snapshot be so special? In any case the result of the subquery may not > be immutable (even in some limited, practical sense), and we expect > check constraints to be on immutable conditions on constrained columns > only. In general it would be practically impossible to determine that > something else had changed the state of the database in such a way as > to make the check constraint no longer verify successfully on each > row, so we would not be able to prevent that from happening later on. > > I imagine that you have a very specific case in mind, though. Perhaps > you can share the details. No I don't have a specific case. I am just wondering because it's defined in the standard. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] subquery in CHECK constraint
Peter Geoghegan writes: > On Fri, Jul 18, 2014 at 7:31 PM, Tatsuo Ishii wrote: >> Has anybody tried to implement subquery in CHECK constaint? If so, >> what are issues to implement it? Or the feature is not worth the >> effort? Comments and/or opinions are welcome. > I think the basic problem would be what the check constraint subquery > meant to the user, and how useful that is expected to be in general. Yeah. Check constraints are only well-defined to the extent that they constrain the contents of the current row independent of anything else. It's hard to conceive of a use-case for a subquery that wouldn't violate that in some fashion. I can certainly conceive of cases in which you want to constrain the contents of one table in terms of another's contents, sort of like foreign keys, but let's suppose that the particular invariant you have in mind isn't expressible as a foreign key. But you can write a CHECK subquery that captures what you want. Now what? There's a *lot* of complicated infrastructure needed to implement foreign keys, because they constrain both tables not just one. How would you invert a CHECK subquery to figure out what changes are allowed in the referenced table? Maybe you're willing to accept the special case in which you don't intend ever to change the referenced table, or are willing to take responsibility for not changing it in a way that violates the CHECK constraint for any existing row in the referencing table. So fine; all the system is supposed to do is check the constraint on every insert/update in the referencing table. I think the implementation issues would be (1) there's no support for doing any planning of subqueries in standalone expressions. This is probably just a small matter of programming, but still a hurdle to be jumped. (2) how would pg_dump deal with check constraints like these? At minimum it'd have to understand, or guess at, the dump ordering restrictions needed to allow data to be reloaded with such a constraint. I'm not sure this is much easier to solve than the general case of SQL assertions (which we have not got either). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] subquery in CHECK constraint
Hi, On Fri, Jul 18, 2014 at 7:31 PM, Tatsuo Ishii wrote: > Has anybody tried to implement subquery in CHECK constaint? If so, > what are issues to implement it? Or the feature is not worth the > effort? Comments and/or opinions are welcome. I think the basic problem would be what the check constraint subquery meant to the user, and how useful that is expected to be in general. A subquery in a check constraint would presumably involve checking the subquery using an existing snapshot of the command that required the constraint to be verified (say, an INSERT). But why should that snapshot be so special? In any case the result of the subquery may not be immutable (even in some limited, practical sense), and we expect check constraints to be on immutable conditions on constrained columns only. In general it would be practically impossible to determine that something else had changed the state of the database in such a way as to make the check constraint no longer verify successfully on each row, so we would not be able to prevent that from happening later on. I imagine that you have a very specific case in mind, though. Perhaps you can share the details. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] subquery in CHECK constraint
Hi, Has anybody tried to implement subquery in CHECK constaint? If so, what are issues to implement it? Or the feature is not worth the effort? Comments and/or opinions are welcome. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers