Thread: Using subselects in INSERTs?
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
J Smith <dark_panda@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
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 >
Attachment
J Smith <dark_panda@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;
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 <dark_panda@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 > >
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-hackers/2002-06/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.
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-hackers/2002-06/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-sql/2001-11/msg00172.php -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@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