Re: No subselects in constraint (bug?) - Mailing list pgsql-bugs

From Stephan Szabo
Subject Re: No subselects in constraint (bug?)
Date
Msg-id Pine.BSF.4.21.0107131120241.10523-100000@megazone23.bigpanda.com
Whole thread Raw
In response to No subselects in constraint (bug?)  ("Alexey V. Neyman" <avn@any.ru>)
List pgsql-bugs
On Fri, 13 Jul 2001, Alexey V. Neyman wrote:

> Hello there!
> 
> [Please Cc: me in followups.]
> 
> I tried the following:
> 
> CREATE TABLE a (
>   int4 id
> );
> CREATE TABLE b (
>   int4 id
>   CHECK (id = ANY(SELECT a.id FROM a))
> );
> 
> Tables are created ok, checking with '\d table' confirms it. But when I
> try to insert into table b, e.g.:
> INSERT INTO b (id)
>   VALUES (0);
> I get:
> ERROR:  ExecEvalExpr: unknown expression type 108
> Of course, the tuple is not inserted.
> 
> As quick dig of code showed, type 108 is T_SubLink which is created for
> ANY() subselect, and ExecEvalExpr() function does not handle this type of
> node. Is it intentional or a bug?

It's unimplemented, and really should fail at create time (I'm not
sure if it does in 7.1). IIRC, it's only required at FULL SQL92 level
(intermediate level has a no subqueries limitation).  The reason is
that the constraint you are making as part of b also constrains 
table a and it's not entirely trivial to support complicated subquery
constraints within the current system.

As a workaround for now, you'll probably have to use triggers on a and
b to do the check.  (before insert trigger on b and a delete/update
trigger on a).



pgsql-bugs by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: bug report: tuple is too big
Next
From: Tom Lane
Date:
Subject: Re: No subselects in constraint (bug?)