Thread: 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
Hi, On Fri, Jul 18, 2014 at 7:31 PM, Tatsuo Ishii <ishii@postgresql.org> 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
Peter Geoghegan <pg@heroku.com> writes:
> On Fri, Jul 18, 2014 at 7:31 PM, Tatsuo Ishii <ishii@postgresql.org> 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
> 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