explain root element for auto-explain
The attached tiny patch sets the <explain> root element for auto-explain
XML output, so it looks something like this:
<explain xmlns="http://www.postgresql.org/2009/explain">
<Plan>
<Node-Type>Result</Node-Type>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>0.01</Total-Cost>
<Plan-Rows>1</Plan-Rows>
<Plan-Width>0</Plan-Width>
</Plan>
</explain>
The JSON output looks like this:
[
"Plan": {
"Node Type": "Result",
"Startup Cost": 0.00,
"Total Cost": 0.01,
"Plan Rows": 1,
"Plan Width": 0
}
]
This is worth doing in itself in the XML case for reasons previously
explained, but it also makes it relatively easy to add a Query-Text node
or some such to the structured output, which is very much worth having,
and would be my next proposed step.
cheers
andrew
Attachments:
explain-root.patchtext/x-patch; charset=iso-8859-1; name=explain-root.patchDownload
Index: contrib/auto_explain/auto_explain.c
===================================================================
RCS file: /cvsroot/pgsql/contrib/auto_explain/auto_explain.c,v
retrieving revision 1.7
diff -c -r1.7 auto_explain.c
*** contrib/auto_explain/auto_explain.c 10 Aug 2009 05:46:49 -0000 1.7
--- contrib/auto_explain/auto_explain.c 17 Aug 2009 21:19:08 -0000
***************
*** 222,228 ****
--- 222,230 ----
es.verbose = auto_explain_log_verbose;
es.format = auto_explain_log_format;
+ ExplainBeginOutput(&es);
ExplainPrintPlan(&es, queryDesc);
+ ExplainEndOutput(&es);
/* Remove last line break */
if (es.str->len > 0 && es.str->data[es.str->len - 1] == '\n')
Index: src/backend/commands/explain.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/explain.c,v
retrieving revision 1.189
diff -c -r1.189 explain.c
*** src/backend/commands/explain.c 10 Aug 2009 05:46:50 -0000 1.189
--- src/backend/commands/explain.c 17 Aug 2009 21:19:09 -0000
***************
*** 91,98 ****
bool labeled, ExplainState *es);
static void ExplainDummyGroup(const char *objtype, const char *labelname,
ExplainState *es);
- static void ExplainBeginOutput(ExplainState *es);
- static void ExplainEndOutput(ExplainState *es);
static void ExplainXMLTag(const char *tagname, int flags, ExplainState *es);
static void ExplainJSONLineEnding(ExplainState *es);
static void escape_json(StringInfo buf, const char *str);
--- 91,96 ----
***************
*** 1712,1718 ****
* This is just enough different from processing a subgroup that we need
* a separate pair of subroutines.
*/
! static void
ExplainBeginOutput(ExplainState *es)
{
switch (es->format)
--- 1710,1716 ----
* This is just enough different from processing a subgroup that we need
* a separate pair of subroutines.
*/
! void
ExplainBeginOutput(ExplainState *es)
{
switch (es->format)
***************
*** 1739,1745 ****
/*
* Emit the end-of-output boilerplate.
*/
! static void
ExplainEndOutput(ExplainState *es)
{
switch (es->format)
--- 1737,1743 ----
/*
* Emit the end-of-output boilerplate.
*/
! void
ExplainEndOutput(ExplainState *es)
{
switch (es->format)
Index: src/include/commands/explain.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/explain.h,v
retrieving revision 1.41
diff -c -r1.41 explain.h
*** src/include/commands/explain.h 10 Aug 2009 05:46:50 -0000 1.41
--- src/include/commands/explain.h 17 Aug 2009 21:19:09 -0000
***************
*** 54,59 ****
--- 54,63 ----
extern void ExplainInitState(ExplainState *es);
+ extern void ExplainBeginOutput(ExplainState *es);
+
+ extern void ExplainEndOutput(ExplainState *es);
+
extern TupleDesc ExplainResultDesc(ExplainStmt *stmt);
extern void ExplainOneUtility(Node *utilityStmt, ExplainState *es,
Andrew Dunstan <andrew.dunstan@pgexperts.com> writes:
The attached tiny patch sets the <explain> root element for auto-explain
XML output, so it looks something like this:
This looks reasonable in itself, but it sort of begs the question on
two other things:
* what's the xmlns URL really going to be?
* what's the <Query> element in regular XML output good for? And
shouldn't we make both explain and auto-explain either have that or not?
The JSON output looks like this:
[
"Plan": {
"Node Type": "Result",
"Startup Cost": 0.00,
"Total Cost": 0.01,
"Plan Rows": 1,
"Plan Width": 0
}
]
<squint> Bearing in mind that I know roughly nothing of JSON ... surely
the above is syntactically incorrect? A labeled value should be within
{...} not [...]. I think this is closely related to the point about
<Query>, ie the same semantic nesting level is missing in both cases.
regards, tom lane
Tom Lane wrote:
Andrew Dunstan <andrew.dunstan@pgexperts.com> writes:
The attached tiny patch sets the <explain> root element for auto-explain
XML output, so it looks something like this:This looks reasonable in itself, but it sort of begs the question on
two other things:* what's the xmlns URL really going to be?
By convention it refers to a place where you publish the schema for the
document type, but it is in fact completely arbitrary, and can refer to
a non-existant resource - as long as it is unique - it's just a
namespace designator, and from an XML POV has no more semantic
significance that a schema name does in SQL.
* what's the <Query> element in regular XML output good for? And
shouldn't we make both explain and auto-explain either have that or not?
and also, why isn't it present in the JSON output for either? We seem to
have several places when we output an XML tag but not a corresponding
named JSON node. Is that really a good idea?
The JSON output looks like this:
[
"Plan": {
"Node Type": "Result",
"Startup Cost": 0.00,
"Total Cost": 0.01,
"Plan Rows": 1,
"Plan Width": 0
}
]<squint> Bearing in mind that I know roughly nothing of JSON ... surely
the above is syntactically incorrect? A labeled value should be within
{...} not [...]. I think this is closely related to the point about
<Query>, ie the same semantic nesting level is missing in both cases.
Looks like it. <http://www.jsonlint.com/> is useful for checking such
things.
Of course, the current JSON output from auto-explain (i.e. without the
enclosing [ ] ) is also illegal, unlike the output from "explain (format
json) select 1", which encloses the Plan node in { } inside the [ ],
cheers
andrew
Andrew Dunstan wrote:
Tom Lane wrote:
* what's the xmlns URL really going to be?
By convention it refers to a place where you publish the schema for
the document type, but it is in fact completely arbitrary, and can
refer to a non-existant resource - as long as it is unique - it's just
a namespace designator, and from an XML POV has no more semantic
significance that a schema name does in SQL.
One thing I definitely think we should do is to put the namespace URL in
a header file. Think of it as being a bit like the catversion.
Hardcoding it in explain.c doesn't seem like a good idea.
cheers
andrew
Andrew Dunstan <andrew@dunslane.net> writes:
One thing I definitely think we should do is to put the namespace URL in
a header file. Think of it as being a bit like the catversion.
Hardcoding it in explain.c doesn't seem like a good idea.
Well, it could at least be a #define, but what's the point of exposing
it in a header file --- what other code will use that file?
regards, tom lane
Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
One thing I definitely think we should do is to put the namespace URL in
a header file. Think of it as being a bit like the catversion.
Hardcoding it in explain.c doesn't seem like a good idea.Well, it could at least be a #define, but what's the point of exposing
it in a header file --- what other code will use that file?
Good point. None right now I guess. But certainly a #define.
cheers
andrew
On Tue, Aug 18, 2009 at 02:11, Andrew Dunstan<andrew@dunslane.net> wrote:
Tom Lane wrote:
Andrew Dunstan <andrew.dunstan@pgexperts.com> writes:
The attached tiny patch sets the <explain> root element for auto-explain
XML output, so it looks something like this:This looks reasonable in itself, but it sort of begs the question on
two other things:* what's the xmlns URL really going to be?
By convention it refers to a place where you publish the schema for the
document type, but it is in fact completely arbitrary, and can refer to a
non-existant resource - as long as it is unique - it's just a namespace
designator, and from an XML POV has no more semantic significance that a
schema name does in SQL.
I'd suggest using a different namespace than www.postgresql.org, just
to be sure it won't conflict with some system we use in the future.
Perhaps http://schemas.postgresql.org/<whatever>? It doesn't actually
need to exist until we want to put anything there, but it mustn't
conflict with anything else.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Are we going to publish an XML DTD for EXPLAIN, or have we already?
---------------------------------------------------------------------------
Andrew Dunstan wrote:
The attached tiny patch sets the <explain> root element for auto-explain
XML output, so it looks something like this:<explain xmlns="http://www.postgresql.org/2009/explain">
<Plan>
<Node-Type>Result</Node-Type>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>0.01</Total-Cost>
<Plan-Rows>1</Plan-Rows>
<Plan-Width>0</Plan-Width>
</Plan>
</explain>The JSON output looks like this:
[
"Plan": {
"Node Type": "Result",
"Startup Cost": 0.00,
"Total Cost": 0.01,
"Plan Rows": 1,
"Plan Width": 0
}
]This is worth doing in itself in the XML case for reasons previously
explained, but it also makes it relatively easy to add a Query-Text node
or some such to the structured output, which is very much worth having,
and would be my next proposed step.cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote:
Are we going to publish an XML DTD for EXPLAIN, or have we already?
Not a DTD, but I am working on an XML Schema (DTDs are a bit yesterday).
cheers
andrew
Andrew Dunstan wrote:
Bruce Momjian wrote:
Are we going to publish an XML DTD for EXPLAIN, or have we already?
Not a DTD, but I am working on an XML Schema (DTDs are a bit yesterday).
OK, either one would be good.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Andrew Dunstan <andrew@dunslane.net> writes:
Bruce Momjian wrote:
Are we going to publish an XML DTD for EXPLAIN, or have we already?
Not a DTD, but I am working on an XML Schema (DTDs are a bit yesterday).
+1 ... I asked for a spec for the output format before, and this would
do fine.
regards, tom lane
Andrew Dunstan wrote:
Bruce Momjian wrote:
Are we going to publish an XML DTD for EXPLAIN, or have we already?
Not a DTD, but I am working on an XML Schema (DTDs are a bit yesterday).
Here is a RelaxNG spec which people might find a bit easier to read. It
has been autocreated by a little tool called trang, that I used on a
very large body of explain output that I produced by mangling the
regression tests (and, incidentally, crashing the server in the result -
I still have to chase that up).
I have a couple of questions, however. First, in that long list of
alternatives for a Plan node, can any of them occur more than once?
Second, we are using Item as a child of both Output and Sort-Key nodes.
Are they really describing the same thing? And in any case, Item is a
wonderfully non-informative name, as is Output, for that matter.
BTW - I know this requires tweaking - those xsd:NCName values will
probably just become text, for example.
cheers
andrew
default namespace = "http://www.postgresql.org/2009/explain"
start =
element explain {
element Query {
Plan,
element Triggers { empty },
element Total-Runtime { xsd:decimal }
}
}
Plan =
element Plan {
(element Actual-Loops { xsd:integer }
| element Actual-Rows { xsd:integer }
| element Actual-Startup-Time { xsd:decimal }
| element Actual-Total-Time { xsd:decimal }
| element Alias { text }
| element Filter { text }
| element Function-Name { xsd:NCName }
| element Hash-Cond { text }
| element Index-Name { xsd:NCName }
| element Join-Filter { text }
| element Join-Type { xsd:NCName }
| element Merge-Cond { text }
| element Node-Type { text }
| element One-Time-Filter { text }
| element Output { Item+ }
| element Parent-Relationship { xsd:NCName }
| element Plan-Rows { xsd:integer }
| element Plan-Width { xsd:integer }
| element Plans { Plan* }
| element Recheck-Cond { text }
| element Relation-Name { xsd:NCName }
| element Scan-Direction { xsd:NCName }
| element Schema { xsd:NCName }
| element Sort-Key { Item+ }
| element Sort-Method { text }
| element Sort-Space-Type { xsd:NCName }
| element Sort-Space-Used { xsd:integer }
| element Startup-Cost { xsd:decimal }
| element Strategy { xsd:NCName }
| element Subplan-Name { text }
| element Total-Cost { xsd:decimal })*,
element Index-Cond { text }?
}
Item = element Item { text }
On Thu, Aug 20, 2009 at 9:30 AM, Andrew Dunstan<andrew@dunslane.net> wrote:
Andrew Dunstan wrote:
Bruce Momjian wrote:
Are we going to publish an XML DTD for EXPLAIN, or have we already?
Not a DTD, but I am working on an XML Schema (DTDs are a bit yesterday).
Here is a RelaxNG spec which people might find a bit easier to read. It has
been autocreated by a little tool called trang, that I used on a very large
body of explain output that I produced by mangling the regression tests
This is definitely easy to read, especially for XML.
(and, incidentally, crashing the server in the result - I still have to
chase that up).
Hmm.
I have a couple of questions, however. First, in that long list of
alternatives for a Plan node, can any of them occur more than once?
I don't think so. But I also don't think Index-Cond should be treated
specially, as you have done here.
Second,
we are using Item as a child of both Output and Sort-Key nodes. Are they
really describing the same thing? And in any case, Item is a wonderfully
non-informative name, as is Output, for that matter.
Well, I can't help Output. That's what 8.4-EXPLAIN calls it. I do
think maybe it should be ripped out of EXPLAIN (VERBOSE) and made a
separate option.
Are they really the same thing? Obviously not. I just needed a way
to make a list of scalars in XML and I picked that for want of
creativity.
BTW - I know this requires tweaking - those xsd:NCName values will probably
just become text, for example.
As far as I'm concerned, you're already way ahead producing something
that fits on the screen.
...Robert
Andrew Dunstan <andrew@dunslane.net> writes:
I have a couple of questions, however. First, in that long list of
alternatives for a Plan node, can any of them occur more than once?
No.
Second, we are using Item as a child of both Output and Sort-Key nodes.
Are they really describing the same thing? And in any case, Item is a
wonderfully non-informative name, as is Output, for that matter.
They are both describing expressions. I wanted to rename Item as Expr,
if you'll recall. But I think we should have a concrete plan about
all the tweaks we want to make to the output schema before doing
anything, so I haven't pushed to change it immediately.
I don't see anything wrong with Output --- what else would you call the
output expressions of a node?
regards, tom lane
I wrote:
Andrew Dunstan wrote:
Bruce Momjian wrote:
Are we going to publish an XML DTD for EXPLAIN, or have we already?
Not a DTD, but I am working on an XML Schema (DTDs are a bit yesterday).
Here is a RelaxNG spec which people might find a bit easier to read.
It has been autocreated by a little tool called trang, that I used on
a very large body of explain output that I produced by mangling the
regression tests (and, incidentally, crashing the server in the result
- I still have to chase that up).
Updated version with more complete information (regression crash was due
to my bad script).
cheers
andrew
default namespace = "http://www.postgresql.org/2009/explain"
start =
element explain {
element Query {
Plan,
Triggers,
element Total-Runtime { xsd:decimal }
}+
}
Plan =
element Plan {
(element Actual-Loops { xsd:integer }
| element Actual-Rows { xsd:integer }
| element Actual-Startup-Time { xsd:decimal }
| element Actual-Total-Time { xsd:decimal }
| element Alias { text }
| element CTE-Name { text }
| element Command { text }
| element Filter { text }
| element Function-Name { text }
| element Hash-Cond { text }
| element Index-Cond { text }
| element Index-Name { text }
| element Join-Filter { text }
| element Join-Type { text }
| element Merge-Cond { text }
| element Node-Type { text }
| element One-Time-Filter { text }
| element Output { Item* }
| element Parent-Relationship { text }
| element Plan-Rows { xsd:integer }
| element Plan-Width { xsd:integer }
| element Plans { Plan* }
| element Recheck-Cond { text }
| element Relation-Name { text }
| element Scan-Direction { text }
| element Schema { text }
| element Sort-Key { Item+ }
| element Sort-Method { text }
| element Sort-Space-Type { text }
| element Sort-Space-Used { xsd:integer }
| element Startup-Cost { xsd:decimal }
| element Strategy { text }
| element Subplan-Name { text }
| element Total-Cost { xsd:decimal },
| element TID-Cond { text }
)*
}
Triggers =
element Triggers {
element Trigger {
element Trigger-Name { text },
element Constraint-Name { text }?,
element Relation { text },
element Time { xsd:decimal },
element Calls { xsd:integer }
}*
}
Item = element Item { text }
Andrew Dunstan <andrew@dunslane.net> writes:
Updated version with more complete information (regression crash was due
to my bad script).
I took a look through the source code to match it against this. I found
that you missed a couple of possibilities: we have <Notify /> and
<Utility-Statement /> as alternatives to <Query> just below <explain>.
Also, it looks to me like <Item> is simply being used as an element
of lists (cf ExplainPropertyList); I was mistaken to equate it with
<Expr>. I don't know XML well enough to understand if we really need
that syntactic detail, or if there's a more idiomatic way to treat
lists.
BTW, I wonder why <explain> doesn't have an init-cap like every other
node type name ...
regards, tom lane
Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
Updated version with more complete information (regression crash was due
to my bad script).I took a look through the source code to match it against this. I found
that you missed a couple of possibilities: we have <Notify /> and
<Utility-Statement /> as alternatives to <Query> just below <explain>.
What causes those to happen? Here's how I mangled the regression tests
to produce the output that this analysis was taken from:
perl -spi.bak -e
's/^(insert|update|select|delete|declare|execute|create table .* as)
/explain (analyse true, verbose true, format xml) $1 /i;' *.sql
cheers
andrew
Andrew Dunstan <andrew@dunslane.net> writes:
Tom Lane wrote:
I took a look through the source code to match it against this. I found
that you missed a couple of possibilities: we have <Notify /> and
<Utility-Statement /> as alternatives to <Query> just below <explain>.
What causes those to happen?
You can get a <Notify> via explaining a command that's been affected
by a rule like
CREATE RULE foo ... DO ALSO NOTIFY foo;
I think the <Utility-Statement> case is not actually reachable code at
present. NOTIFY is the only utility command that's allowed in CREATE
RULE, and auto-explain is hooked in in a place where it can't see
utility statements at all. I suppose we could make EXPLAIN throw error
there, instead of printing a node type we'd have to document.
regards, tom lane
On Thu, Aug 20, 2009 at 12:40 PM, Andrew Dunstan<andrew@dunslane.net> wrote:
Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
Updated version with more complete information (regression crash was due
to my bad script).I took a look through the source code to match it against this. I found
that you missed a couple of possibilities: we have <Notify /> and
<Utility-Statement /> as alternatives to <Query> just below <explain>.What causes those to happen? Here's how I mangled the regression tests to
produce the output that this analysis was taken from:perl -spi.bak -e 's/^(insert|update|select|delete|declare|execute|create
table .* as) /explain (analyse true, verbose true, format xml) $1 /i;' *.sql
CREATE RULE foo_notify AS ON UPDATE TO foo DO ALSO NOTIFY bob;
I am not sure that there's any way to get any other kind of utility
statement in there; I think that's just a safety valve in case someone
changes the rule mechanism and forgets to update EXPLAIN.
...Robert