Re: machine-readable explain output v4 - Mailing list pgsql-hackers
From | Robert Haas |
---|---|
Subject | Re: machine-readable explain output v4 |
Date | |
Msg-id | 603c8f070908050452r2e71fae0j78a0650f095761e7@mail.gmail.com Whole thread Raw |
In response to | Re: machine-readable explain output v4 (Andrew Dunstan <andrew@dunslane.net>) |
List | pgsql-hackers |
On Wed, Aug 5, 2009 at 7:20 AM, Andrew Dunstan<andrew@dunslane.net> wrote: > 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. <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
pgsql-hackers by date: