Re: Subqueries in Check() -- Still Intentionally Omitted?

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