[gpoo@ubiobio.cl: Re: [HACKERS] EXPLAIN omits schema?]

German sent this some time ago and it never reached the list. He sent
it again from Gmail but again it was lost in the void.

I am forwarding it to improve the chances of it being delivered ... The
patch in the fwd is not a nice MIME part but it should work without
problem anyway.

----- Forwarded message from Germán Poó Caamaño <gpoo(at)ubiobio(dot)cl> -----

From: Germán Poó Caamaño <gpoo(at)ubiobio(dot)cl>
To: Magnus Hagander <magnus(at)hagander(dot)net>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>,
Dave Page <dpage(at)postgresql(dot)org>,
PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Date: Thu, 14 Jun 2007 17:53:37 -0400
Subject: Re: [HACKERS] EXPLAIN omits schema?
X-Bogosity: Ham, tests=bogofilter, spamicity=0.000000, version=1.1.5

On Wed, 2007-06-13 at 14:49 +0200, Magnus Hagander wrote:
> On Wed, Jun 13, 2007 at 08:47:30AM -0400, Alvaro Herrera wrote:
> > Magnus Hagander wrote:
> >
> > > Just to open a whole new can of worms ;-)
> > >
> > > I read an article a couple of days ago about the "machine readable showplan
> > > output" in SQL Server 2005 (basically, it's EXPLAIN output but in XML
> > > format). It does make a lot of sense if yourp rimary interface is !=
> > > commandline (psql), such as pgadmin or phppgadmin. The idea being that you
> > > can stick in *all* the details you want, since you can't possibly clutter
> > > up the display. And you stick them in a well-defined XML format (or another
> > > format if you happen to hate XML) where the client-side program can easily
> > > parse out whatever it needs. It's also future-proof - if you add a new
> > > field somewhere, the client program parser won't break.
> > >
> > > Something worth doing? Not to replace the current explain output, but as a
> > > second option (EXPLAIN XML whatever)?
> >
> > FYI a patch was posted for this some time ago, because a friend of mine
> > wanted to help a student to write an EXPLAIN parsing tool.
>
> Didn't see that one. Explain in XML format? Got an URL for it, I can't seem
> to find it on -patches.

I never send it, sorry. It was made against 8.0 beta. By the time of
the message, I was told that pgAdmin folks were working on parser the
text output. Hence, I thought it was useless after all.

Anyway, I made a break and I have the patch working against CVS HEAD.

Please note this is a 3 years old patch. Some stuff are missing, such
as show_sort_info, but it should be easy to add it.

By the time this code was written, the 'XML' token didn't exists.
Today, when I made the merge, I noted there is a XML_P. So, I
touched keywords "xml". I hope I'm not break anything.

Any comments are welcomed. Even if this patch is a total crap.

PS: I owe you the DTD.

--
Germán Poó Caamaño
Concepción - Chile

*** /dev/fd/63 2007-06-14 17:40:26.478023384 -0400
--- src/backend/commands/explain.c 2007-06-14 17:23:29.280056575 -0400
*************** typedef struct ExplainState
*** 49,54 ****
--- 49,61 ----
List *rtable; /* range table */
} ExplainState;

+ typedef struct ExplainXML
+ {
+ /* options */
+ StringInfo str;
+ int level; /* level of childs */
+ } ExplainXML;
+
static void ExplainOneQuery(Query *query, ExplainStmt *stmt,
const char *queryString,
ParamListInfo params, TupOutputState *tstate);
*************** static double elapsed_time(instr_time *s
*** 56,72 ****
static void explain_outNode(StringInfo str,
Plan *plan, PlanState *planstate,
Plan *outer_plan,
! int indent, ExplainState *es);
static void show_scan_qual(List *qual, const char *qlabel,
int scanrelid, Plan *outer_plan, Plan *inner_plan,
! StringInfo str, int indent, ExplainState *es);
static void show_upper_qual(List *qual, const char *qlabel, Plan *plan,
! StringInfo str, int indent, ExplainState *es);
static void show_sort_keys(Plan *sortplan, int nkeys, AttrNumber *keycols,
const char *qlabel,
! StringInfo str, int indent, ExplainState *es);
static void show_sort_info(SortState *sortstate,
! StringInfo str, int indent, ExplainState *es);
static const char *explain_get_index_name(Oid indexId);


--- 63,79 ----
static void explain_outNode(StringInfo str,
Plan *plan, PlanState *planstate,
Plan *outer_plan,
! int indent, ExplainState *es, ExplainXML *exml);
static void show_scan_qual(List *qual, const char *qlabel,
int scanrelid, Plan *outer_plan, Plan *inner_plan,
! StringInfo str, int indent, ExplainState *es, ExplainXML *exml);
static void show_upper_qual(List *qual, const char *qlabel, Plan *plan,
! StringInfo str, int indent, ExplainState *es, ExplainXML *exml);
static void show_sort_keys(Plan *sortplan, int nkeys, AttrNumber *keycols,
const char *qlabel,
! StringInfo str, int indent, ExplainState *es, ExplainXML *exml);
static void show_sort_info(SortState *sortstate,
! StringInfo str, int indent, ExplainState *es, ExplainXML *exml);
static const char *explain_get_index_name(Oid indexId);


*************** ExplainOnePlan(PlannedStmt *plannedstmt,
*** 222,227 ****
--- 229,235 ----
ExplainState *es;
StringInfoData buf;
int eflags;
+ ExplainXML *exml;

/*
* Update snapshot command ID to ensure this query sees results of any
*************** ExplainOnePlan(PlannedStmt *plannedstmt,
*** 263,268 ****
--- 271,282 ----
totaltime += elapsed_time(&starttime);
}

+ exml = (ExplainXML *) palloc0(sizeof(ExplainXML));
+
+ exml->str = makeStringInfo();
+ appendStringInfo (exml->str, "<?xml version=\"1.0\"?>\n");
+ appendStringInfo (exml->str, "<explain>\n");
+
es = (ExplainState *) palloc0(sizeof(ExplainState));

es->printNodes = stmt->verbose;
*************** ExplainOnePlan(PlannedStmt *plannedstmt,
*** 292,298 ****
initStringInfo(&buf);
explain_outNode(&buf,
queryDesc->plannedstmt->planTree, queryDesc->planstate,
! NULL, 0, es);

/*
* If we ran the command, run any AFTER triggers it queued. (Note this
--- 306,312 ----
initStringInfo(&buf);
explain_outNode(&buf,
queryDesc->plannedstmt->planTree, queryDesc->planstate,
! NULL, 0, es, exml);

/*
* If we ran the command, run any AFTER triggers it queued. (Note this
*************** ExplainOnePlan(PlannedStmt *plannedstmt,
*** 325,330 ****
--- 339,347 ----
Trigger *trig = rInfo->ri_TrigDesc->triggers + nt;
Instrumentation *instr = rInfo->ri_TrigInstrument + nt;
char *conname;
+ StringInfo triggerStr;
+
+ triggerStr = makeStringInfo();

/* Must clean up instrumentation state */
InstrEndLoop(instr);
*************** ExplainOnePlan(PlannedStmt *plannedstmt,
*** 341,358 ****
--- 358,393 ----
{
appendStringInfo(&buf, "Trigger for constraint %s",
conname);
+ appendStringInfo(triggerStr, "constraint=\"%s\"",
+ conname);
pfree(conname);
}
else
+ {
appendStringInfo(&buf, "Trigger %s", trig->tgname);
+ appendStringInfo(triggerStr, "name=\"%s\"", trig->tgname);
+ }

if (numrels > 1)
+ {
appendStringInfo(&buf, " on %s",
RelationGetRelationName(rInfo->ri_RelationDesc));
+ appendStringInfo(triggerStr, " on=\"%s\"",
+ RelationGetRelationName(rInfo->ri_RelationDesc));
+ }

appendStringInfo(&buf, ": time=%.3f calls=%.0f\n",
1000.0 * instr->total,
instr->ntuples);
+ appendStringInfo(triggerStr, " <trigger %s "
+ "time=%.3f calls=%.0f />\n",
+ triggerStr->data,
+ 1000.0 * instr->total,
+ instr->ntuples);
+ appendStringInfo(exml->str, triggerStr->data);
+
+ pfree(triggerStr->data);
+ pfree(triggerStr);
}
}
}
*************** ExplainOnePlan(PlannedStmt *plannedstmt,
*** 374,383 ****
totaltime += elapsed_time(&starttime);

if (stmt->analyze)
appendStringInfo(&buf, "Total runtime: %.3f ms\n",
1000.0 * totaltime);
! do_text_output_multiline(tstate, buf.data);

pfree(buf.data);
pfree(es);
}
--- 409,434 ----
totaltime += elapsed_time(&starttime);

