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