pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedunplanned sub-select error?
5+ messages / 2 participants
[nested] [flat]
* unplanned sub-select error?
@ 2005-11-22 00:37 Kyle Bateman <kyle@actarg.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kyle Bateman @ 2005-11-22 00:37 UTC (permalink / raw)
To: pgsql-sql
I have a query:
insert into mtr_reg_v_wt (ropnum, inum, pnum, rquant, value, status,
ddate, fr_proj, to_proj) values (28985,1,1014,1,(select cost from
prd_part where pnum = 1014),'work','2005-Nov-15',50,75);
That used to work fine under 7.1.3 but now gives the error:
ERROR: cannot handle unplanned sub-select
Anyone know what this means? Is there a good reason why this update
should no longer work? Or is this a bug?
Kyle
wyatterp.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: unplanned sub-select error?
@ 2005-11-22 01:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kyle Bateman <kyle@actarg.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2005-11-22 01:10 UTC (permalink / raw)
To: Kyle Bateman <kyle@actarg.com>; +Cc: pgsql-sql
Kyle Bateman <kyle@actarg.com> writes:
> I have a query:
> insert into mtr_reg_v_wt (ropnum, inum, pnum, rquant, value, status,
> ddate, fr_proj, to_proj) values (28985,1,1014,1,(select cost from
> prd_part where pnum = 1014),'work','2005-Nov-15',50,75);
> That used to work fine under 7.1.3 but now gives the error:
> ERROR: cannot handle unplanned sub-select
You need to offer a little more context, like what PG version you are
using now and what is the underlying DDL --- I suspect some rules or
views are involved here, but you didn't show them to us.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: unplanned sub-select error?
@ 2005-11-22 19:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kyle Bateman <kyle@actarg.com>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-11-22 19:47 UTC (permalink / raw)
To: Kyle Bateman <kyle@actarg.com>; +Cc: pgsql-sql
Kyle Bateman <kyle@actarg.com> writes:
> Sorry, you're right. I have now confirmed that this only happens when
> updating via a view/rule (as you suspected). Attached is a minimalist
> sql file that demonstrates the same error message from a blank
> database. I'm using 8.1.0. I'm pretty sure this problem did not exist
> on 8.0.3.
Thanks for the test case. I've confirmed it fails here in CVS tip but
not in 8.0 branch, so indeed it must be a new bug. Will look into it.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: unplanned sub-select error?
@ 2005-11-22 20:24 Kyle Bateman <kyle@actarg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Kyle Bateman @ 2005-11-22 20:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane wrote:
>Kyle Bateman <kyle@actarg.com> writes:
>
>
>>I have a query:
>>insert into mtr_reg_v_wt (ropnum, inum, pnum, rquant, value, status,
>>ddate, fr_proj, to_proj) values (28985,1,1014,1,(select cost from
>>prd_part where pnum = 1014),'work','2005-Nov-15',50,75);
>>
>>
>
>
>
>>That used to work fine under 7.1.3 but now gives the error:
>>
>>
>
>
>
>>ERROR: cannot handle unplanned sub-select
>>
>>
>
>You need to offer a little more context, like what PG version you are
>using now and what is the underlying DDL --- I suspect some rules or
>views are involved here, but you didn't show them to us.
>
>
>
Sorry, you're right. I have now confirmed that this only happens when
updating via a view/rule (as you suspected). Attached is a minimalist
sql file that demonstrates the same error message from a blank
database. I'm using 8.1.0. I'm pretty sure this problem did not exist
on 8.0.3.
Kyle
Attachments:
testmtr.sql (text/x-sql, 744B) download
-- Expose the "unplanned sub-select" error message
create table parts (
partnum varchar(18) primary key,
cost float8
);
create table shipped (
ttype char(2),
ordnum int4,
partnum varchar(18) references parts,
value float8,
primary key (ttype, ordnum)
);
create view shipped_view as
select * from shipped where ttype = 'wt';
create rule shipped_view_insert as on insert to shipped_view
do instead insert into shipped
(ttype, ordnum, partnum, value)
values
('wt', new.ordnum, new.partnum, new.value);
insert into parts (partnum, cost) values (1, 1234.56);
insert into shipped_view
(ordnum, partnum, value)
values
(100,1,(select cost from parts where partnum = 1));
* Re: unplanned sub-select error?
@ 2005-11-23 17:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kyle Bateman <kyle@actarg.com>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-11-23 17:23 UTC (permalink / raw)
To: Kyle Bateman <kyle@actarg.com>; +Cc: pgsql-sql
Kyle Bateman <kyle@actarg.com> writes:
> Sorry, you're right. I have now confirmed that this only happens when
> updating via a view/rule (as you suspected). Attached is a minimalist
> sql file that demonstrates the same error message from a blank
> database. I'm using 8.1.0. I'm pretty sure this problem did not exist
> on 8.0.3.
Fixed --- attached is the patch if you need it right away. Thanks for
the report!
regards, tom lane
inline patch 1: src/backend/rewrite/rewriteHandler.c, src/backend/rewrite/rewriteManip.c (+26/-4 lines) download inline-1.patch
Index: src/backend/rewrite/rewriteHandler.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/rewrite/rewriteHandler.c,v
retrieving revision 1.159
diff -c -r1.159 rewriteHandler.c
*** src/backend/rewrite/rewriteHandler.c 22 Nov 2005 18:17:19 -0000 1.159
--- src/backend/rewrite/rewriteHandler.c 23 Nov 2005 17:10:01 -0000
***************
*** 374,379 ****
--- 374,387 ----
sub_action->jointree->fromlist =
list_concat(newjointree, sub_action->jointree->fromlist);
+
+ /*
+ * There could have been some SubLinks in newjointree, in which
+ * case we'd better mark the sub_action correctly.
+ */
+ if (parsetree->hasSubLinks && !sub_action->hasSubLinks)
+ sub_action->hasSubLinks =
+ checkExprHasSubLink((Node *) newjointree);
}
}
Index: src/backend/rewrite/rewriteManip.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/rewrite/rewriteManip.c,v
retrieving revision 1.93
diff -c -r1.93 rewriteManip.c
*** src/backend/rewrite/rewriteManip.c 22 Nov 2005 18:17:19 -0000 1.93
--- src/backend/rewrite/rewriteManip.c 23 Nov 2005 17:10:01 -0000
***************
*** 930,935 ****
--- 930,936 ----
RangeTblEntry *target_rte,
List *targetlist, int event, int update_varno)
{
+ Node *result;
ResolveNew_context context;
context.target_varno = target_varno;
***************
*** 944,951 ****
* Must be prepared to start with a Query or a bare expression tree; if
* it's a Query, we don't want to increment sublevels_up.
*/
! return query_or_expression_tree_mutator(node,
! ResolveNew_mutator,
! (void *) &context,
! 0);
}
--- 945,965 ----
* Must be prepared to start with a Query or a bare expression tree; if
* it's a Query, we don't want to increment sublevels_up.
*/
! result = query_or_expression_tree_mutator(node,
! ResolveNew_mutator,
! (void *) &context,
! 0);
!
! if (context.inserted_sublink)
! {
! if (IsA(result, Query))
! ((Query *) result)->hasSubLinks = true;
! /*
! * Note: if we're called on a non-Query node then it's the caller's
! * responsibility to update hasSubLinks in the ancestor Query.
! * This is pretty fragile and perhaps should be rethought ...
! */
! }
!
! return result;
}
end of thread, other threads:[~2005-11-23 17:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-22 00:37 unplanned sub-select error? Kyle Bateman <kyle@actarg.com>
2005-11-22 01:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-22 20:24 ` Kyle Bateman <kyle@actarg.com>
2005-11-22 19:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-23 17:23 ` 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