Thread: Using SUBSELECT in CHECK expressions

"Najib Abi Fadel" <nabifadel@usj.edu.lb> writes:
> Is postgres going to support in a future release the use of SUBSELECT in a =
> CHECK expression ??

Not very likely.  To my mind, such a constraint would imply rechecking
whenever the table(s) read by the sub-SELECT change, not only when a
row of the constrained table changes.  I don't know any reasonably
efficient implementation of that behavior.  We might figure out how to
do it eventually, but don't hold your breath.

If you are satisfied with only a one-directional constraint (apply the
check just when the constrained table is modified), you can have it today.
Just put the SELECT into a function that's called by the CHECK
expression.

            regards, tom lane

On Tue, 4 Nov 2003, Najib Abi Fadel wrote:

> Is postgres going to support in a future release the use of SUBSELECT in
> a CHECK expression ??

Possibly, but AFAIK it's not on anyone's hit list in the short term, so
you're probably looking at multiple years unless you can do it or find
someone to do it.

On Wednesday, November 5, 2003, at 06:26 AM, Tom Lane wrote:
> If you are satisfied with only a one-directional constraint (apply the
> check just when the constrained table is modified), you can have it
> today.
> Just put the SELECT into a function that's called by the CHECK
> expression.

Tom, you've just provided the solution to something I've been thinking
about a lot. I really have to start thinking in terms of functions. Are
there any restrictions on the function used in the CHECK, i.e., it must
be STRICT or IMMUTABLE? I took a quick look at the developer docs
(thinking they'd be for 7.4RC1. I was happily suprised to see they're
already labeled 7.5 dev!)

Michael
grzm myrealbox com

Michael Glaesemann <grzm@myrealbox.com> writes:
> On Wednesday, November 5, 2003, at 06:26 AM, Tom Lane wrote:
>> Just put the SELECT into a function that's called by the CHECK
>> expression.

> Tom, you've just provided the solution to something I've been thinking
> about a lot. I really have to start thinking in terms of functions. Are
> there any restrictions on the function used in the CHECK, i.e., it must
> be STRICT or IMMUTABLE?

IIRC we check that it is labeled IMMUTABLE.  Whether it really is or
not, we cannot check (and if it uses a SELECT on tables that could
change, then of course it isn't; so you are going to need to tell a
little white lie here).

Because of that, you need to be careful that you pass at least one
nonconstant argument to the function within the CHECK expression, else
you risk having the call constant-folded too early.  Normally you'd
probably pass values from the checked row into the function, so this
doesn't seem like it should be a problem, but I could see someone
getting bit by it someday ...

            regards, tom lane

On Thursday, November 6, 2003, at 02:38 AM, Tom Lane wrote:

> Michael Glaesemann <grzm@myrealbox.com> writes:
>> On Wednesday, November 5, 2003, at 06:26 AM, Tom Lane wrote:
>>> Just put the SELECT into a function that's called by the CHECK
>>> expression.
>
>> Tom, you've just provided the solution to something I've been thinking
>> about a lot. I really have to start thinking in terms of functions.
>> Are
>> there any restrictions on the function used in the CHECK, i.e., it
>> must
>> be STRICT or IMMUTABLE?
>
> IIRC we check that it is labeled IMMUTABLE.  Whether it really is or
> not, we cannot check (and if it uses a SELECT on tables that could
> change, then of course it isn't; so you are going to need to tell a
> little white lie here).
>
> Because of that, you need to be careful that you pass at least one
> nonconstant argument to the function within the CHECK expression, else
> you risk having the call constant-folded too early.  Normally you'd
> probably pass values from the checked row into the function, so this
> doesn't seem like it should be a problem, but I could see someone
> getting bit by it someday ...

Thanks for the clarification. Is this in the documentation somewhere?
I'm not quite used to the new documentation ordering yet.

Michael
grzm myrealbox com

Michael Glaesemann <grzm@myrealbox.com> writes:
> On Thursday, November 6, 2003, at 02:38 AM, Tom Lane wrote:
>> IIRC we check that it is labeled IMMUTABLE.

> Thanks for the clarification. Is this in the documentation somewhere?

[digs...] No, because my recollection is wrong: there's no such check.

regression=# create function foo() returns bool as 'select true' language sql;
CREATE FUNCTION
regression=# create table foo (f1 int check (foo()));
CREATE TABLE

Possibly there should be such a restriction, but there isn't today...

            regards, tom lane

On Thursday, November 6, 2003, at 03:12 AM, Tom Lane wrote:

> Michael Glaesemann <grzm@myrealbox.com> writes:
>> On Thursday, November 6, 2003, at 02:38 AM, Tom Lane wrote:
>>> IIRC we check that it is labeled IMMUTABLE.
>
>> Thanks for the clarification. Is this in the documentation somewhere?
>
> [digs...] No, because my recollection is wrong: there's no such check.

Thanks for, erm, checking! (Okay, I'm off to bed now.)

Michael
grzm myrealbox com