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
