Re: BUG #11107: UPDATE violates table check constraint

jesse(dot)denardo(at)myfarms(dot)com writes:
> After creating two test tables and a table check constraint on one of them,
> I use an UPDATE statement to update one of the rows. This update causes the
> row to violate the check constraint, but the update succeeds anyways.
> Dropping and re-adding the check constraint then fails because the
> constraint is violated.

Sorry, but this check constraint has entirely undefined behavior, as does
any check constraint that refers to data rows other than the one that is
being checked.

> -- Check function to use as constraint
> CREATE OR REPLACE FUNCTION fn_chk_constraint (
> b_id integer,
> a_id integer
> ) RETURNS boolean AS $$
> SELECT 0 = (
> SELECT count(*)
> FROM a
> JOIN b ON b.a_id = a.id AND b.num <> a.num
> WHERE a.id = a_id
> AND b.id = b_id
> )
> $$ LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;

The "immutable" marking of this function is a lie on its face; not that
a proper marking would have helped.

The reason the constraint doesn't fail is that Postgres checks it before
storing the updated row, not after. But even without that, there would be
no way to guarantee proper maintenance of the constraint --- what if
somebody changes table a, or if there are two concurrent changes to
table b that individually are still consistent but not when taken
together? (I've not tried to work out whether the latter is possible
given this specific condition, but in general it's a problem if a
check constraint on b tries to examine other rows in b.)

This is why we don't allow sub-SELECTs in check conditions. You
can escape that syntactic constraint by embedding the sub-SELECT
in a function as you've done here, but that isn't going to make
it work.

regards, tom lane