machine-readable explain output

On 6/11/09 10:15 PM, Robert Haas wrote:

Here we go, XML and JSON output.

You will need to apply explain_refactor-v4.patch and
explain_options-v2.patch first, then apply the two patches attached to
this message.

Wow, cool. Can this work with auto_explain? That's where I see
machine-readable being most useful.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

On Fri, Jun 12, 2009 at 11:47 AM, Josh Berkus<josh@agliodbs.com> wrote:

On 6/11/09 10:15 PM, Robert Haas wrote:

Here we go, XML and JSON output.

You will need to apply explain_refactor-v4.patch and
explain_options-v2.patch first, then apply the two patches attached to
this message.

Wow, cool.  Can this work with auto_explain?  That's where I see
machine-readable being most useful.

The patch does touch contrib/auto_explain, but just enough to make it
keep working the same way it does now. I don't think it would be too
hard to improve on that, though; I might work on it if I get bored,
but I'm hoping someone else will be motivated enough to do that part.
:-)

How would you go about extracting the XML/JSON bits from the rest of
what is in the log file? (apologies if this is a question I should
already know the answer to)

...Robert

How would you go about extracting the XML/JSON bits from the rest of
what is in the log file? (apologies if this is a question I should
already know the answer to)

If you do CSV output, it's in a field.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

Josh Berkus wrote:

How would you go about extracting the XML/JSON bits from the rest of
what is in the log file? (apologies if this is a question I should
already know the answer to)

If you do CSV output, it's in a field.

And even if it's not, a well formed XML document would be fairly easy to
extract, especially if the root element is well known (e.g. <pg:explain>
or some such). And if the patch doesn't produce a well-formed XML doc
then it needs work ;-). It might be nice if Robert were to post some
samples of the output.

Quick question: does the patch use formal methods using, say, the DOM
API to build up the XML, or informal methods (like
foo.append('<element>'); foo.append(content); foo.append('</element>'); )

As you can tell, I haven't looked over it yet. But I intend to ;-)

cheers

andrew

On Fri, Jun 12, 2009 at 5:13 PM, Andrew Dunstan<andrew@dunslane.net> wrote:

Josh Berkus wrote:

How would you go about extracting the XML/JSON bits from the rest of
what is in the log file?  (apologies if this is a question I should
already know the answer to)

If you do CSV output, it's in a field.

And even if it's not, a well formed XML document would be fairly easy to
extract, especially if the root element is well known (e.g. <pg:explain> or
some such). And if the patch doesn't produce a well-formed XML doc then it
needs work ;-). It might be nice if Robert were to post some samples of the
output.

<pgexplain>, as it happens... I could post some samples of the
output, but it seems like it might be just as well to let those who
are curious try it for themselves. I'd rather get opinions from
people who care enough to download & test than from those who are just
bikeshedding. :-)

Quick question: does the patch use formal methods using, say, the DOM API to
build up the XML, or informal methods  (like foo.append('<element>');
foo.append(content); foo.append('</element>'); )

As you can tell, I haven't looked over it yet. But I intend to ;-)

Use the Source, Luke. :-)

But, it's informal methods. I don't see a lot of value in doing it
the other way, though perhaps I could be convinced otherwise. One
thing that's nice about the way it works now is that the only support
function it requires is a basic XML-escaping function, which it turns
out we already have in the PG sources anyway, though not in a quite
usable form (the infrastructure patch deals with the necessary
adjustments). So you can explain (format xml) even if you compile
without --with-libxml.

If you want to see how the actual XML/JSON stuff works, you might want
to start with the last patch in the series (explain_format). If you
want to commit it, a course of action to which I can give my unbiased
endorsement, then you'll want to start with explain_refactor.

...Robert

On Friday 12 June 2009 08:15:17 Robert Haas wrote:

Here we go, XML and JSON output.

Could you post some examples of how some plans would look in either format?
That would help us judge the particulars.

On Saturday 13 June 2009 01:10:06 Robert Haas wrote:

<pgexplain>, as it happens... I could post some samples of the
output, but it seems like it might be just as well to let those who
are curious try it for themselves. I'd rather get opinions from
people who care enough to download & test than from those who are just
bikeshedding. :-)

I recommend, however, that you think about writing a regression test for this,
so the interfaces are explicit, and those tweaking them in the future know
what they are dealing with.

A couple of comments on the specifics of the output:

For the JSON format:

* Numbers should not be quoted.

For the XML format:

* Instead of <pgexplain>, use <explain> with an XML namespace declaration.

The schema name is missing in either output format. I think that was supposed
to be one of the features of this that the objects are unambiguously
qualified.

I'm not sure I like element names such as <Node-Type>, instead of say
<nodetype>, which is more like HTML and DocBook. (Your way might be more like
SOAP, I guess.)

Also, the result type of an EXPLAIN (format xml) should be type xml, not text.

In general, I like this direction very much. There will probably be more
tweaks on the output format over time. It's not like the plain EXPLAIN hasn't
been tweaked countless times.

On Sat, Jun 13, 2009 at 9:08 AM, Peter Eisentraut<peter_e@gmx.net> wrote:

On Saturday 13 June 2009 01:10:06 Robert Haas wrote:

<pgexplain>, as it happens...  I could post some samples of the
output, but it seems like it might be just as well to let those who
are curious try it for themselves.  I'd rather get opinions from
people who care enough to download & test than from those who are just
bikeshedding.  :-)

I recommend, however, that you think about writing a regression test for this,
so the interfaces are explicit, and those tweaking them in the future know
what they are dealing with.

I would like to have something in this area, but Tom didn't think it
was workable.

http://archives.postgresql.org/message-id/603c8f070904151623ne07d744k615edd4aa669a64a@mail.gmail.com

Currently, we don't even have something trivial like "EXPLAIN SELECT
1" in the regression tests, so even if you completely break EXPLAIN so
that it core dumps (voice of experience speaking here) make check
still passes with flying colors.

One feature I'd like to add is an EXPLAIN-option for "COSTS", so that
you can say explain (costs off) .... Then we could at least try a
couple of simple examples against the build-farm to see whether the
issues that Tom is worried about are problems in practice and to what
degree. But I'm a little reluctant to develop that until at least
some of my existing work is committed, because at present I have no
guarantee either that this patch will be accepted or that it won't be
extensively modified by the committer, thus creating merge conflicts
for me to resolve. However, assuming the infrastructure in the
explain_options patch is accepted in something similar to its current
form, it should be a very easy patch to write when the time comes.

A couple of comments on the specifics of the output:

For the JSON format:

* Numbers should not be quoted.

OK, will fix.

For the XML format:

* Instead of <pgexplain>, use <explain> with an XML namespace declaration.

Could you specify this a bit further, like write out exactly what you
want it to look like? My XML-fu is not very strong.

The schema name is missing in either output format.  I think that was supposed
to be one of the features of this that the objects are unambiguously
qualified.

Well, as I said, I'm not sure that this decision should be made based
on the selected output format. I think it should be controlled by a
separate option that can be used for text, XML, or JSON. Of course,
we also don't want to end up with a zillion options. I think maybe
the existing VERBOSE option could be pressed into service here. Right
now, all it does is print out the output lists for each node, but
maybe it could also have the effect of forcing the schema name to be
emitted, and any other similarly minor verbosities we run across.

There's other weirdness in this area too: when emitting a qual, we
table-qualify column names according to a complex heuristic (for scan
quals, when the outer plan is non-NULL or it's a subquery scan; for
upper quals, when the length of the range-table list is more than 1).
Not sure whether anyone cares about this or not. In a similar vein,
in report_triggers(), we omit the constraint name if there is a
trigger name. All of these seem like fairly good candidates for
things that you might want to behave differently if you ask for
"VERBOSE".

I'm not sure I like element names such as <Node-Type>, instead of say
<nodetype>, which is more like HTML and DocBook.  (Your way might be more like
SOAP, I guess.)

I'm not sure I like them either. I mostly did it that way because I
wanted to maintain consistency with the text output, which uses labels
like "Hash Cond" and "Filter". So I just made the JSON format use
those same labels, and for the XML format, since tag names can't
contain spaces, I just replaced spaces with dashes. Once I made that
decision it seemed like everything else should be consistent, so
that's what I did. But we could certainly subject them all to some
additional regular transformation if we're so inclined. I'm not sure
it's really worth the additional code complexity, but I don't care
very much.

Also, the result type of an EXPLAIN (format xml) should be type xml, not text.

Seems reasonable. I'll see if I can figure out how to do that.

In general, I like this direction very much.  There will probably be more
tweaks on the output format over time.  It's not like the plain EXPLAIN hasn't
been tweaked countless times.

Cool, thanks for the review. I have no illusions it won't get changed
further. In all honesty, I'm most interested in the options syntax.
The multiple output formats portion is just a demonstration that you
can use the options syntax to enable interesting functionality, but I
personally have little use for it. I'm hoping, however, that once we
have a standard way to add options, people will propose more options
that do interesting things; I have a few ideas myself.

...Robert

--On 13. Juni 2009 15:01:43 -0400 Robert Haas <robertmhaas@gmail.com> wrote:

