Обсуждение: subselect in CHECK constraint?
-----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. Let me pose another question, if this is the case: Say I have a table of warehouses. I then want to have another table keep track of the products at the warehouse, such that the amount of product at a warehouse does not exceed the capacity of the warehouse. Which probably means I would need to have a CHECK statement with a select on each side of the operator. Any ideas? :o I'd look at the source to see what this error means, except that I am on a dialup. Ian Turner -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.1 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE5syqYfn9ub9ZE1xoRAraCAKCFL7iMHuS62dyYlMMfY84FLG1LvQCgsUYi T3wCLoCqsojQ0WCDdkLjVPg= =tJoC -----END PGP SIGNATURE-----
* 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-----