if (stmt->analyze)
+ {
appendStringInfo(&buf, "Total runtime: %.3f ms\n",
1000.0 * totaltime);
! appendStringInfo(exml->str, "<runtime>%.3f ms</runtime>\n",
! 1000.0 * totaltime);
! }
! if (stmt->xml)
! {
! appendStringInfo(exml->str, "</explain>\n");
! do_text_output_multiline(tstate, exml->str->data);
! }
! else
! {
! do_text_output_multiline(tstate, buf.data);
! }

+ pfree(exml->str->data);
+ pfree(exml->str);
+ pfree(exml);
+
pfree(buf.data);
pfree(es);
}
*************** static void
*** 421,427 ****
explain_outNode(StringInfo str,
Plan *plan, PlanState *planstate,
Plan *outer_plan,
! int indent, ExplainState *es)
{
char *pname;
int i;
--- 472,479 ----
explain_outNode(StringInfo str,
Plan *plan, PlanState *planstate,
Plan *outer_plan,
! int indent, ExplainState *es,
! ExplainXML *exml)
{
char *pname;
int i;
*************** explain_outNode(StringInfo str,
*** 429,434 ****
--- 481,487 ----
if (plan == NULL)
{
appendStringInfoChar(str, '\n');
+ appendStringInfo(exml->str, "<plan />");
return;
}

*************** explain_outNode(StringInfo str,
*** 600,612 ****
}

appendStringInfoString(str, pname);
switch (nodeTag(plan))
{
case T_IndexScan:
! if (ScanDirectionIsBackward(((IndexScan *) plan)->indexorderdir))
! appendStringInfoString(str, " Backward");
! appendStringInfo(str, " using %s",
! explain_get_index_name(((IndexScan *) plan)->indexid));
/* FALL THRU */
case T_SeqScan:
case T_BitmapHeapScan:
--- 653,683 ----
}

appendStringInfoString(str, pname);
+ appendStringInfo(exml->str , "<plan name=\"%s\" level=\"%d\">\n",
+ pname, exml->level);
+
switch (nodeTag(plan))
{
case T_IndexScan:
! {
! StringInfo index;
!
! index = makeStringInfo();
! appendStringInfo(index, "name=\"%s\"",
! explain_get_index_name(((IndexScan *) plan)->indexid));
!
! if (ScanDirectionIsBackward(((IndexScan *) plan)->indexorderdir))
! {
! appendStringInfoString(str, " Backward");
! appendStringInfoString(index, " backward");
! }
! appendStringInfo(str, " using %s",
! explain_get_index_name(((IndexScan *) plan)->indexid));
! appendStringInfo(exml->str, " <index %s />\n",
! index->data);
! pfree(index->data);
! pfree(index);
! }
/* FALL THRU */
case T_SeqScan:
case T_BitmapHeapScan:
*************** explain_outNode(StringInfo str,
*** 616,621 ****
--- 687,695 ----
RangeTblEntry *rte = rt_fetch(((Scan *) plan)->scanrelid,
es->rtable);
char *relname;
+ StringInfo resname;
+
+ resname = makeStringInfo();

/* Assume it's on a real relation */
Assert(rte->rtekind == RTE_RELATION);
*************** explain_outNode(StringInfo str,
*** 625,638 ****
--- 699,727 ----

appendStringInfo(str, " on %s",
quote_identifier(relname));
+ appendStringInfo(resname, "name=\"%s\"",
+ quote_identifier(relname));
+
if (strcmp(rte->eref->aliasname, relname) != 0)
+ {
appendStringInfo(str, " %s",
quote_identifier(rte->eref->aliasname));
+
+ appendStringInfo(resname, " alias=\"%s\"",
+ quote_identifier(rte->eref->aliasname));
+ }
+
+ appendStringInfo(exml->str, " <table %s/>\n",
+ resname->data);
+ pfree(resname->data);
+ pfree(resname);
}
break;
case T_BitmapIndexScan:
appendStringInfo(str, " on %s",
explain_get_index_name(((BitmapIndexScan *) plan)->indexid));
+ appendStringInfo(exml->str, " <index name=\"%s\" />\n",
+ explain_get_index_name(((BitmapIndexScan *) plan)->indexid));
break;
case T_SubqueryScan:
if (((Scan *) plan)->scanrelid > 0)
*************** explain_outNode(StringInfo str,
*** 642,647 ****
--- 731,739 ----

appendStringInfo(str, " %s",
quote_identifier(rte->eref->aliasname));
+ appendStringInfo(exml->str, " <table alias=\"%s\" />\n",
+ quote_identifier(rte->eref->aliasname));
+
}
break;
case T_FunctionScan:
*************** explain_outNode(StringInfo str,
*** 652,657 ****
--- 744,753 ----
Node *funcexpr;
char *proname;

+ StringInfo resname;
+
+ resname = makeStringInfo();
+
/* Assert it's on a RangeFunction */
Assert(rte->rtekind == RTE_FUNCTION);

*************** explain_outNode(StringInfo str,
*** 674,682 ****
--- 770,791 ----

appendStringInfo(str, " on %s",
quote_identifier(proname));
+
+ appendStringInfo(resname, "name=\"%s\"",
+ quote_identifier(proname));
+
if (strcmp(rte->eref->aliasname, proname) != 0)
+ {
appendStringInfo(str, " %s",
quote_identifier(rte->eref->aliasname));
+ appendStringInfo(resname, " alias=\"%s\"",
+ quote_identifier(rte->eref->aliasname));
+ }
+ appendStringInfo(exml->str, " <function %s />\n",
+ resname->data);
+ pfree(resname->data);
+ pfree(resname);
+
}
break;
case T_ValuesScan:
*************** explain_outNode(StringInfo str,
*** 693,698 ****
--- 802,809 ----

appendStringInfo(str, " on %s",
quote_identifier(valsname));
+ appendStringInfo(exml->str, "name=\"%s\"",
+ quote_identifier(valsname));
}
break;
default:
*************** explain_outNode(StringInfo str,
*** 703,708 ****
--- 814,824 ----
plan->startup_cost, plan->total_cost,
plan->plan_rows, plan->plan_width);

+ appendStringInfo(exml->str, " <cost startup=\"%.2f\" total=\"%.2f\" "
+ "rows=\"%.0f\" width=\"%d\" />\n",
+ plan->startup_cost, plan->total_cost,
+ plan->plan_rows, plan->plan_width);
+
/*
* We have to forcibly clean up the instrumentation state because we
* haven't done ExecutorEnd yet. This is pretty grotty ...
*************** explain_outNode(StringInfo str,
*** 719,727 ****
--- 835,853 ----
1000.0 * planstate->instrument->total / nloops,
planstate->instrument->ntuples / nloops,
planstate->instrument->nloops);
+ appendStringInfo(exml->str,
+ " <analyze time_start=\"%.3f\" time_end=\"%.3f\" "
+ "rows=\"%.0f\" loops=\"%.0f\" />\n",
+ 1000.0 * planstate->instrument->startup / nloops,
+ 1000.0 * planstate->instrument->total / nloops,
+ planstate->instrument->ntuples / nloops,
+ planstate->instrument->nloops);
}
else if (es->printAnalyze)
+ {
appendStringInfo(str, " (never executed)");
+ appendStringInfo(exml->str, " <analyze never />");
+ }
appendStringInfoChar(str, '\n');

/* quals, sort keys, etc */
*************** explain_outNode(StringInfo str,
*** 732,750 ****
"Index Cond",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es);
show_scan_qual(plan->qual,
"Filter",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es);
break;
case T_BitmapIndexScan:
show_scan_qual(((BitmapIndexScan *) plan)->indexqualorig,
"Index Cond",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es);
break;
case T_BitmapHeapScan:
/* XXX do we want to show this in production? */
--- 858,876 ----
"Index Cond",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es, exml);
show_scan_qual(plan->qual,
"Filter",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es, exml);
break;
case T_BitmapIndexScan:
show_scan_qual(((BitmapIndexScan *) plan)->indexqualorig,
"Index Cond",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es, exml);
break;
case T_BitmapHeapScan:
/* XXX do we want to show this in production? */
*************** explain_outNode(StringInfo str,
*** 752,758 ****
"Recheck Cond",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es);
/* FALL THRU */
case T_SeqScan:
case T_FunctionScan:
--- 878,884 ----
"Recheck Cond",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es, exml);
/* FALL THRU */
case T_SeqScan:
case T_FunctionScan:
*************** explain_outNode(StringInfo str,
*** 761,767 ****
"Filter",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es);
break;
case T_SubqueryScan:
show_scan_qual(plan->qual,
--- 887,893 ----
"Filter",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es, exml);
break;
case T_SubqueryScan:
show_scan_qual(plan->qual,
*************** explain_outNode(StringInfo str,
*** 769,775 ****
((Scan *) plan)->scanrelid,
outer_plan,
((SubqueryScan *) plan)->subplan,
! str, indent, es);
break;
case T_TidScan:
{
--- 895,901 ----
((Scan *) plan)->scanrelid,
outer_plan,
((SubqueryScan *) plan)->subplan,
! str, indent, es, exml);
break;
case T_TidScan:
{
*************** explain_outNode(StringInfo str,
*** 785,855 ****
"TID Cond",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es);
show_scan_qual(plan->qual,
"Filter",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es);
}
break;
case T_NestLoop:
show_upper_qual(((NestLoop *) plan)->join.joinqual,
"Join Filter", plan,
! str, indent, es);
show_upper_qual(plan->qual,
"Filter", plan,
! str, indent, es);
break;
case T_MergeJoin:
show_upper_qual(((MergeJoin *) plan)->mergeclauses,
"Merge Cond", plan,
! str, indent, es);
show_upper_qual(((MergeJoin *) plan)->join.joinqual,
"Join Filter", plan,
! str, indent, es);
show_upper_qual(plan->qual,
"Filter", plan,
! str, indent, es);
break;
case T_HashJoin:
show_upper_qual(((HashJoin *) plan)->hashclauses,
"Hash Cond", plan,
! str, indent, es);
show_upper_qual(((HashJoin *) plan)->join.joinqual,
"Join Filter", plan,
! str, indent, es);
show_upper_qual(plan->qual,
"Filter", plan,
! str, indent, es);
break;
case T_Agg:
case T_Group:
show_upper_qual(plan->qual,
"Filter", plan,
! str, indent, es);
break;
case T_Sort:
show_sort_keys(plan,
((Sort *) plan)->numCols,
((Sort *) plan)->sortColIdx,
"Sort Key",
! str, indent, es);
show_sort_info((SortState *) planstate,
! str, indent, es);
break;
case T_Result:
show_upper_qual((List *) ((Result *) plan)->resconstantqual,
"One-Time Filter", plan,
! str, indent, es);
show_upper_qual(plan->qual,
"Filter", plan,
! str, indent, es);
break;
default:
break;
}

/* initPlan-s */
if (plan->initPlan)
{
--- 911,983 ----
"TID Cond",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es, exml);
show_scan_qual(plan->qual,
"Filter",
((Scan *) plan)->scanrelid,
outer_plan, NULL,
! str, indent, es, exml);
}
break;
case T_NestLoop:
show_upper_qual(((NestLoop *) plan)->join.joinqual,
"Join Filter", plan,
! str, indent, es, exml);
show_upper_qual(plan->qual,
"Filter", plan,
! str, indent, es, exml);
break;
case T_MergeJoin:
show_upper_qual(((MergeJoin *) plan)->mergeclauses,
"Merge Cond", plan,
! str, indent, es, exml);
show_upper_qual(((MergeJoin *) plan)->join.joinqual,
"Join Filter", plan,
! str, indent, es, exml);
show_upper_qual(plan->qual,
"Filter", plan,
! str, indent, es, exml);
break;
case T_HashJoin:
show_upper_qual(((HashJoin *) plan)->hashclauses,
"Hash Cond", plan,
! str, indent, es,exml);
show_upper_qual(((HashJoin *) plan)->join.joinqual,
"Join Filter", plan,
! str, indent, es, exml);
show_upper_qual(plan->qual,
"Filter", plan,
! str, indent, es, exml);
break;
case T_Agg:
case T_Group:
show_upper_qual(plan->qual,
"Filter", plan,
! str, indent, es, exml);
break;
case T_Sort:
show_sort_keys(plan,
((Sort *) plan)->numCols,
((Sort *) plan)->sortColIdx,
"Sort Key",
! str, indent, es, exml);
show_sort_info((SortState *) planstate,
! str, indent, es, exml);
break;
case T_Result:
show_upper_qual((List *) ((Result *) plan)->resconstantqual,
"One-Time Filter", plan,
! str, indent, es, exml);
show_upper_qual(plan->qual,
"Filter", plan,
! str, indent, es, exml);
break;
default:
break;
}

+ appendStringInfo(exml->str, "</plan>\n");
+
/* initPlan-s */
if (plan->initPlan)
{
*************** explain_outNode(StringInfo str,
*** 857,862 ****
--- 985,993 ----

for (i = 0; i < indent; i++)
appendStringInfo(str, " ");
+
+ exml->level = indent;
+
appendStringInfo(str, " InitPlan\n");
foreach(lst, planstate->initPlan)
{
*************** explain_outNode(StringInfo str,
*** 870,876 ****
exec_subplan_get_plan(es->pstmt, sp),
sps->planstate,
NULL,
! indent + 4, es);
}
}

--- 1001,1007 ----
exec_subplan_get_plan(es->pstmt, sp),
sps->planstate,
NULL,
! indent + 4, es, exml);
}
}