Also, the result type of an EXPLAIN (format xml) should be type xml, not
text.

Seems reasonable. I'll see if I can figure out how to do that.

I suppose it's okay then, that the format is not available when the server
isn't build with --with-libxml ?

--
Thanks

Bernd

Bernd Helmle <mailings@oopsware.de> writes:

--On 13. Juni 2009 15:01:43 -0400 Robert Haas <robertmhaas@gmail.com> wrote:

Also, the result type of an EXPLAIN (format xml) should be type xml, not
text.

Seems reasonable. I'll see if I can figure out how to do that.

I suppose it's okay then, that the format is not available when the server
isn't build with --with-libxml ?

I believe we have things set up so that you can still print "xml" data
without libxml configured in. We'd need to be sure casting to text
works too, but other than that I don't see an issue here.

regards, tom lane

On Sat, Jun 13, 2009 at 6:40 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:

Bernd Helmle <mailings@oopsware.de> writes:

--On 13. Juni 2009 15:01:43 -0400 Robert Haas <robertmhaas@gmail.com> wrote:

Also, the result type of an EXPLAIN (format xml) should be type xml, not
text.

Seems reasonable.  I'll see if I can figure out how to do that.

I suppose it's okay then, that the format is not available when the server
isn't build with --with-libxml ?

I believe we have things set up so that you can still print "xml" data
without libxml configured in.  We'd need to be sure casting to text
works too, but other than that I don't see an issue here.

Hmm, I just tried to do this by modifying ExplainResultDesc to use
XMLOID rather than TEXTOID when stmt->format == EXPLAIN_FORMAT_XML,
and sure enough, explain (format xml) ... fails when --with-libxml is
not specified. But maybe that's not the right way to do it - now that
I think about it, using that in combination with
do_text_output_multiline() seems totally wrong even if we end up
deciding not to worry about the output type, since while there are
multiple rows when the output is considered as text, there is surely
only one row when you look at the whole thing as an XML document. I'm
not too sure how to do this though. Help?

In any event, considering that EXPLAIN is a utility statement and
can't be embedded within a query, I'm not sure what benefit we get out
of returning the data as XML rather than text. This doesn't seem
likely to change either, based on Tom's comments here.

http://archives.postgresql.org/pgsql-hackers/2009-05/msg00969.php

...Robert

Robert Haas <robertmhaas@gmail.com> writes:

In any event, considering that EXPLAIN is a utility statement and
can't be embedded within a query, I'm not sure what benefit we get out
of returning the data as XML rather than text. This doesn't seem
likely to change either, based on Tom's comments here.

http://archives.postgresql.org/pgsql-hackers/2009-05/msg00969.php

I think you misinterpreted the point of that example, which is that
there already is a way to get the output of EXPLAIN into the system
for further processing. Were this not so, we wouldn't be worrying
at all what data type it claims to have. But since there is a way,
it's important what data type it produces.

regards, tom lane

On Sat, Jun 13, 2009 at 7:42 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

In any event, considering that EXPLAIN is a utility statement and
can't be embedded within a query, I'm not sure what benefit we get out
of returning the data as XML rather than text.  This doesn't seem
likely to change either, based on Tom's comments here.

http://archives.postgresql.org/pgsql-hackers/2009-05/msg00969.php

I think you misinterpreted the point of that example, which is that
there already is a way to get the output of EXPLAIN into the system
for further processing.  Were this not so, we wouldn't be worrying
at all what data type it claims to have.  But since there is a way,
it's important what data type it produces.

Well, if you get the EXPLAIN output into the system by defining a
wrapper function, said wrapper function will return the type that it's
defined to return, regardless of what EXPLAIN itself returns, no?

I don't have a problem making it return XML; I'm just not exactly sure
how to do it. Is it possible to get that working without depending on
libxml? How?

...Robert

On Saturday 13 June 2009 22:01:43 Robert Haas wrote:

* Instead of <pgexplain>, use <explain> with an XML namespace
declaration.

Could you specify this a bit further, like write out exactly what you
want it to look like? My XML-fu is not very strong.

Just replace your <pgexplain> by

<explain xmlns="http://www.postgresql.org/2009/explain&quot;&gt;

The actual URI doesn't matter, as long as it is distinguishing. The value I
chose here follows conventions used by W3C.

On Saturday 13 June 2009 22:01:43 Robert Haas wrote:

I recommend, however, that you think about writing a regression test for
this, so the interfaces are explicit, and those tweaking them in the
future know what they are dealing with.

I would like to have something in this area, but Tom didn't think it
was workable.

http://archives.postgresql.org/message-id/603c8f070904151623ne07d744k615edd
4aa669a64a@mail.gmail.com

