On Sunday 02 August 2009 23:34:04 Robert Haas wrote:
> On Sun, Aug 2, 2009 at 12:06 PM, Andres Freund<[email protected]> wrote:
> > Hi Robert, Hi all,
> >
> > On Thursday 30 July 2009 05:05:48 Robert Haas wrote:
> >> OK, here's the updated version of my machine-readable explain output
> >> patch. This needed heavy updating as a result of the changes that Tom
> >> asked me to make to the explain options patch, and the further changes
> >> he made himself. In addition to any regressions I may have introduced
> >> during the rebasing process, there is one definite problem here: in
> >> the previous version of this patch, explain (format xml) returned XML
> >> data; now, it's back to text.
> >>
> >> The reason for this regression is that Tom asked me to change
> >> ExplainStmt to just carry a list of nodes and to do all the parsing in
> >> ExplainQuery. Unfortunately, the TupleDesc is constructed by
> >> ExplainResultDesc() which can't trivially be changed to take an
> >> ExplainState, because UtilityTupleDescriptor() also wants to call it.
> >> We could possibly fix this by a hack similar to the one we already
> >> added to GetCommandLogLevel(), but I haven't done that here.
> > Hm. I think its cleaner to move the option parsing into its own function
> > - its 3 places parsing options then and probably not going to get less. I
> > am not sure this is cleaner than including the parsed options in
> > ExplainStm though... (possibly in a separate struct to avoid changing
> > copy/equal-funcs everytime)
> Well, this is why we need Tom to weigh in.
Yes.
> > Some more comments on the (new) version of the patch:
> > - The regression tests are gone?
> Tom added some that look adequate to me to create_index.sql, as a
> separate commit, so I don't think I need to do this in my patch any
> more. Maybe some of those examples should be changed to output JSON
> or XML, though, but I'd rather leave this up to Tom's discretion on
> commit because I think he has opinions about this and I think my
> chances of guessing what they are are low.
Yea, I was referring to ones using xml/json.
> > - Currently a value scan looks like »Values Scan on "*VALUES*"« What
> > about adding its alias at least in verbose mode? This currently is
> > inconsistent with other scans.
> I don't know why this doesn't work, but I think it's unrelated to this
> patch.
True.
> > Also he output columns of a VALUES scan are named column$N even
> > if names as specified like in AS foo(colname)
> This is consistent with how other types of scans are treated.
> rhaas=# explain verbose select x,y,z from (select * from pg_class)
> a(x,y,z); QUERY PLAN
> ----------------------------------------------------------------------
> Seq Scan on pg_catalog.pg_class (cost=0.00..8.44 rows=244 width=72)
> Output: pg_class.relname, pg_class.relnamespace, pg_class.reltype
> (2 rows)
This is someone weird considering since using it directly in relations works
different:
explain (verbose) SELECT * FROM pg_namespace AS f(a,b,c);
QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on pg_catalog.pg_namespace f (cost=0.00..1.06 rows=6 width=100)
Output: a, b, c
(2 rows)
Not your "guilt" though. Still its rather strange and looks worth fixable.
> > - why do xml/json contain no time units anymore? (e.g. Total Runtime).
> > Admittedly thats already inconsistent in the current text format...
> I'm not sure what you mean by "any more". I don't think any version
> of these patches I ever submitted did otherwise, nor do I think it's
> particularly valuable. Costs are implicitly in units of
> PostgreSQL-costing and times are implicitly in units of milliseconds,
> just as they are in the text format. Changing this would require
> clients to strip off the trailing 'ms' before converting to a
> floating-point number, which seems like an irritation with no
> corresponding benefit.
I did not think any of your patches did - it was just a difference between the
original output and the new formats I just noted - as I said its not even
consistent in the text format.
> > - Code patterns like:
> > if (es->format == EXPLAIN_FORMAT_TEXT)
> > appendStringInfo(es->str, "Total runtime: %.3f
> > ms\n", 1000.0 * totaltime); else if (es->format == EXPLAIN_FORMAT_XML)
> > appendStringInfo(es->str,
> > "
> > <Total-Runtime>%.3f</Total-Runtime>\n", 1000.0 * totaltime); else if
> > (es->format == EXPLAIN_FORMAT_JSON)
> > appendStringInfo(es->str, ",\n \"Total
> > runtime\" : %.3f", 1000.0 * totaltime); or
> > if (es->format == EXPLAIN_FORMAT_TEXT)
> > appendStringInfo(es->str, " for constraint
> > %s", conname); else if (es->format == EXPLAIN_FORMAT_XML) {
> > appendStringInfoString(es->str, "
> > <Constraint-Name>"); escape_xml(es->str, conname);
> > appendStringInfoString(es->str,
> > "</Constraint-Name>\n"); }
> > else if (es->format == EXPLAIN_FORMAT_JSON)
> > {
> > appendStringInfo(es->str, "\n
> > \"Constraint Name\": "); escape_json(es->str, conname);
> > }
> >
> > possibly could be simplified using ExplainPropertyText or a function
> > accepting a format string.
> > At least for the !EXPLAIN_FORMAT_TEXT this seems simple at multiple
> > places in ExplainOnePlan and report_triggers.
> Well, the whole explain output format is pretty idiosyncratic, and I
> had to work pretty hard to beat it into submission. I think that it
> would not be totally trivial to do what you're suggesting here because
> it would require adding code to manage es->indent outside of
> ExplainPrintPlan(), which we currently don't. I'm not sure whether
> that works out to a net win.
Thats why I suggested doing it for JSON/XML only. E.g. like in the attached
patch. The result looks simpler for my eyes.
While re-checking this I noticed a newly introduced bug in report_triggers() -
in case of a trigger/conname==false "Trigger Name" gets printed twice due to a
duplicated else - merge glitch? (fixed in attached patch as well)
From 3a7d221a636a2800c00d7bc9ffa5cd205ac2d1cd Mon Sep 17 00:00:00 2001
From: Andres Freund <[email protected]>
Date: Mon, 3 Aug 2009 00:58:23 +0200
Subject: [PATCH] reduce json/xml duplication + fix duplicate output
---
src/backend/commands/explain.c | 90 ++++++++++++----------------------------
1 files changed, 27 insertions(+), 63 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 9f86444..53e92c1 100644
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
*************** ExplainOnePlan(PlannedStmt *plannedstmt,
*** 427,439 ****
if (es->format == EXPLAIN_FORMAT_TEXT)
appendStringInfo(es->str, "Total runtime: %.3f ms\n",
1000.0 * totaltime);
! else if (es->format == EXPLAIN_FORMAT_XML)
! appendStringInfo(es->str,
! " <Total-Runtime>%.3f</Total-Runtime>\n",
! 1000.0 * totaltime);
! else if (es->format == EXPLAIN_FORMAT_JSON)
! appendStringInfo(es->str, ",\n \"Total runtime\" : %.3f",
! 1000.0 * totaltime);
}
/* Closing boilerplate */
--- 427,439 ----
if (es->format == EXPLAIN_FORMAT_TEXT)
appendStringInfo(es->str, "Total runtime: %.3f ms\n",
1000.0 * totaltime);
! else{
! char b[256];
! sprintf(b, "%.3f", 1000.0 * totaltime);
! es->indent++;
! ExplainPropertyText("Total Runtime", b, 1, es);
! es->indent--;
! }
}
/* Closing boilerplate */
*************** report_triggers(ResultRelInfo *rInfo, bo
*** 535,540 ****
--- 535,543 ----
if (OidIsValid(trig->tgconstraint))
conname = get_constraint_name(trig->tgconstraint);
+ es->indent += 3;
+ es->needs_separator = 0;
+
/*
* In text mode, we avoid printing both the trigger name and the
* constraint name unless VERBOSE is specified. In XML or JSON
*************** report_triggers(ResultRelInfo *rInfo, bo
*** 547,597 ****
else
appendStringInfoString(es->str, "Trigger");
}
! else if (es->format == EXPLAIN_FORMAT_XML)
! {
! appendStringInfoString(es->str, " <Trigger-Name>");
! escape_xml(es->str, trig->tgname);
! appendStringInfoString(es->str, "</Trigger-Name>\n");
! }
! else if (es->format == EXPLAIN_FORMAT_JSON)
{
! appendStringInfo(es->str, "\n \"Trigger Name\": ");
! escape_json(es->str, trig->tgname);
}
if (conname != NULL)
{
if (es->format == EXPLAIN_FORMAT_TEXT)
appendStringInfo(es->str, " for constraint %s", conname);
! else if (es->format == EXPLAIN_FORMAT_XML)
! {
! appendStringInfoString(es->str, " <Constraint-Name>");
! escape_xml(es->str, conname);
! appendStringInfoString(es->str, "</Constraint-Name>\n");
! }
! else if (es->format == EXPLAIN_FORMAT_JSON)
{
! appendStringInfo(es->str, "\n \"Constraint Name\": ");
! escape_json(es->str, conname);
}
pfree(conname);
}
- else
- {
- if (es->format == EXPLAIN_FORMAT_TEXT)
- appendStringInfo(es->str, "Trigger %s", trig->tgname);
- else if (es->format == EXPLAIN_FORMAT_XML)
- {
- appendStringInfoString(es->str, " <Trigger-Name>");
- escape_xml(es->str, trig->tgname);
- appendStringInfoString(es->str, "</Trigger-Name>\n");
- }
- else if (es->format == EXPLAIN_FORMAT_JSON)
- {
- appendStringInfo(es->str, "\n \"Trigger Name\": ");
- escape_json(es->str, trig->tgname);
- }
- }
if (es->format == EXPLAIN_FORMAT_TEXT)
{
--- 550,570 ----
else
appendStringInfoString(es->str, "Trigger");
}
! else
{
! ExplainPropertyText("Trigger Name", trig->tgname, 0, es);
}
if (conname != NULL)
{
if (es->format == EXPLAIN_FORMAT_TEXT)
appendStringInfo(es->str, " for constraint %s", conname);
! else
{
! ExplainPropertyText("Constraint Name", conname, 0, es);
}
pfree(conname);
}
if (es->format == EXPLAIN_FORMAT_TEXT)
{
*************** report_triggers(ResultRelInfo *rInfo, bo
*** 601,630 ****
appendStringInfo(es->str, ": time=%.3f calls=%.0f\n",
1000.0 * instr->total, instr->ntuples);
}
! else if (es->format == EXPLAIN_FORMAT_XML)
! {
! /* In non-text formats, we always show the relation name. */
! appendStringInfoString(es->str, " <Relation>");
! escape_xml(es->str,
! RelationGetRelationName(rInfo->ri_RelationDesc));
! appendStringInfoString(es->str, "</Relation>\n");
! appendStringInfo(es->str, " <Time>%.3f</Time>\n",
! 1000.0 * instr->total);
! appendStringInfo(es->str," <Calls>%.0f</Calls>\n",
! instr->ntuples);
! }
! else if (es->format == EXPLAIN_FORMAT_JSON)
! {
! /* In non-text formats, we always show the relation name. */
! appendStringInfoString(es->str, ",\n \"Relation\": ");
! escape_json(es->str,
! RelationGetRelationName(rInfo->ri_RelationDesc));
! appendStringInfo(es->str, ",\n \"Time\": %.3f",
! 1000.0 * instr->total);
! appendStringInfo(es->str, ",\n \"Calls\": %.0f",
! instr->ntuples);
}
/* Closing boilerplate for this trigger */
if (es->format == EXPLAIN_FORMAT_XML)
appendStringInfoString(es->str, " </Trigger>\n");
--- 574,594 ----
appendStringInfo(es->str, ": time=%.3f calls=%.0f\n",
1000.0 * instr->total, instr->ntuples);
}
! else{
! char b[256];
! ExplainPropertyText("Relation",
! RelationGetRelationName(rInfo->ri_RelationDesc), 0, es);
!
! sprintf(b, "%.3f", 1000.0 * instr->total);
! ExplainPropertyText("Time",
! b, 1, es);
!
! sprintf(b, "%.0f", instr->ntuples);
! ExplainPropertyText("Calls",
! b, 1, es);
}
+ es->indent -= 3;
/* Closing boilerplate for this trigger */
if (es->format == EXPLAIN_FORMAT_XML)
appendStringInfoString(es->str, " </Trigger>\n");
--
1.6.3.3.335.ge09a8
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
