By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,200 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

Using subselects in INSERTs?

P: n/a

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
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
J Smith <da********@hushmail.com> writes:
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 t2 (name, ref) VALUES ('bar', (SELECT id FROM t1 WHERE name =
'foo'));
ERROR: ExecEvalExpr: unknown expression type 108
I tried to reproduce this, but the given example works fine on my 7.3.*
installation. I also looked through the post-7.3.1 changes in the 7.3
CVS branch to see if there was anything related, but didn't see
anything.

One possible theory is that you didn't do a clean rebuild after updating
your source tree to 7.3.4 ... but I'm not sure how tenable that is; it'd
only be likely to matter if we changed in-memory data structures in the
7.3 branch, and I didn't notice anything like that in perusing the logs.
The tables look something like


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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #2

P: n/a

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


Nov 12 '05 #3

P: n/a
J Smith <da********@hushmail.com> writes:
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.
I dug into this and found that the misbehavior occurs when the
sub-SELECT that is present in the INSERT:
INSERT INTO clip (program_id, clip_name) VALUES (
(SELECT program_id FROM program WHERE program_code = '9531443001'),
'Canada: A Diverse Culture');
is inserted to replace "new.program_id" in the RULE:
CREATE RULE program_clip_insert_only_1 AS ON INSERT TO clip WHERE
((SELECT count(*) AS count FROM clip WHERE clip.program_id =
new.program_id) >= 1) DO INSTEAD NOTHING;


As far as I can tell, this problem has existed for a long time; it is
certainly not new in 7.3.4. (I see the same failure in 7.2.4 as 7.3.4.)
Are you sure you weren't changing your application at the same time you
updated?

I've applied the attached patch to the 7.3 branch, if you want to use
it.

regards, tom lane
Index: src/backend/rewrite/rewriteManip.c
================================================== =================
RCS file: /cvsroot/pgsql-server/src/backend/rewrite/rewriteManip.c,v
retrieving revision 1.67
diff -c -r1.67 rewriteManip.c
*** src/backend/rewrite/rewriteManip.c 20 Oct 2002 00:58:55 -0000 1.67
--- src/backend/rewrite/rewriteManip.c 20 Oct 2003 19:09:48 -0000
***************
*** 74,80 ****
checkExprHasSubLink(Node *node)
{
/*
! * If a Query is passed, examine it --- but we will not recurse into
* sub-Queries.
*/
if (node && IsA(node, Query))
--- 74,80 ----
checkExprHasSubLink(Node *node)
{
/*
! * If a Query is passed, examine it --- but we need not recurse into
* sub-Queries.
*/
if (node && IsA(node, Query))
***************
*** 644,653 ****
/*
* Make sure query is marked correctly if added qual has sublinks or
* aggregates (not sure it can ever have aggs, but sublinks
! * definitely).
*/
! parsetree->hasAggs |= checkExprHasAggs(copy);
! parsetree->hasSubLinks |= checkExprHasSubLink(copy);
}

/*
--- 644,655 ----
/*
* Make sure query is marked correctly if added qual has sublinks or
* aggregates (not sure it can ever have aggs, but sublinks
! * definitely). Need not search qual when query is already marked.
*/
! if (!parsetree->hasAggs)
! parsetree->hasAggs = checkExprHasAggs(copy);
! if (!parsetree->hasSubLinks)
! parsetree->hasSubLinks = checkExprHasSubLink(copy);
}

/*
***************
*** 684,693 ****
/*
* Make sure query is marked correctly if added qual has sublinks or
* aggregates (not sure it can ever have aggs, but sublinks
! * definitely).
*/
! parsetree->hasAggs |= checkExprHasAggs(copy);
! parsetree->hasSubLinks |= checkExprHasSubLink(copy);
}
--- 686,697 ----
/*
* Make sure query is marked correctly if added qual has sublinks or
* aggregates (not sure it can ever have aggs, but sublinks
! * definitely). Need not search qual when query is already marked.
*/
! if (!parsetree->hasAggs)
! parsetree->hasAggs = checkExprHasAggs(copy);
! if (!parsetree->hasSubLinks)
! parsetree->hasSubLinks = checkExprHasSubLink(copy);
}
***************
*** 758,763 ****
--- 762,773 ----
* entry with matching resno from targetlist, if there is one.
* If not, we either change the unmatched Var's varno to update_varno
* (when event == CMD_UPDATE) or replace it with a constant NULL.
+ *
+ * Note: the business with inserted_sublink is needed to update hasSubLinks
+ * in subqueries when the replacement adds a subquery inside a subquery.
+ * Messy, isn't it? We do not need to do similar pushups for hasAggs,
+ * because it isn't possible for this transformation to insert a level-zero
+ * aggregate reference into a subquery --- it could only insert outer aggs.
*/

typedef struct
***************
*** 767,772 ****
--- 777,783 ----
List *targetlist;
int event;
int update_varno;
+ bool inserted_sublink;
} ResolveNew_context;

