Is postgres going to support in a future release the use of SUBSELECT in a CHECK expression ??
Thx
Thx
-
-
Re: Using SUBSELECT in CHECK expressions
"Najib Abi Fadel" <nabifadel@usj. edu.lb> writes:[color=blue]
> Is postgres going to support in a future release the use of SUBSELECT in a =
> CHECK expression ??[/color]
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
-
Re: Using SUBSELECT in CHECK expressions
On Tue, 4 Nov 2003, Najib Abi Fadel wrote:
[color=blue]
> Is postgres going to support in a future release the use of SUBSELECT in
> a CHECK expression ??[/color]
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
Comment
-
Re: Using SUBSELECT in CHECK expressions
On Wednesday, November 5, 2003, at 06:26 AM, Tom Lane wrote:[color=blue]
> 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.[/color]
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 majordomo@postg resql.org so that your
message can get through to the mailing list cleanly
Comment
-
Re: Using SUBSELECT in CHECK expressions
Michael Glaesemann <grzm@myrealbox .com> writes:[color=blue]
> On Wednesday, November 5, 2003, at 06:26 AM, Tom Lane wrote:[color=green]
>> Just put the SELECT into a function that's called by the CHECK
>> expression.[/color][/color]
[color=blue]
> 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?[/color]
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
Comment
-
Re: Using SUBSELECT in CHECK expressions
On Thursday, November 6, 2003, at 02:38 AM, Tom Lane wrote:
[color=blue]
> Michael Glaesemann <grzm@myrealbox .com> writes:[color=green]
>> On Wednesday, November 5, 2003, at 06:26 AM, Tom Lane wrote:[color=darkred]
>>> Just put the SELECT into a function that's called by the CHECK
>>> expression.[/color][/color]
>[color=green]
>> 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?[/color]
>
> 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 ...[/color]
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 YourEmailAddres sHere" to majordomo@postg resql.org)
Comment
-
Re: Using SUBSELECT in CHECK expressions
Michael Glaesemann <grzm@myrealbox .com> writes:[color=blue]
> On Thursday, November 6, 2003, at 02:38 AM, Tom Lane wrote:[color=green]
>> IIRC we check that it is labeled IMMUTABLE.[/color][/color]
[color=blue]
> Thanks for the clarification. Is this in the documentation somewhere?[/color]
[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
Comment
-
Re: Using SUBSELECT in CHECK expressions
On Thursday, November 6, 2003, at 03:12 AM, Tom Lane wrote:
[color=blue]
> Michael Glaesemann <grzm@myrealbox .com> writes:[color=green]
>> On Thursday, November 6, 2003, at 02:38 AM, Tom Lane wrote:[color=darkred]
>>> IIRC we check that it is labeled IMMUTABLE.[/color][/color]
>[color=green]
>> Thanks for the clarification. Is this in the documentation somewhere?[/color]
>
> [digs...] No, because my recollection is wrong: there's no such check.[/color]
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
Comment
Comment