Skip site navigation (1) Skip section navigation (2)

Re: No subselects in constraint (bug?)

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: "Alexey V(dot) Neyman" <avn(at)any(dot)ru>
Cc: pgsql-sql(at)postgresql(dot)org, pgsql-bugs(at)postgresql(dot)org
Subject: Re: No subselects in constraint (bug?)
Date: 2001-07-13 18:25:45
Message-ID: Pine.BSF.4.21.0107131120241.10523-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-sql
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).


In response to

pgsql-bugs by date

Next:From: Peter EisentrautDate: 2001-07-13 18:29:39
Subject: Re: bug report: tuple is too big
Previous:From: Lamar OwenDate: 2001-07-13 18:19:58
Subject: Re: Upgrade to 7.1.2 problem

pgsql-sql by date

Next:From: Joe ConwayDate: 2001-07-13 18:57:19
Subject: Re: How can we match a condition among 2 diff. tables?
Previous:From: Josh BerkusDate: 2001-07-13 17:44:39
Subject: Re: Date Validation?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group