Lists: | pgsql-sql |
---|
From: | Kyle Bateman <kyle(at)actarg(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | unplanned sub-select error? |
Date: | 2005-11-22 00:37:36 |
Message-ID: | 43826850.1020302@actarg.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Kyle Bateman <kyle(at)actarg(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: unplanned sub-select error? |
Date: | 2005-11-22 01:10:54 |
Message-ID: | 6603.1132621854@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
Kyle Bateman <kyle(at)actarg(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Kyle Bateman <kyle(at)actarg(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: unplanned sub-select error? |
Date: | 2005-11-22 19:47:22 |
Message-ID: | 13492.1132688842@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
Kyle Bateman <kyle(at)actarg(dot)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
From: | Kyle Bateman <kyle(at)actarg(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: unplanned sub-select error? |
Date: | 2005-11-22 20:24:59 |
Message-ID: | 43837E9B.20306@actarg.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
Tom Lane wrote:
>Kyle Bateman <kyle(at)actarg(dot)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
Attachment | Content-Type | Size |
---|---|---|
testmtr.sql | text/x-sql | 745 bytes |
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Kyle Bateman <kyle(at)actarg(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: unplanned sub-select error? |
Date: | 2005-11-23 17:23:00 |
Message-ID: | 22945.1132766580@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
Kyle Bateman <kyle(at)actarg(dot)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'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;
}