Re: [HACKERS] explain doesn't work with execute using

[Tom Lane]

"Pavel Stehule" <[EMAIL PROTECTED]> 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?

                        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)
                        {

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers