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