Re: [HACKERS] explain and PARAM_EXEC

[Yeb Havinga]

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