Re: Using subselects in INSERTs?

I managed to trace the problem back to a RULE that was created while I must
have been asleep at the wheel. The problem goes away when I disable the
RULE or replace the subquery with an actual value.

Attached is the most simplified example I have that still replicates the
problem. The first attachment (test.pgdump) is the database schema, the
second (test.sql) is an example of what the inserts look like. The RULE
could really be replaced with a constraint I suppose, but when you see it
with all of the rest of the tables and the database in place it makes a bit
more sense.

Either way, it's strange that a subquery would cause problems but using an
actual value in it's place goes through. (Stranger still, the first INSERT
using a subquery works but subsequent INSERTs fail.)

I tried this example on both a 7.3.4 system and a 7.3.2 system, and they
both produced the same results, so evidently my previous post is incorrect
as far as the version numbers are concerned. (Late night that night I
guess...)

If anyone is still curious about compilers, options, etc., the 7.3.4 system
was compiled using gentoo's standard ebuild. I believe the configure line
works out to:

configure --with-openssl --enable-nls --with-python --with-perl --with-CXX
--with-pam

Compiled with GCC 3.2.3.

The 7.3.2 system is a straight "./configure" on an RH 7.3 system with GCC
2.96.

I guess the only question now is why using a subquery fails but using an
actual value does not. Perhaps there's just something wrong with that
RULE...?

J

Tom Lane wrote:

> Could you provide an *exact* sequence-to-reproduce rather than
> handwaving? Maybe I'm just missing the problem. Also, what platform,
> compiler, and configure arguments are you using, exactly?
>
> regards, tom lane
>