Using subselects in INSERTs?

From: J Smith <dark_panda(at)hushmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Using subselects in INSERTs?
Date: 2003-10-17 19:43:51
Message-ID: bmpgpm$ll$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


After doing some googling through the lists and such, I wasn't able to
arrive at a solution for a problem I've run into recently. I've seen
mention of similar symptoms, but my case seems different.

After recently upgrading to 7.3.4 from 7.3.1, I started having problems with
using subselects in INSERTs. I'm seeing things like this (simplified, as
this is a part of a larger database):

INSERT INTO t1 (name) VALUES ('foo');
INSERT 123 1
INSERT INTO t2 (name, ref) VALUES ('bar', (SELECT id FROM t1 WHERE name =
'foo'));
ERROR: ExecEvalExpr: unknown expression type 108

The tables look something like

CREATE TABLE t1 (
id serial not null primary key,
name text not null unique
);

CREATE TABLE t2 (
id serial not null primary key,
name text not null,
ref integer references t1(id) on update cascade on delete cascade
);

This was working fine in 7.3.2 and below, but started breaking in 7.3.3. The
same pg_dumps were used on each to create the test databases.

Is this behaviour intentional? I've read many of the previous posts about
this error, but I didn't see any that were caused by subqueries in INSERTs.
Everything else had to do with CHECKs and SELECTs and such. I find it weird
that this would start breaking in between minor releases at any rate.
(Maybe in 7.4, but from 7.3.2 to 7.3.3?)

For reference, works on:

test=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96

Dies on:

test=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC 2.96

Anyone seen this and have some advice?

J

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Stark 2003-10-17 20:12:05 Re: ShmemAlloc errors
Previous Message CSN 2003-10-17 19:43:35 restart and postgres.conf