On Wed, Aug 5, 2009 at 7:20 AM, Andrew Dunstan<[email protected]> wrote:
> Robert Haas wrote:
>> On Sun, Aug 2, 2009 at 7:57 PM, Tom Lane<[email protected]> wrote:
>>> Robert Haas <[email protected]> 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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
