Thread: Problem with subquery in CHECK constraint.
Hi,
I'm using a general lookup table defined as following
to avoid a proliferation of lookup tables:
CREATE TABLE XREF
( XREF_GROUP VARCHAR(12) NOT NULL, XREF_CD VARCHAR(8) NOT NULL, XREF_VALUE
VARCHAR(128),
PRIMARY KEY (XREF_GROUP, XREF_CD)
);
INSERT INTO XREF VALUES ('CUST_TYPE', 'EXP', 'Expired');
INSERT INTO XREF VALUES ('CUST_TYPE', 'ACTIV', 'Active');
INSERT INTO XREF VALUES ('CUST_TYPE', 'POT', 'Potential');
I'm trying to define a check constraint to validate
lookup codes used, for example:
CREATE TABLE CUST
( CUST_ID INTEGER NOT NULL, NAME VARCHAR(64) NOT NULL, TYPE_CD VARCHAR(8)
NOTNULL, CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID), CONSTRAINT TYPE_CD_OK CHECK ( EXISTS (SELECT 1
FROMXREF WHERE XREF_GROUP = 'CUST_TYPE' AND XREF_CD = TYPE_CD) )
);
However when trying to insert into CUST I get the following error:
ERROR: ExecEvalExpr: unknown expression type 108
Is this a bug in PostGreSQL? I can work around it by
defining a function and using it in the CHECK constraint
for now. See the appended test file for example SQL.
By the way, there was a massive performance difference in using:
SELECT $2 IS NULL OR EXISTS (SELECT 1 FROM XREF WHERE XREF_GROUP = $1 AND XREF_CD = $2)
versus the slower:
SELECT $2 IN (SELECT XREF_CD FROM XREF WHERE XREF_GROUP = $1)
for validation, is this to be expected?
Here's the test case:
DROP TABLE XREF;
CREATE TABLE XREF
( XREF_GROUP VARCHAR(12) NOT NULL, XREF_CD VARCHAR(8) NOT NULL, XREF_VALUE
VARCHAR(128),
PRIMARY KEY (XREF_GROUP, XREF_CD)
);
INSERT INTO XREF VALUES ('CUST_TYPE', 'EXP', 'Expired');
INSERT INTO XREF VALUES ('CUST_TYPE', 'ACTIV', 'Active');
INSERT INTO XREF VALUES ('CUST_TYPE', 'POT', 'Potential');
DROP FUNCTION VALID_XREF(VARCHAR(12), VARCHAR(8));
CREATE FUNCTION VALID_XREF(VARCHAR(12), VARCHAR(8)) RETURNS BOOLEAN AS 'SELECT $2 IS NULL OR EXISTS (SELECT 1
FROMXREF WHERE XREF_GROUP = $1 AND XREF_CD = $2)'
LANGUAGE 'SQL';
--
-- Much slower version:
--
-- 'SELECT $2 IN (SELECT XREF_CD FROM XREF WHERE XREF_GROUP = $1)'
--
DROP TABLE CUST;
CREATE TABLE CUST
( CUST_ID INTEGER NOT NULL, NAME VARCHAR(64) NOT NULL, TYPE_CD VARCHAR(8)
NOTNULL,
CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID), CONSTRAINT TYPE_CD_OK CHECK ( EXISTS (SELECT 1 FROM XREF WHERE
XREF_GROUP = 'CUST_TYPE' AND XREF_CD = TYPE_CD) )
);
INSERT INTO CUST VALUES (1, 'Joe Bloggs', 'POT');
DROP TABLE CUST;
CREATE TABLE CUST
( CUST_ID INTEGER NOT NULL, NAME VARCHAR(64) NOT NULL, TYPE_CD VARCHAR(8)
NOTNULL,
CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID), CONSTRAINT TYPE_CD_OK CHECK (VALID_XREF('CUST_TYPE', TYPE_CD))
);
INSERT INTO CUST VALUES (1, 'Joe Bloggs', 'ACTIV');
INSERT INTO CUST VALUES (2, 'Jim Smith', 'foo');
--
Niall Smart
email: niall.smart@ebeon.com
phone: (087) 8052390
Niall Smart <niall.smart@ebeon.com> writes:
> However when trying to insert into CUST I get the following error:
> ERROR: ExecEvalExpr: unknown expression type 108
> Is this a bug in PostGreSQL?
Yup. Looks like the subquery is not getting expanded into a subplan.
Could be a little messy to fix, unless we redo the planning on every
execution of the check constraint ... which doesn't sound appetizing.
For now I'd suggest putting the subquery into an SQL or plpgsql function
that you invoke from the constraint expression.
regards, tom lane
> -----Original Message----- > From: pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]On Behalf > Of Tom Lane > > Niall Smart <niall.smart@ebeon.com> writes: > > However when trying to insert into CUST I get the following error: > > ERROR: ExecEvalExpr: unknown expression type 108 > > Is this a bug in PostGreSQL? > > Yup. Looks like the subquery is not getting expanded into a subplan. > Could be a little messy to fix, unless we redo the planning on every > execution of the check constraint ... which doesn't sound appetizing. > There seems to be more serious problems. 1) The constraint is not only for the defined table but also for referenced tables in the subquery. 2) There should be some standard lock mechanism for the range restricted by the subquery. I'm suspicious that we should/could implement constraints other than column constraints. Regards. Hiroshi Inoue Inoue@tpf.co.jp
CONSTRAINT TYPE_CD_OK CHECK (EXISTS (SELECT 1 FROM XREF WHERE XREF_GROUP = 'CUST_TYPE' AND XREF_CD = TYPE_CD) ) > There seems to be more serious problems. > 1) The constraint is not only for the defined table but also for referenced > tables in the subquery. I don't understand what you mean -- the constraint only constrains 1 column in one table... > 2) There should be some standard lock mechanism for the range restricted > by the subquery. > > I'm suspicious that we should/could implement constraints other than > column constraints. Again, I don't fully understand what you're saying; but I have successfully implemented the constraint using a user defined function. Niall
> -----Original Message----- > From: pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]On > Behalf Of Niall Smart > > > CONSTRAINT TYPE_CD_OK CHECK ( > EXISTS (SELECT 1 FROM XREF WHERE > XREF_GROUP = 'CUST_TYPE' AND > XREF_CD = TYPE_CD) > ) > > > > There seems to be more serious problems. > > 1) The constraint is not only for the defined table but also > for referenced > > tables in the subquery. > > I don't understand what you mean -- the constraint only > constrains 1 column in one table... > Doesn't the constraint mean thatfor any row in table CUST,there *always* exist some rows inthe table XREF such that satisfiesXREF_GROUP='CUST_TYPE'AND XREF_CD=TYPE_CD ? If all such rows are deleted from the table XREF,above condition isn't satisfied any longer. So isn't the constraint for the table XREF either ? Regards. Hiroshi Inoue Inoue@tpf.co.jp
> CONSTRAINT TYPE_CD_OK CHECK (
> EXISTS (SELECT 1 FROM XREF WHERE
> XREF_GROUP = 'CUST_TYPE' AND
> XREF_CD = TYPE_CD)
> )
>
>
> > There seems to be more serious problems.
> > 1) The constraint is not only for the defined table but also for
referenced
> > tables in the subquery.
>
> I don't understand what you mean -- the constraint only
> constrains 1 column in one table...
Think of this sequence:
insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '1');
insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '2');
insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '3');
insert into cust (cust_id, name, type_cd) values (1, 'a', '1');
insert into cust (cust_id, name, type_cd) values (2, 'b', '2');
delete from xref where xref_cd='1';
I believe that technically the delete should fail because it breaks the
type_cd_ok constraint, but right now, we wouldn't be even checking
that constraint to notice that we're breaking it.
Hiroshi Inoue wrote:
> > From: pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]On
> > Behalf Of Niall Smart
> >
> > CONSTRAINT TYPE_CD_OK CHECK (
> > EXISTS (SELECT 1 FROM XREF WHERE
> > XREF_GROUP = 'CUST_TYPE' AND
> > XREF_CD = TYPE_CD)
> > )
> >
> >
> > > There seems to be more serious problems.
> > > 1) The constraint is not only for the defined table but also
> > for referenced
> > > tables in the subquery.
> >
> > I don't understand what you mean -- the constraint only
> > constrains 1 column in one table...
>
> Doesn't the constraint mean that
> for any row in table CUST,there *always* exist some rows in
> the table XREF such that satisfies XREF_GROUP='CUST_TYPE'
> AND XREF_CD=TYPE_CD ?
>
> If all such rows are deleted from the table XREF,above condition
> isn't satisfied any longer. So isn't the constraint for the table XREF
> either ?
Ah, I see what you mean now. Well, thats an interesting point,
as I originally tried to define the constraint using the
foreign key syntax. Perhaps a better way to support this
functionality is to allow constants in the source columns
of a foreign key constraint, for example:
CONSTRAINT TYPE_CD_OK FOREIGN KEY ('CUST_TYPE' AS XREF_GROUP, XREF_CD) REFERENCES XREF
However I would submit that constraint clauses are
effectively before-insert validation triggers and that
it is up to the database designer to use more robust
methods when they wish to enforce referential integrity.
Note that it is possible to define a CHECK constraint using
a function, in which case it is impossible to determine if
the function is expressing a relational integrity constraint.
Niall