static Node *
***************
*** 814,819 ****
--- 825,833 ----
/* Adjust varlevelsup if tlist item is from higher query */
if (this_varlevelsup > 0)
IncrementVarSublevelsUp(n, this_varlevelsup, 0);
+ /* Report it if we are adding a sublink to query */
+ if (!context->inserted_sublink)
+ context->inserted_sublink = checkExprHasSubLink(n);
return n;
}
}
***************
*** 840,849 ****
--- 854,868 ----
{
Query *query = (Query *) node;
Query *newnode;
+ bool save_inserted_sublink;

FLATCOPY(newnode, query, Query);
context->sublevels_up++;
+ save_inserted_sublink = context->inserted_sublink;
+ context->inserted_sublink = false;
query_tree_mutator(newnode, ResolveNew_mutator, context, 0);
+ newnode->hasSubLinks |= context->inserted_sublink;
+ context->inserted_sublink = save_inserted_sublink;
context->sublevels_up--;
return (Node *) newnode;
}
***************
*** 862,867 ****
--- 881,887 ----
context.targetlist = targetlist;
context.event = event;
context.update_varno = update_varno;
+ context.inserted_sublink = false;

/*
* Must be prepared to start with a Query or a bare expression tree;

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #4

P: n/a

Thanks.

Yeah, there was a small change. Between the versions I tested (7.3.2 and
7.3.4), the RULE was implemented, although I had thought they were using
the same dumps. I didn't notice that when I first posted. (It was a late
night, you'll have to excuse my sleepy eyes.)

Someone posted an interesting workaround that I'll probably use for the time
being, as we have several servers set up at different client sites that I'd
rather not patch. I'll try out the patch locally, though, and I'll be
upgrading the servers after the next PostgreSQL release. (7.3.5 or 7.4, I
assume.)

Thanks for looking into this, though.

J

Tom Lane wrote:
J Smith <da********@hushmail.com> writes:
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.


I dug into this and found that the misbehavior occurs when the
sub-SELECT that is present in the INSERT:
INSERT INTO clip (program_id, clip_name) VALUES (
(SELECT program_id FROM program WHERE program_code = '9531443001'),
'Canada: A Diverse Culture');


is inserted to replace "new.program_id" in the RULE:
CREATE RULE program_clip_insert_only_1 AS ON INSERT TO clip WHERE
((SELECT count(*) AS count FROM clip WHERE clip.program_id =
new.program_id) >= 1) DO INSTEAD NOTHING;


As far as I can tell, this problem has existed for a long time; it is
certainly not new in 7.3.4. (I see the same failure in 7.2.4 as 7.3.4.)
Are you sure you weren't changing your application at the same time you
updated?

I've applied the attached patch to the 7.3 branch, if you want to use
it.

regards, tom lane


Nov 12 '05 #5

P: n/a
I am using Postgres 7.3.4 through JDBC, and turning auto-commit off. My
application needs to insert a row or, if a row with the same primary key
already exists, update the existing row. I was hoping to implement this
by just trying the insert, and doing the update only in case of a PK
violation (which results in a SQLException). I've run into two problems.

1) Detecting a PK violation cannot be done cleanly. The violation
results in a SQLException, and the only way I can see to distinguish a
PK violation from some other problem is to check the text of the error
message returned by SQLException.getMessage().
(SQLException.getErrorCode() returns 0, and getSQLState() returns null).
It would be nice if the error code clearly identified a PK violation,
(or even just a uniqueness violation).

2) The more serious problem is that the PK violation causes an abort of
the transaction, so I can't proceed to do the update in the same
transaction. Yes, there are easy ways to code around this problem, but
they are going to be slower. Duplicates are very unlikely in my
application, so if I update, and then do the insert on an update count
of zero, I will end up executing twice as many commands as I would
otherwise.

Why does PostgreSQL abort a transaction when a PK violation occurs? The
closest I was able to find was this:

http://archives.postgresql.org/pgsql...6/msg00325.php

but it doesn't really answer my question.

I can understand this behavior for pgplsql programs, where exceptions
cannot be caught, but it seems to be an unnecessary restriction for
Java, and in general, for applications written using APIs that permit
continuation following an error.

Jack Orenstein
Reference Information Systems, Inc.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #6

P: n/a
On Mon, 2003-11-10 at 13:12, Jack Orenstein wrote:
I am using Postgres 7.3.4 through JDBC, and turning auto-commit off. My
application needs to insert a row or, if a row with the same primary key
already exists, update the existing row. I was hoping to implement this
by just trying the insert, and doing the update only in case of a PK
violation (which results in a SQLException). I've run into two problems.

1) Detecting a PK violation cannot be done cleanly. The violation
results in a SQLException, and the only way I can see to distinguish a
PK violation from some other problem is to check the text of the error
message returned by SQLException.getMessage().
(SQLException.getErrorCode() returns 0, and getSQLState() returns null).
It would be nice if the error code clearly identified a PK violation,
(or even just a uniqueness violation).

2) The more serious problem is that the PK violation causes an abort of
the transaction, so I can't proceed to do the update in the same
transaction. Yes, there are easy ways to code around this problem, but
they are going to be slower. Duplicates are very unlikely in my
application, so if I update, and then do the insert on an update count
of zero, I will end up executing twice as many commands as I would
otherwise.

Why does PostgreSQL abort a transaction when a PK violation occurs? The
closest I was able to find was this:

http://archives.postgresql.org/pgsql...6/msg00325.php

but it doesn't really answer my question.

I can understand this behavior for pgplsql programs, where exceptions
cannot be caught, but it seems to be an unnecessary restriction for
Java, and in general, for applications written using APIs that permit
continuation following an error.


Because that's how the designers wanted it.
http://archives.postgresql.org/pgsql...1/msg00172.php

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"As I like to joke, I may have invented it, but Microsoft made it
popular"
David Bradley, regarding Ctrl-Alt-Del
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.