Skip site navigation (1) Skip section navigation (2)

Re: explain doesn't work with execute using

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: explain doesn't work with execute using
Date: 2008-06-01 15:43:48
Message-ID: 162867790806010843n31b3ccafq26137a57b9043a20@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
hello

2008/6/1 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
>> I found following bug - using explain in stored procedures like:
>> ...
>> produce wrong result. Real plan is correct, etc variables are
>> substituted. Bud this explain show variables.
>
> This seems to be correctable with a one-line patch: make SPI_cursor_open
> set the CONST flag on parameters it puts into the portal (attached).
> I'm not entirely sure if it's a good idea or not --- comments?

We can do less invasive patch - it's much more ugly, but don't change
any other behave. I am afraid, so one-line patch can change behave of
explain statements in some cases where using variables is correct.

Regards
Pavel Stehule

>
>                        regards, tom lane
>
> Index: src/backend/executor/spi.c
> ===================================================================
> RCS file: /cvsroot/pgsql/src/backend/executor/spi.c,v
> retrieving revision 1.195
> diff -c -r1.195 spi.c
> *** src/backend/executor/spi.c  12 May 2008 20:02:00 -0000      1.195
> --- src/backend/executor/spi.c  1 Jun 2008 15:33:13 -0000
> ***************
> *** 997,1003 ****
>                        ParamExternData *prm = &paramLI->params[k];
>
>                        prm->ptype = plan->argtypes[k];
> !                       prm->pflags = 0;
>                        prm->isnull = (Nulls && Nulls[k] == 'n');
>                        if (prm->isnull)
>                        {
> --- 997,1010 ----
>                        ParamExternData *prm = &paramLI->params[k];
>
>                        prm->ptype = plan->argtypes[k];
> !                       /*
> !                        * We mark the parameters as const.  This has no effect for simple
> !                        * execution of a plan, but if more planning happens within the
> !                        * portal (eg via EXPLAIN), the effect will be to treat the
> !                        * parameters as constants.  This is good and correct as long as
> !                        * no plan generated inside the portal is used outside it.
> !                        */
> !                       prm->pflags = PARAM_FLAG_CONST;
>                        prm->isnull = (Nulls && Nulls[k] == 'n');
>                        if (prm->isnull)
>                        {
>

Attachment: spi_execute_using_explain.diff
Description: text/x-patch (2.6 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-06-01 15:55:52
Subject: Re: explain doesn't work with execute using
Previous:From: Tom LaneDate: 2008-06-01 15:36:25
Subject: Re: explain doesn't work with execute using

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group