Currently, we don't even have something trivial like "EXPLAIN SELECT
1" in the regression tests, so even if you completely break EXPLAIN so
that it core dumps (voice of experience speaking here) make check
still passes with flying colors.

That post described a scenario where you check whether given a data set and
ANALYZE, the optimizer produces a certain plan. I agree that that might be
tricky.

A regression test for EXPLAIN, however, should primarily check whether the
output format is stable. We are planning to offer this as a public interface,
after all. You could use faked up statistics and all but one or two plan
types turned off, and then the results should be pretty stable. Unless the
fundamental cost model changes, but it doesn't do that very often for the
simpler plan types anyway. Things to check for would be checking whether all
the fields are there, quoted and escaped correctly, and what happens if
statistics are missing or corrupted, etc. Or whether you get any output at
all, as you say.

On Sunday 14 June 2009 07:27:19 Robert Haas wrote:

On Sat, Jun 13, 2009 at 7:42 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

In any event, considering that EXPLAIN is a utility statement and
can't be embedded within a query, I'm not sure what benefit we get out
of returning the data as XML rather than text. This doesn't seem
likely to change either, based on Tom's comments here.

http://archives.postgresql.org/pgsql-hackers/2009-05/msg00969.php

I think you misinterpreted the point of that example, which is that
there already is a way to get the output of EXPLAIN into the system
for further processing. Were this not so, we wouldn't be worrying
at all what data type it claims to have. But since there is a way,
it's important what data type it produces.

Well, if you get the EXPLAIN output into the system by defining a
wrapper function, said wrapper function will return the type that it's
defined to return, regardless of what EXPLAIN itself returns, no?

I don't have a problem making it return XML; I'm just not exactly sure
how to do it. Is it possible to get that working without depending on
libxml? How?

Even if this doesn't end up being feasible, I feel it's important that the XML
and JSON formats return one datum, not one per line. Otherwise a client that
wants to do some processing on the result will have to do about three extra
steps to get the result usable.

On 6/13/09, Bernd Helmle <mailings@oopsware.de> wrote:

--On 13. Juni 2009 15:01:43 -0400 Robert Haas <robertmhaas@gmail.com> wrote:

Also, the result type of an EXPLAIN (format xml) should be type xml, not
text.

Seems reasonable. I'll see if I can figure out how to do that.

I suppose it's okay then, that the format is not available when the server
isn't build with --with-libxml ?

I hope not, otherwise the usefulness of the format is significantly
reduced (to practically zero) if tools cannot rely on it being
available and have to fall back to something else if it's not
available.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

2009/6/14 Dave Page <dpage@pgadmin.org>:

On 6/13/09, Bernd Helmle <mailings@oopsware.de> wrote:

--On 13. Juni 2009 15:01:43 -0400 Robert Haas <robertmhaas@gmail.com> wrote:

Also, the result type of an EXPLAIN (format xml) should be type xml, not
text.

Seems reasonable.  I'll see if I can figure out how to do that.

I suppose it's okay then, that the format is not available when the server
isn't build with --with-libxml ?

I hope not, otherwise the usefulness of the format is significantly
reduced (to practically zero) if tools cannot rely on it being
available and have to fall back to something else if it's not
available.

I thing so using --with-libxml is good idea. Is nonsense repeat some
necessary xml code like xml escaping and similar. And almost all
distributed PostgreSQL binaries are compiled with xml support, so this
cannot do some problems. When somebody compile pg without xml support,
then he knows what he do.

regards
Pavel Stehule

On 6/14/09, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2009/6/14 Dave Page <dpage@pgadmin.org>:

On 6/13/09, Bernd Helmle <mailings@oopsware.de> wrote:

--On 13. Juni 2009 15:01:43 -0400 Robert Haas <robertmhaas@gmail.com>
wrote:

Also, the result type of an EXPLAIN (format xml) should be type xml,
not
text.

Seems reasonable. I'll see if I can figure out how to do that.

I suppose it's okay then, that the format is not available when the
server
isn't build with --with-libxml ?

I hope not, otherwise the usefulness of the format is significantly
reduced (to practically zero) if tools cannot rely on it being
available and have to fall back to something else if it's not
available.

I thing so using --with-libxml is good idea. Is nonsense repeat some
necessary xml code like xml escaping and similar. And almost all
distributed PostgreSQL binaries are compiled with xml support, so this
cannot do some problems. When somebody compile pg without xml support,
then he knows what he do.

That will mean we never get to use XML explain in pgAdmin. We're not
in the business of writing basic features that might work, if the
postgres packager enabled an option. We need to be able to rely on
such features always being available.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com