Re: explain and PARAM_EXEC - Mailing list pgsql-hackers

Tom Lane wrote: <br /><blockquote cite="mid:4BA361F2.5010509@gmail.com" type="cite"><blockquote type="cite">In
principleit could look something like <br /><br />     (SubPlan N ($0 := b.oid)) <br /><br /> but with a few parameters
anda bunch of other stuff on the same line <br /> that would get out of hand.  </blockquote></blockquote> The patch I
submittedto implement this, hits bogus varno:65001 in <a
href="http://doxygen.postgresql.org/ruleutils_8c-source.html#l03547">get_variable</a>when I explain the following query
fromthe aggregates.sql regression test.<br /><br /><tt>select ten, sum(distinct four) from onek a<br /> group by ten<br
/>having exists (select 1 from onek b where sum(distinct a.four) = b.four);</tt><br /><br /> The explain without the
subplanargument-patch  shows<br /><tt>regression=# explain verbose select ten, sum(distinct four) from onek a<br />
groupby ten<br /> having exists (select 1 from onek b where sum(distinct a.four) = b.four);<br />
                                 QUERY PLAN                                  <br />
------------------------------------------------------------------------------<br/>  GroupAggregate 
(cost=197.83..216.52rows=10 width=8)<br />    Output: a.ten, sum(DISTINCT a.four)<br />    Filter: (alternatives:
SubPlan1 or hashed SubPlan 2)<br />    ->  Sort  (cost=197.83..200.33 rows=1000 width=8)<br />          Output:
a.ten,a.four<br />          Sort Key: a.ten<br />          ->  Seq Scan on public.onek a  (cost=0.00..148.00
rows=1000width=8)<br />                Output: a.ten, a.four<br />    SubPlan 1<br />      ->  Seq Scan on
public.onekb  (cost=0.00..150.50 rows=250 width=0)<br />            Filter: ($0 = b.four)<br />    SubPlan 2<br />     
-> Seq Scan on public.onek b  (cost=0.00..148.00 rows=1000 width=4)<br />            Output: b.four<br /> (14
rows)<br/></tt><br /> The subplan argument list contains a aggref node, with a var argument that has the 65001 varno.
Apparentlythe OUTER varno is set by search_indexed_tlist_for_var and it seems like correct behaviour, see stack trace
below.<br/><br /> I'm unsure what is a good approach to solve this problem: let get_variable not give an error in this
case?<br/><br /> regards,<br /> Yeb Havinga<br /><br /><br /><br /><br /> #0  search_indexed_tlist_for_var
(var=0xe32dc0,itlist=0xe67290, newvarno=65001, rtoffset=0)<br />     at setrefs.c:1568<br /> #1  0x0000000000697581 in
fix_upper_expr_mutator(node=0xe32dc0, context=0x7fffabcde850)<br />     at setrefs.c:1853<br /> #2  0x0000000000697529
infix_upper_expr (glob=0xe77178, node=0xe32dc0, subplan_itlist=0xe67290, <br />     rtoffset=0) at setrefs.c:1839<br />
#3 0x0000000000696b6e in set_upper_references (glob=0xe77178, plan=0xe66f80, rtoffset=0)<br /><br /><br /><blockquote
cite="mid:4BA361F2.5010509@gmail.com"type="cite">postgres=# explain select oid::int + 1,(select oid from pg_class a
wherea.oid = b.relfilenode and a.relnamespace = b.relnamespace) from pg_class b; <br />
                                       QUERY PLAN                                        
---------------------------------------------------------------------------------------------<br /> Seq Scan on
pg_classb  (cost=0.00..2459.64 rows=296 width=12) <br />   SubPlan 1 ($0 := b.relfilenode, $1 := b.relnamespace) <br />
   ->  Index Scan using pg_class_oid_index on pg_class a  (cost=0.00..8.27 rows=1 width=4) <br />           Index
Cond:(oid = $0) <br />           Filter: (relnamespace = $1) <br /> (5 rows) <br /><br /> index 56d9c5b..454d59b 100644
<br/> --- a/src/backend/commands/explain.c <br /> +++ b/src/backend/commands/explain.c <br /> @@ -1686,20 +1686,47 @@
staticvoid <br /> ExplainSubPlans(List *plans, const char *relationship, ExplainState *es) <br /> { <br />       
ListCell  *lst; <br /><br />        foreach(lst, plans) <br />        { <br />                SubPlanState *sps =
(SubPlanState*) lfirst(lst); <br /> -               SubPlan    *sp = (SubPlan *) sps->xprstate.expr; <br />
+              SubPlan      *sp = (SubPlan *) sps->xprstate.expr; <br /> +               StringInfo    signature =
makeStringInfo();<br /> +               int           i = 0; <br /> +               List         *context; <br />
+              bool          useprefix; <br /> +               ListCell     *c; <br /> + <br /> +               context
=deparse_context_for_plan((Node *)exec_subplan_get_plan(es->pstmt, sp), <br />
+                                                                                 NULL, <br />
+                                                                                 es->rtable, <br />
+                                                                                 es->pstmt->subplans); <br />
+              useprefix = list_length(es->rtable) > 1; <br /> + <br /> +              
appendStringInfoString(signature,sp->plan_name); <br /> + <br /> +               foreach(c, sp->args) <br />
+              { <br /> +                       Node *n = lfirst(c); <br /> +                      
appendStringInfo(signature,"%s$%d := %s", <br /> +                                                        (i == 0) ? "
(": ", ", <br /> +                                                        i, <br />
+                                                       deparse_expression(n, context, useprefix, true)); <br />
+                      i++; <br /> +               } <br /> + <br /> +               if (i > 0) <br />
+                      appendStringInfoString(signature, ")"); <br /><br />               
ExplainNode(exec_subplan_get_plan(es->pstmt,sp), <br />                                        sps->planstate,
<br/>                                        NULL, <br /> -                                       relationship,
sp->plan_name,<br /> +                                       relationship, <br />
+                                      signature->data, <br />                                        es); <br />
      } <br /> } <br /><br /> /* <br />  * Explain a property, such as sort keys or targets, that takes the form of <br
/> * a list of unlabeled items.  "data" is a list of C strings. <br /><br /></blockquote><br />