<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane wrote:
<br>
<blockquote cite="mid:4BA361F2(dot)5010509(at)gmail(dot)com" type="cite">
<blockquote type="cite">In principle it could look something like
<br>
<br>
(SubPlan N ($0 := b.oid))
<br>
<br>
but with a few parameters and a bunch of other stuff on the same line
<br>
that would get out of hand.
</blockquote>
</blockquote>
The patch I submitted to 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 from the 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 subplan argument-patch shows<br>
<tt>regression=# explain verbose 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);<br>
QUERY
PLAN <br>
------------------------------------------------------------------------------<br>
GroupAggregate (cost=197.83..216.52 rows=10 width=8)<br>
Output: a.ten, sum(DISTINCT a.four)<br>
Filter: (alternatives: SubPlan 1 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=1000
width=8)<br>
Output: a.ten, a.four<br>
SubPlan 1<br>
-> Seq Scan on public.onek b (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. Apparently the 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 in fix_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(dot)5010509(at)gmail(dot)com" type="cite">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;
<br>
QUERY
PLAN
---------------------------------------------------------------------------------------------
<br>
Seq Scan on pg_class b (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 @@ static void
<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>
</body>
</html>