Re: machine-readable explain output v4 - Mailing list pgsql-hackers

Robert Haas wrote:
> On Sun, Aug 2, 2009 at 7:57 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
>   
>> Robert Haas <robertmhaas@gmail.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.

cheers

andrew