Re: subquery in CHECK constraint

Peter Geoghegan <pg(at)heroku(dot)com> writes:
> On Fri, Jul 18, 2014 at 7:31 PM, Tatsuo Ishii <ishii(at)postgresql(dot)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