*************** explain_outNode(StringInfo str,
*** 889,895 ****
explain_outNode(str, outerPlan(plan),
outerPlanState(planstate),
IsA(plan, BitmapHeapScan) ? outer_plan : NULL,
! indent + 3, es);
}

/* righttree */
--- 1020,1026 ----
explain_outNode(str, outerPlan(plan),
outerPlanState(planstate),
IsA(plan, BitmapHeapScan) ? outer_plan : NULL,
! indent + 3, es, exml);
}

/* righttree */
*************** explain_outNode(StringInfo str,
*** 901,907 ****
explain_outNode(str, innerPlan(plan),
innerPlanState(planstate),
outerPlan(plan),
! indent + 3, es);
}

if (IsA(plan, Append))
--- 1032,1038 ----
explain_outNode(str, innerPlan(plan),
innerPlanState(planstate),
outerPlan(plan),
! indent + 3, es, exml);
}

if (IsA(plan, Append))
*************** explain_outNode(StringInfo str,
*** 929,935 ****
explain_outNode(str, subnode,
appendstate->appendplans[j],
outer_plan,
! indent + 3, es);
j++;
}
}
--- 1060,1066 ----
explain_outNode(str, subnode,
appendstate->appendplans[j],
outer_plan,
! indent + 3, es, exml);
j++;
}
}
*************** explain_outNode(StringInfo str,
*** 953,959 ****
explain_outNode(str, subnode,
bitmapandstate->bitmapplans[j],
outer_plan, /* pass down same outer plan */
! indent + 3, es);
j++;
}
}
--- 1084,1090 ----
explain_outNode(str, subnode,
bitmapandstate->bitmapplans[j],
outer_plan, /* pass down same outer plan */
! indent + 3, es, exml);
j++;
}
}
*************** explain_outNode(StringInfo str,
*** 977,983 ****
explain_outNode(str, subnode,
bitmaporstate->bitmapplans[j],
outer_plan, /* pass down same outer plan */
! indent + 3, es);
j++;
}
}
--- 1108,1114 ----
explain_outNode(str, subnode,
bitmaporstate->bitmapplans[j],
outer_plan, /* pass down same outer plan */
! indent + 3, es, exml);
j++;
}
}
*************** explain_outNode(StringInfo str,
*** 995,1001 ****
explain_outNode(str, subnode,
subquerystate->subplan,
NULL,
! indent + 3, es);
}

