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

Re: explain doesn't work with execute using

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: explain doesn't work with execute using
Date: 2008-06-01 15:36:25
Message-ID: 21479.1212334585@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
"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?

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

In response to

Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2008-06-01 15:43:48
Subject: Re: explain doesn't work with execute using
Previous:From: Jonah H. HarrisDate: 2008-06-01 14:56:10
Subject: Re: Feedback on blog post about Replication Feature decision and its impact

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