Thread: Subqueries in Check() -- Still Intentionally Omitted?
I am curious if the motivation is still valid for intentionally omitting check sub-queries. (what was the motivation to begin with?) Since we can effectively work around this limitation by doing the same thing with a function in a CHECK constraint, why would we want to prevent anyone from using the standard syntax for achieving the same effect? As a side point, for consistency I think that the CREATE ASSERTION feature should have the same comments as a check() sub-query, since it is very similar in purpose. http://www.postgresql.org/docs/8.3/interactive/unsupported-features-sql-standard.html F671 | Enhanced integrity management | Sub queries in CHECK | intentionally omitted -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Richard Broersma escribió: > I am curious if the motivation is still valid for intentionally > omitting check sub-queries. (what was the motivation to begin with?) The problem is that you have to rerun the query to verify that the CHECK condition still holds, whenever the table that the CHECK clause is checking changes. This is rather problematic, because we'd need to make the system aware of such reverse dependencies. The usual workaround is only enough protection if you trust that the table referenced in the CHECK query does not change. If the query references something other than a table (say a function), it gets even more messy. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
"Richard Broersma" <richard.broersma@gmail.com> writes:
> I am curious if the motivation is still valid for intentionally
> omitting check sub-queries. (what was the motivation to begin with?)
> Since we can effectively work around this limitation by doing the same
> thing with a function in a CHECK constraint, why would we want to
> prevent anyone from using the standard syntax for achieving the same
> effect?
Because if we supported the standard syntax, we'd also have to support
the standard semantics; which a function-in-CHECK does *not* give you.
The standard says that the constraint is guaranteed not to be violated,
which in the worst case means that any time you update the table(s)
referenced in the subquery, you have to retest the CHECK expression
at every row of the table having the constraint. Consider for instance
CREATE TABLE t1 (x int CHECK (x < (SELECT sum(y) FROM t2)));
If we change some value of t2.y, do all values of t1.x still satisfy
their constraint?
In some cases, with enough intelligence you could optimize this into
something fast enough to be usable; but it's a research problem.
(The cases that I can see how to optimize are pretty much equivalent to
plain foreign key constraints, anyway.)
regards, tom lane
On Tue, 2008-09-02 at 15:30 -0700, Richard Broersma wrote:
> I am curious if the motivation is still valid for intentionally
> omitting check sub-queries. (what was the motivation to begin with?)
>
> Since we can effectively work around this limitation by doing the same
> thing with a function in a CHECK constraint, why would we want to
Wow, I assumed you needed an immutable function for that to work. Then I
tried it:
=> create table foo(i int check (random() > 0.5));
My question is not why don't we allow subqueries in CHECK, my question
is why do we allow stable/volatile functions?
As I understand it, CHECK is meant for simple declarative tuple
constraints. It's not designed for sophisticated inter-relation
constraints -- or even intra-relation constraints, for that matter.
Consider:
CREATE TABLE foo(
...
CHECK ((SELECT COUNT(*) FROM foo) < 10)
);
We'd need some big locks for that to actually be a true declaration.
All of this can be solved with triggered procedures, where you can
define the locks as needed.
Regards,
Jeff Davis
On Tue, 2008-09-02 at 18:47 -0400, Alvaro Herrera wrote:
> The problem is that you have to rerun the query to verify that the CHECK
> condition still holds, whenever the table that the CHECK clause is
> checking changes. This is rather problematic, because we'd need to make
> the system aware of such reverse dependencies.
Even if you re-ran the query, how do you avoid the race condition?
For example:
CREATE TABLE foo(
...
CHECK ((SELECT COUNT(*) FROM foo) < 10)
);
If another transaction commits between the time you re-run the query and
the time you commit, the CHECK will be violated.
From an arbitrary subquery in a CHECK, it's hard to determine what kind
of locking semantics might be necessary for inserting transactions.
I think this is precisely what triggers are for. You define the error
condition and the locking semantics in one procedure.
Regards,
Jeff Davis
Jeff Davis <pgsql@j-davis.com> writes:
> My question is not why don't we allow subqueries in CHECK, my question
> is why do we allow stable/volatile functions?
Historically we've allowed it, and it's not clear what we'd buy by
changing that, other than breaking existing applications whose authors
forgot to mark their functions immutable. If there were something we
could usefully do by checking the mutability status of the condition,
then it would be worth breaking compatibility here...
regards, tom lane
On Tue, 2008-09-02 at 18:57 -0400, Tom Lane wrote:
> The standard says that the constraint is guaranteed not to be violated,
> which in the worst case means that any time you update the table(s)
> referenced in the subquery, you have to retest the CHECK expression
> at every row of the table having the constraint. Consider for instance
> CREATE TABLE t1 (x int CHECK (x < (SELECT sum(y) FROM t2)));
> If we change some value of t2.y, do all values of t1.x still satisfy
> their constraint?
>
And as I pointed out to Alvaro, I believe there is a race there as well.
[ say t1 and t2 start empty ]
s1=> insert into t2 values(5); -- checks condition, ok
s1=> BEGIN;
s2=> BEGIN;
s1=> insert into t1 values(4);
s2=> update t2 set y = 3;
s1=> -- checks condition, sees sum(y)=5, ok
s2=> -- checks condition, sees no tuples in t1, ok
s1=> COMMIT;
s2=> COMMIT; -- wrong!
The only solution is a big lock, or at least to somehow figure out what
kind of locks might be required.
Regards,
Jeff Davis
On Tue, 2008-09-02 at 19:22 -0400, Tom Lane wrote:
> Jeff Davis <pgsql@j-davis.com> writes:
> > My question is not why don't we allow subqueries in CHECK, my question
> > is why do we allow stable/volatile functions?
>
> Historically we've allowed it, and it's not clear what we'd buy by
> changing that, other than breaking existing applications whose authors
> forgot to mark their functions immutable. If there were something we
> could usefully do by checking the mutability status of the condition,
> then it would be worth breaking compatibility here...
>
I suppose this means that we're already treating any CHECK constraint as
immutable anyway, e.g. for constraint_exclusion?
Regards,
Jeff Davis
On Tue, Sep 2, 2008 at 3:47 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > The problem is that you have to rerun the query to verify that the CHECK > condition still holds, whenever the table that the CHECK clause is > checking changes. This is rather problematic, because we'd need to make > the system aware of such reverse dependencies. Thanks for the clarification. This makes sense. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Jeff Davis <pgsql@j-davis.com> writes:
> On Tue, 2008-09-02 at 19:22 -0400, Tom Lane wrote:
>> Jeff Davis <pgsql@j-davis.com> writes:
>>> My question is not why don't we allow subqueries in CHECK, my question
>>> is why do we allow stable/volatile functions?
>>
>> Historically we've allowed it,
> I suppose this means that we're already treating any CHECK constraint as
> immutable anyway, e.g. for constraint_exclusion?
I think the constraint_exclusion code does check for immutability before
assuming it can deduce exclusion. But the ordinary use of CHECK just
assumes it only needs to evaluate the check at tuple insertion.
regards, tom lane
Tom Lane wrote: > "Richard Broersma" <richard.broersma@gmail.com> writes: > > I am curious if the motivation is still valid for intentionally > > omitting check sub-queries. (what was the motivation to begin with?) > > > Since we can effectively work around this limitation by doing the same > > thing with a function in a CHECK constraint, why would we want to > > prevent anyone from using the standard syntax for achieving the same > > effect? > > Because if we supported the standard syntax, we'd also have to support > the standard semantics; which a function-in-CHECK does *not* give you. > > The standard says that the constraint is guaranteed not to be violated, > which in the worst case means that any time you update the table(s) > referenced in the subquery, you have to retest the CHECK expression > at every row of the table having the constraint. Consider for instance > CREATE TABLE t1 (x int CHECK (x < (SELECT sum(y) FROM t2))); > If we change some value of t2.y, do all values of t1.x still satisfy > their constraint? > > In some cases, with enough intelligence you could optimize this into > something fast enough to be usable; but it's a research problem. > (The cases that I can see how to optimize are pretty much equivalent to > plain foreign key constraints, anyway.) Is this a TODO? I assume it is not. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +