subselect in CHECK constraint?

* Ian Turner <vectro@pipeline.com> [000903 22:37] wrote:

-----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.

Two things:

1) i'm pretty sure this subselect can be rewritten as:
SELECT SUM(t.a) < 1000 FROM test t WHERE t.b = b
to return a boolean.

2) you can probably get away with using a plpgsql function
that has more logic in it.

I'm not saying that subselects do or do not work, just offering
some alternative advice.

-Alfred

-----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

Ian Turner <vectro@pipeline.com> writes:

I'm guessing this means I can't do subselects in CHECK statements.

Right. Pushing the SELECT into a SQL or PLPGSQL function that's called
by the constraint is a good workaround, ie

CHECK (testconstraint(a, b))

where FUNCTION testconstraint(a int, b int) RETURNS bool does all the
heavy lifting. If you use plpgsql there should be a performance
advantage too --- the query plan for the function will be cached for
re-use across calls, which is not true for the text of CHECK conditions.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

CHECK (testconstraint(a, b))

Uhhh. I get no errors, but it dosen't work, either. Consider:

CREATE FUNCTION testconstraint(int,int) RETURNS bool AS '
BEGIN
RETURN (select sum(a) FROM test WHERE b = $2) < 1000;
END;
' LANGUAGE 'plpgsql';

CREATE TABLE test (a int, b int, CHECK (testconstraint(a,b)));

INSERT INTO test (a,b) VALUES (1100, 1);

SELECT * FROM test;

Yielding:

a | b
- ------+---
1100 | 1
(1 row)

which clearly does not satisfy the constraint.

Ian
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE5s8/6fn9ub9ZE1xoRAuiRAKCHh/wWSl7uYzhJGWnc7kc0OxqZogCgpMCN
MdTBSXm7w0C4R4Ghh77+8ok=
=nik7
-----END PGP SIGNATURE-----

Ian Turner <vectro@pipeline.com> writes:

Uhhh. I get no errors, but it dosen't work, either. Consider:

I didn't say that you would like the semantics ;-).

The check constraint is going to be evaluated *before* the proposed
new tuple is inserted into the table, not after; so doing a select
on the same table won't see the new tuple.

Also, as several other people already pointed out, a constraint
involving a select could be violated in many ways including alteration
or removal of tuples in other tables. We only evaluate check
constraints when we insert/update tuples in the table they are attached
to...

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Also, as several other people already pointed out, a constraint
involving a select could be violated in many ways including alteration
or removal of tuples in other tables. We only evaluate check
constraints when we insert/update tuples in the table they are attached
to...

OK.

Is this something that could be accomplished with triggers? :o

Also, is it possible to have a foreign key constraint across multiple
columns? :o

Ian
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE5s9xxfn9ub9ZE1xoRAo9WAJ0blihjzEQFo+3clEGRsySjkUzrqgCdEIhe
8VumU6bICMN6jUHCdq0WSYM=
=niuY
-----END PGP SIGNATURE-----