Re: subquery in CHECK constraint

Hi,

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. 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