pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
No subselects in constraint (bug?)
3+ messages / 3 participants
[nested] [flat]

* No subselects in constraint (bug?)
@ 2001-07-13 10:16  Alexey V. Neyman <avn@any.ru>
  0 siblings, 2 replies; 3+ messages in thread

From: Alexey V. Neyman @ 2001-07-13 10:16 UTC (permalink / raw)
  To: pgsql-sql; +Cc: pgsql-bugs@postgresql.org

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?

I use 7.0.3, but 7.1.2 code looks pretty the same in ExecEvalExpr(). The
platform is FreeBSD 4.3-R.

TIA for responses,
Alexey.

-- 
-------------------------------------------------+---------------------------
             Yes.  We have good news!            | Regards, Alexey V. Neyman
      Well, that is to say, we have no news.     |     mailto: avn@any.ru
----------------------------------( Pkunk, SC2 )-+---------------------------




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: No subselects in constraint (bug?)
@ 2001-07-13 18:25  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Alexey V. Neyman <avn@any.ru>
  1 sibling, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2001-07-13 18:25 UTC (permalink / raw)
  To: Alexey V. Neyman <avn@any.ru>; +Cc: pgsql-sql; pgsql-bugs@postgresql.org

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).




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: No subselects in constraint (bug?)
@ 2001-07-13 19:00  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Alexey V. Neyman <avn@any.ru>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2001-07-13 19:00 UTC (permalink / raw)
  To: Alexey V. Neyman <avn@any.ru>; +Cc: pgsql-sql; pgsql-bugs@postgresql.org

"Alexey V. Neyman" <avn@any.ru> writes:
> CREATE TABLE b (
>   int4 id
>   CHECK (id = ANY(SELECT a.id FROM a))
> );

> ERROR:  ExecEvalExpr: unknown expression type 108

More recent versions say

ERROR:  Cannot use subselect in CHECK clause

It seems to me that what you really want here is a foreign key
reference, anyway, not a handmade CHECK constraint.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-07-13 19:00 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-13 10:16 No subselects in constraint (bug?) Alexey V. Neyman <avn@any.ru>
2001-07-13 18:25 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-07-13 19:00 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox