RE: subselect in CHECK constraint?

> -----Original Message-----
> From: Ian Turner
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> When I try to do this:
>
> CREATE TABLE test (
>     a Integer,
>     b Integer,
>     CHECK ((SELECT SUM(t.a) FROM test t WHERE t.b = b) < 1000)
> );
>
> INSERT INTO test (a, b) VALUES (100, 2);
>
> I get this error on the second query:
>
> ERROR:  ExecEvalExpr: unknown expression type 108
>
> I'm guessing this means I can't do subselects in CHECK statements.
>

Yes.
It would be very difficult to implement constraints other than column
constraints. There seems to be 2 reasons at least.
1) We have to check the constraint not only for the row itself which is
    about to be insert/update/deleted but also for other related rows.
    As for your case,if b is updated the constraints not only for new b
    but also for old b should be checked. If the WHERE clause is more
    complicated what kind of check should we do ?
2) The implementation is very difficult without acquiring a table level
    locking.  As for your case I couldn't think of any standard way to
    prevent the following other than acquiring a table level locking.

    When there's no row which satisfies b = 2,two backends insert values
    (500, 2) at the same time.

Regards.

Hiroshi Inoue