Re: machine-readable explain output v4

On Wed, Aug 5, 2009 at 7:20 AM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
> Robert Haas wrote:
>> On Sun, Aug 2, 2009 at 7:57 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>>>>> 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.
>>>>>>
>>>
>>> I don't see anything particularly wrong with having ExplainResultDesc
>>> do the same kind of thing GetCommandLogLevel is doing.
>>>
>>
>> After I did this, I thought it would be useful to add a regression
>> test to make sure that it is doing the right thing.  So I came up with
>> this:
>>
>> CREATE OR REPLACE FUNCTION test_explain_format(text) RETURNS text AS $$
>> DECLARE
>>        x RECORD;
>> BEGIN
>>        EXECUTE 'explain (format ' || $1 || ') select 1' INTO x;
>>        RETURN pg_typeof(x."QUERY PLAN");
>> END
>> $$ LANGUAGE plpgsql;
>>
>> This works the first time you run it in a particular session, but then
>> if change $1 so as to get a different answer, it fails:
>>
>> rhaas=# select test_explain_format('text');
>>  test_explain_format
>> ---------------------
>>  text
>> (1 row)
>>
>> rhaas=# select test_explain_format('xml');
>> ERROR:  type of "x.QUERY PLAN" does not match that when preparing the plan
>> CONTEXT:  PL/pgSQL function "test_explain_format" line 5 at RETURN
>> rhaas=# discard
>> ALL    PLANS  TEMP
>> rhaas=# discard plans;
>> DISCARD PLANS
>> rhaas=# select test_explain_format('xml');
>> ERROR:  type of "x.QUERY PLAN" does not match that when preparing the plan
>> CONTEXT:  PL/pgSQL function "test_explain_format" line 5 at RETURN
>> rhaas=# discard all;
>> DISCARD ALL
>> rhaas=# select test_explain_format('xml');
>> ERROR:  type of "x.QUERY PLAN" does not match that when preparing the plan
>> CONTEXT:  PL/pgSQL function "test_explain_format" line 5 at RETURN
>> rhaas=#
>>
>> If I quit psql and start back up again, then it works:
>>
>> rhaas=# select test_explain_format('xml');
>>  test_explain_format
>> ---------------------
>>  xml
>> (1 row)
>>
>> So I guess that leads me to -
>>
>> (1) How do I make this work?
>> (2) Is it worth making this work?
>
>
> You could have the function create an inner function which it then runs and
> drops. I have had to perform such gymnastics in the past to get around
> similar problems. And yes, it's ugly as hell.

<hurls>

Well, I guess I could do it like this:

CREATE OR REPLACE FUNCTION test_explain_format() RETURNS text[] AS $$
DECLARE
xt RECORD;
xx RECORD;
xj RECORD;
BEGIN
EXPLAIN (FORMAT TEXT) SELECT 1 INTO xt;
EXPLAIN (FORMAT XML) SELECT 1 INTO xx;
EXPLAIN (FORMAT JSON) SELECT 1 INTO xj;
RETURN ARRAY[
pg_typeof(xt."QUERY PLAN"),
pg_typeof(xx."QUERY PLAN"),
pg_typeof(xj."QUERY PLAN")
];
END
$$ LANGUAGE plpgsql;

Fortunately there is not an unlimited space to probe here...

...Robert