Обсуждение: unplanned sub-select error?
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
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
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
-- 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));
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
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
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'twant 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'twant 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; }