/* subPlan-s */
--- 1126,1132 ----
explain_outNode(str, subnode,
subquerystate->subplan,
NULL,
! indent + 3, es, exml);
}

/* subPlan-s */
*************** explain_outNode(StringInfo str,
*** 1018,1024 ****
exec_subplan_get_plan(es->pstmt, sp),
sps->planstate,
NULL,
! indent + 4, es);
}
}
}
--- 1149,1155 ----
exec_subplan_get_plan(es->pstmt, sp),
sps->planstate,
NULL,
! indent + 4, es, exml);
}
}
}
*************** explain_outNode(StringInfo str,
*** 1033,1039 ****
static void
show_scan_qual(List *qual, const char *qlabel,
int scanrelid, Plan *outer_plan, Plan *inner_plan,
! StringInfo str, int indent, ExplainState *es)
{
List *context;
bool useprefix;
--- 1164,1171 ----
static void
show_scan_qual(List *qual, const char *qlabel,
int scanrelid, Plan *outer_plan, Plan *inner_plan,
! StringInfo str, int indent, ExplainState *es,
! ExplainXML *exml)
{
List *context;
bool useprefix;
*************** show_scan_qual(List *qual, const char *q
*** 1061,1066 ****
--- 1193,1200 ----
for (i = 0; i < indent; i++)
appendStringInfo(str, " ");
appendStringInfo(str, " %s: %s\n", qlabel, exprstr);
+ appendStringInfo(exml->str," <qualifier type=\"%s\" value=\"%s\" />\n",
+ qlabel, exprstr);
}

/*
*************** show_scan_qual(List *qual, const char *q
*** 1068,1074 ****
*/
static void
show_upper_qual(List *qual, const char *qlabel, Plan *plan,
! StringInfo str, int indent, ExplainState *es)
{
List *context;
bool useprefix;
--- 1202,1209 ----
*/
static void
show_upper_qual(List *qual, const char *qlabel, Plan *plan,
! StringInfo str, int indent, ExplainState *es,
! ExplainXML *exml)
{
List *context;
bool useprefix;
*************** show_upper_qual(List *qual, const char *
*** 1094,1099 ****
--- 1229,1236 ----
for (i = 0; i < indent; i++)
appendStringInfo(str, " ");
appendStringInfo(str, " %s: %s\n", qlabel, exprstr);
+ appendStringInfo(exml->str," <qualifier type=\"%s\" value=\"%s\" />\n",
+ qlabel, exprstr);
}

/*
*************** show_upper_qual(List *qual, const char *
*** 1102,1114 ****
static void
show_sort_keys(Plan *sortplan, int nkeys, AttrNumber *keycols,
const char *qlabel,
! StringInfo str, int indent, ExplainState *es)
{
List *context;
bool useprefix;
int keyno;
char *exprstr;
int i;

if (nkeys <= 0)
return;
--- 1239,1253 ----
static void
show_sort_keys(Plan *sortplan, int nkeys, AttrNumber *keycols,
const char *qlabel,
! StringInfo str, int indent, ExplainState *es,
! ExplainXML *exml)
{
List *context;
bool useprefix;
int keyno;
char *exprstr;
int i;
+ StringInfo condition;

if (nkeys <= 0)
return;
*************** show_sort_keys(Plan *sortplan, int nkeys
*** 1116,1121 ****
--- 1255,1262 ----
for (i = 0; i < indent; i++)
appendStringInfo(str, " ");
appendStringInfo(str, " %s: ", qlabel);
+ appendStringInfo(exml->str," <sort type=\"%s\">\n",
+ qlabel);

/* Set up deparsing context */
context = deparse_context_for_plan((Node *) outerPlan(sortplan),
*************** show_sort_keys(Plan *sortplan, int nkeys
*** 1123,1128 ****
--- 1264,1271 ----
es->rtable);
useprefix = list_length(es->rtable) > 1;

+ condition = makeStringInfo();
+
for (keyno = 0; keyno < nkeys; keyno++)
{
/* find key expression in tlist */
*************** show_sort_keys(Plan *sortplan, int nkeys
*** 1138,1146 ****
--- 1281,1294 ----
if (keyno > 0)
appendStringInfo(str, ", ");
appendStringInfoString(str, exprstr);
+ appendStringInfo(condition, " <key number=\"%d\">%s</key>\n", keyno, exprstr);
}

appendStringInfo(str, "\n");
+ appendStringInfo(exml->str,"%s </sort>\n", condition->data);
+
+ pfree(condition->data);
+ pfree(condition);
}

/*
*************** show_sort_keys(Plan *sortplan, int nkeys
*** 1148,1154 ****
*/
static void
show_sort_info(SortState *sortstate,
! StringInfo str, int indent, ExplainState *es)
{
Assert(IsA(sortstate, SortState));
if (es->printAnalyze && sortstate->sort_Done &&
--- 1296,1303 ----
*/
static void
show_sort_info(SortState *sortstate,
! StringInfo str, int indent, ExplainState *es,
! ExplainXML *exml)
{
Assert(IsA(sortstate, SortState));
if (es->printAnalyze && sortstate->sort_Done &&
*** /dev/fd/63 2007-06-14 17:40:26.666034098 -0400
--- src/backend/parser/gram.y 2007-06-14 15:02:37.870438189 -0400
*************** static Node *makeXmlExpr(XmlExprOp op, c
*** 277,282 ****
--- 277,283 ----
%type <boolean> opt_instead opt_analyze
%type <boolean> index_opt_unique opt_verbose opt_full
%type <boolean> opt_freeze opt_default opt_recheck
+ %type <boolean> opt_xml
%type <defelt> opt_binary opt_oids copy_delimiter

%type <boolean> copy_from
*************** static Node *makeXmlExpr(XmlExprOp op, c
*** 444,449 ****
--- 445,452 ----

WHEN WHERE WHITESPACE_P WITH WITHOUT WORK WRITE

+ XML
+
XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLFOREST XMLPARSE
XMLPI XMLROOT XMLSERIALIZE

*************** opt_name_list:
*** 5521,5536 ****
/*****************************************************************************
*
* QUERY:
! * EXPLAIN [ANALYZE] [VERBOSE] query
*
*****************************************************************************/

! ExplainStmt: EXPLAIN opt_analyze opt_verbose ExplainableStmt
{
ExplainStmt *n = makeNode(ExplainStmt);
n->analyze = $2;
n->verbose = $3;
! n->query = $4;
$$ = (Node *)n;
}
;
--- 5524,5540 ----
/*****************************************************************************
*
* QUERY:
! * EXPLAIN [ANALYZE] [VERBOSE] [XML] query
*
*****************************************************************************/

! ExplainStmt: EXPLAIN opt_analyze opt_verbose opt_xml ExplainableStmt
{
ExplainStmt *n = makeNode(ExplainStmt);
n->analyze = $2;
n->verbose = $3;
! n->xml = $4;
! n->query = $5;
$$ = (Node *)n;
}
;
*************** opt_analyze:
*** 5548,5553 ****
--- 5552,5561 ----
analyze_keyword { $$ = TRUE; }
| /* EMPTY */ { $$ = FALSE; }
;
+ opt_xml:
+ XML { $$ = TRUE; }
+ | /*EMPTY*/ { $$ = FALSE; }
+ ;

/*****************************************************************************
*
*************** unreserved_keyword:
*** 9021,9026 ****
--- 9029,9035 ----
| WITHOUT
| WORK
| WRITE
+ | XML
| XML_P
| YEAR_P
| YES_P
*** /dev/fd/63 2007-06-14 17:40:26.878046180 -0400
--- src/backend/parser/keywords.c 2007-06-14 15:10:06.836023279 -0400
*************** static const ScanKeyword ScanKeywords[]
*** 386,392 ****
{"without", WITHOUT},
{"work", WORK},
{"write", WRITE},
! {"xml", XML_P},
{"xmlattributes", XMLATTRIBUTES},
{"xmlconcat", XMLCONCAT},
{"xmlelement", XMLELEMENT},
--- 386,393 ----
{"without", WITHOUT},
{"work", WORK},
{"write", WRITE},
! {"xml", XML},
! {"xmlp", XML_P},
{"xmlattributes", XMLATTRIBUTES},
{"xmlconcat", XMLCONCAT},
{"xmlelement", XMLELEMENT},
*** /dev/fd/63 2007-06-14 17:40:27.042055526 -0400
--- src/bin/psql/tab-complete.c 2007-06-14 15:13:33.251786243 -0400
*************** psql_completion(char *text, int start, i
*** 1330,1341 ****
/* EXPLAIN */

/*
! * Complete EXPLAIN [ANALYZE] [VERBOSE] with list of EXPLAIN-able commands
*/
else if (pg_strcasecmp(prev_wd, "EXPLAIN") == 0)
{
static const char *const list_EXPLAIN[] =
! {"SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE", "ANALYZE", "VERBOSE", NULL};

COMPLETE_WITH_LIST(list_EXPLAIN);
}
--- 1330,1341 ----
/* EXPLAIN */

/*
! * Complete EXPLAIN [ANALYZE] [VERBOSE] [XML] with list of EXPLAIN-able commands
*/
else if (pg_strcasecmp(prev_wd, "EXPLAIN") == 0)
{
static const char *const list_EXPLAIN[] =
! {"SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE", "ANALYZE", "VERBOSE", "XML", NULL};

COMPLETE_WITH_LIST(list_EXPLAIN);
}
*************** psql_completion(char *text, int start, i
*** 1343,1349 ****
pg_strcasecmp(prev_wd, "ANALYZE") == 0)
{
static const char *const list_EXPLAIN[] =
! {"SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE", "VERBOSE", NULL};

COMPLETE_WITH_LIST(list_EXPLAIN);
}
--- 1343,1349 ----
pg_strcasecmp(prev_wd, "ANALYZE") == 0)
{
static const char *const list_EXPLAIN[] =
! {"SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE", "VERBOSE", "XML", NULL};

COMPLETE_WITH_LIST(list_EXPLAIN);
}
*** /dev/fd/63 2007-06-14 17:40:27.206064873 -0400
--- src/include/nodes/parsenodes.h 2007-06-14 15:14:44.455843931 -0400
*************** typedef struct ExplainStmt
*** 1830,1835 ****
--- 1830,1836 ----
Node *query; /* the query (as a raw parse tree) */
bool verbose; /* print plan info */
bool analyze; /* get statistics by executing plan */
+ bool xml; /* get the output as XML instead text plain */
} ExplainStmt;

/* ----------------------

----- End forwarded message -----

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.