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

[Pavel Stehule]

hello

2008/6/1 Tom Lane <[EMAIL PROTECTED]>:
> "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?

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

*** ./src/backend/executor/spi.c.orig	2008-06-01 17:26:19.000000000 +0200
--- ./src/backend/executor/spi.c	2008-06-01 17:35:01.000000000 +0200
***************
*** 63,68 ****
--- 63,71 ----
  static MemoryContext _SPI_execmem(void);
  static MemoryContext _SPI_procmem(void);
  static bool _SPI_checktuples(void);
+ static Portal _SPI_cursor_open(const char *name, SPIPlanPtr plan,
+ 				Datum *Values, const char *Nulls,
+ 				bool read_only, int pflags);
  
  
  /* =================== interface functions =================== */
***************
*** 908,921 ****
  
  
  /*
!  * SPI_cursor_open()
!  *
!  *	Open a prepared SPI plan as a portal
   */
! Portal
! SPI_cursor_open(const char *name, SPIPlanPtr plan,
  				Datum *Values, const char *Nulls,
! 				bool read_only)
  {
  	CachedPlanSource *plansource;
  	CachedPlan *cplan;
--- 911,923 ----
  
  
  /*
!  * _SPI_cursor_open()
!  *	Open a prepared SPI plan as portal, allows set parameter's pflags
   */
! static Portal
! _SPI_cursor_open(const char *name, SPIPlanPtr plan,
  				Datum *Values, const char *Nulls,
! 				bool read_only, int pflags)
  {
  	CachedPlanSource *plansource;
  	CachedPlan *cplan;
***************
*** 997,1003 ****
  			ParamExternData *prm = &paramLI->params[k];
  
  			prm->ptype = plan->argtypes[k];
! 			prm->pflags = 0;
  			prm->isnull = (Nulls && Nulls[k] == 'n');
  			if (prm->isnull)
  			{
--- 999,1005 ----
  			ParamExternData *prm = &paramLI->params[k];
  
  			prm->ptype = plan->argtypes[k];
! 			prm->pflags = pflags;
  			prm->isnull = (Nulls && Nulls[k] == 'n');
  			if (prm->isnull)
  			{
***************
*** 1130,1135 ****
--- 1132,1154 ----
  
  
  /*
+  * SPI_cursor_open()
+  *
+  *	Open a prepared SPI plan as a portal
+  */
+ Portal
+ SPI_cursor_open(const char *name, SPIPlanPtr plan,
+ 				Datum *Values, const char *Nulls,
+ 				bool read_only)
+ {
+ 	return _SPI_cursor_open(name, plan,
+ 				Values, Nulls,
+ 				read_only, 0);
+ 
+ }
+ 
+ 
+ /*
   * SPI_cursor_open_with_args()
   *
   * Parse and plan a query and open it as a portal.  Like SPI_execute_with_args,
***************
*** 1177,1183 ****
  	/* SPI_cursor_open expects to be called in procedure memory context */
  	_SPI_procmem();
  
! 	result = SPI_cursor_open(name, &plan, Values, Nulls, read_only);
  
  	/* And clean up */
  	_SPI_curid++;
--- 1196,1203 ----
  	/* SPI_cursor_open expects to be called in procedure memory context */
  	_SPI_procmem();
  
! 	/* all params has PARAM_FLAG_CONST flag */
! 	result = _SPI_cursor_open(name, &plan, Values, Nulls, read_only, PARAM_FLAG_CONST);
  
  	/* And clean up */
  	_SPI_curid++;

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