Using SUBSELECT in CHECK expressions

[Najib Abi Fadel]

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

Thx

[Tom Lane]

"Najib Abi Fadel" <na*******@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Michael Glaesemann]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Michael Glaesemann <gr**@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Michael Glaesemann]

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

Michael Glaesemann <gr**@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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Michael Glaesemann <gr**@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Michael Glaesemann]

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

Michael Glaesemann <gr**@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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match