Thread: strange bug in plperl

strange bug in plperl

From

Andrew Dunstan

Date:

05 July 2004, 13:34:02

Can anyone suggest why I might be seeing this effect (each notice comes 
out once per row plus once per function call)

thanks

andrew

andrew=# create function tstset() returns setof tst language plperl as $$
andrew$# elog(NOTICE,"tstset called");
andrew$# return [{i=>1,v=>"one"},{i=>2,v=>"two"}];
andrew$# $$;
CREATE FUNCTION
andrew=# select * from tstset();
NOTICE:  tstset called
NOTICE:  tstset called
NOTICE:  tstset calledi |  v 
---+-----1 | one2 | two
(2 rows)

Re: strange bug in plperl

From

Darko Prenosil

Date:

05 July 2004, 14:30:38

Because that is exactly count of "tstset" function being called. Set returning 
functions are called recursively until SRF_RETURN_DONE is returned, and that 
in You case means until last row is fetched.

When You programming functions in "C", there is SRF_ISFIRST_CALL function that  
returns "true" if function is called for the first time, so You can write 
something like this:

if (SRF_ISFIRST_CALL())
{//Code that executes only once
}
else
{//Code that executes per row
}

I do not know how this works with plperl, and this could be a bug, because 
only "return [{i=>1,v=>"one"},{i=>2,v=>"two"}];" should be executed more than 
once (that is the way it is working in pl/psql). 
I'm sorry I can't help more, but do not know much about plperl :-(
Hope some plperl guru will know more...

Regards !

On Monday 05 July 2004 15:33, Andrew Dunstan wrote:
> Can anyone suggest why I might be seeing this effect (each notice comes
> out once per row plus once per function call)
>
> thanks
>
> andrew
>
> andrew=# create function tstset() returns setof tst language plperl as $$
> andrew$# elog(NOTICE,"tstset called");
> andrew$# return [{i=>1,v=>"one"},{i=>2,v=>"two"}];
> andrew$# $$;
> CREATE FUNCTION
> andrew=# select * from tstset();
> NOTICE:  tstset called
> NOTICE:  tstset called
> NOTICE:  tstset called
>  i |  v
> ---+-----
>  1 | one
>  2 | two
> (2 rows)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

Re: strange bug in plperl

From

Andrew Dunstan

Date:

05 July 2004, 15:37:44

OK, thanks. I see where the problem is. We'll fix the SRF code.

cheers

andrew


Darko Prenosil wrote:

>Because that is exactly count of "tstset" function being called. Set returning 
>functions are called recursively until SRF_RETURN_DONE is returned, and that 
>in You case means until last row is fetched.
>
>When You programming functions in "C", there is SRF_ISFIRST_CALL function that  
>returns "true" if function is called for the first time, so You can write 
>something like this:
>
>if (SRF_ISFIRST_CALL())
>{
>    //Code that executes only once
>}
>else
>{
>    //Code that executes per row
>}
>
>
>I do not know how this works with plperl, and this could be a bug, because 
>only "return [{i=>1,v=>"one"},{i=>2,v=>"two"}];" should be executed more than 
>once (that is the way it is working in pl/psql). 
>I'm sorry I can't help more, but do not know much about plperl :-(
>Hope some plperl guru will know more...
>
>Regards !
>
>On Monday 05 July 2004 15:33, Andrew Dunstan wrote:
>  
>
>>Can anyone suggest why I might be seeing this effect (each notice comes
>>out once per row plus once per function call)
>>
>>thanks
>>
>>andrew
>>
>>andrew=# create function tstset() returns setof tst language plperl as $$
>>andrew$# elog(NOTICE,"tstset called");
>>andrew$# return [{i=>1,v=>"one"},{i=>2,v=>"two"}];
>>andrew$# $$;
>>CREATE FUNCTION
>>andrew=# select * from tstset();
>>NOTICE:  tstset called
>>NOTICE:  tstset called
>>NOTICE:  tstset called
>> i |  v
>>---+-----
>> 1 | one
>> 2 | two
>>(2 rows)
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 8: explain analyze is your friend
>>    
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>  
>

Re: strange bug in plperl

From

Tom Lane

Date:

05 July 2004, 16:21:14

Andrew Dunstan <andrew@dunslane.net> writes:
> Can anyone suggest why I might be seeing this effect (each notice comes 
> out once per row plus once per function call)

It looks like you're executing the whole function body once per physical
call, which is certainly not a good plan for a function returning set.
Once you get to the RETURN statement, you probably want to stash away
the array value and then just return elements of it on successive calls,
without reexecuting any user code.
        regards, tom lane

Re: strange bug in plperl

From

Andrew Dunstan

Date:

05 July 2004, 16:28:47


Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>  
>
>>Can anyone suggest why I might be seeing this effect (each notice comes 
>>out once per row plus once per function call)
>>    
>>
>
>It looks like you're executing the whole function body once per physical
>call, which is certainly not a good plan for a function returning set.
>Once you get to the RETURN statement, you probably want to stash away
>the array value and then just return elements of it on successive calls,
>without reexecuting any user code.
>
>
>  
>

Yep. I had come to that conclusion.

cheers

andrew

Re: strange bug in plperl

From

elein

Date:

05 July 2004, 22:03:41

I just reproduced this problem when returning
a composite and NOT as SETOF composite.

An assumption is being made that if the return
value is a composite, that it must be part of a set.
This is incorrect.

Test case available on request--if you don't have
one already.

Spoke with Andrew wrt on #postgresql.

--elein

On Mon, Jul 05, 2004 at 12:28:32PM -0400, Andrew Dunstan wrote:
> 
> 
> Tom Lane wrote:
> 
> >Andrew Dunstan <andrew@dunslane.net> writes:
> > 
> >
> >>Can anyone suggest why I might be seeing this effect (each notice comes 
> >>out once per row plus once per function call)
> >>   
> >>
> >
> >It looks like you're executing the whole function body once per physical
> >call, which is certainly not a good plan for a function returning set.
> >Once you get to the RETURN statement, you probably want to stash away
> >the array value and then just return elements of it on successive calls,
> >without reexecuting any user code.
> >
> >
> > 
> >
> 
> Yep. I had come to that conclusion.
> 
> cheers
> 
> andrew
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>               http://archives.postgresql.org

Re: [Plperlng-devel] strange bug in plperl

From

Sergej Sergeev

Date:

09 July 2004, 21:33:47

Atached patch fix this bug

Serg


Andrew Dunstan wrote:

>
> Can anyone suggest why I might be seeing this effect (each notice
> comes out once per row plus once per function call)
>
> thanks
>
> andrew
>
> andrew=# create function tstset() returns setof tst language plperl as $$
> andrew$# elog(NOTICE,"tstset called");
> andrew$# return [{i=>1,v=>"one"},{i=>2,v=>"two"}];
> andrew$# $$;
> CREATE FUNCTION
> andrew=# select * from tstset();
> NOTICE:  tstset called
> NOTICE:  tstset called
> NOTICE:  tstset called
> i |  v ---+-----
> 1 | one
> 2 | two
> (2 rows)
>
> _______________________________________________
> Plperlng-devel mailing list
> Plperlng-devel@pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/plperlng-devel
>
>
*** plperl.c    2004-07-01 23:50:22.000000000 +0300
--- newplperl.c    2004-07-06 11:57:56.000000000 +0300
***************
*** 99,104 ****
--- 99,105 ----
  static HV  *plperl_proc_hash = NULL;
  AV           *g_row_keys = NULL;
  AV           *g_column_keys = NULL;
+ SV           *srf_perlret=NULL; /*keep returned value*/
  int            g_attr_num = 0;

  /**********************************************************************
***************
*** 839,847 ****
      /* Find or compile the function */
      prodesc = compile_plperl_function(fcinfo->flinfo->fn_oid, false);
      /************************************************************
!      * Call the Perl function
       ************************************************************/
!     perlret = plperl_call_perl_func(prodesc, fcinfo);
      if (prodesc->fn_retistuple && SRF_IS_FIRSTCALL())
      {

--- 840,855 ----
      /* Find or compile the function */
      prodesc = compile_plperl_function(fcinfo->flinfo->fn_oid, false);
      /************************************************************
!      * Call the Perl function if not returning set
       ************************************************************/
!      if (!prodesc->fn_retistuple)
!         perlret = plperl_call_perl_func(prodesc, fcinfo);
!      else {
!         if (SRF_IS_FIRSTCALL()) /*call function only once*/
!             srf_perlret = plperl_call_perl_func(prodesc, fcinfo);
!         perlret = srf_perlret;
!     }
!
      if (prodesc->fn_retistuple && SRF_IS_FIRSTCALL())
      {

Re: strange bug in plperl

From

ggray

Date:

09 July 2004, 21:34:49

Atached patch fix the problem.

Serg

Andrew Dunstan wrote:

>
> Can anyone suggest why I might be seeing this effect (each notice
> comes out once per row plus once per function call)
>
> thanks
>
> andrew
>
> andrew=# create function tstset() returns setof tst language plperl as $$
> andrew$# elog(NOTICE,"tstset called");
> andrew$# return [{i=>1,v=>"one"},{i=>2,v=>"two"}];
> andrew$# $$;
> CREATE FUNCTION
> andrew=# select * from tstset();
> NOTICE:  tstset called
> NOTICE:  tstset called
> NOTICE:  tstset called
> i |  v ---+-----
> 1 | one
> 2 | two
> (2 rows)
>
> _______________________________________________
> Plperlng-devel mailing list
> Plperlng-devel@pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/plperlng-devel
>
>
*** plperl.c    2004-07-01 23:50:22.000000000 +0300
--- newplperl.c    2004-07-06 11:57:56.000000000 +0300
***************
*** 99,104 ****
--- 99,105 ----
  static HV  *plperl_proc_hash = NULL;
  AV           *g_row_keys = NULL;
  AV           *g_column_keys = NULL;
+ SV           *srf_perlret=NULL; /*keep returned value*/
  int            g_attr_num = 0;

  /**********************************************************************
***************
*** 839,847 ****
      /* Find or compile the function */
      prodesc = compile_plperl_function(fcinfo->flinfo->fn_oid, false);
      /************************************************************
!      * Call the Perl function
       ************************************************************/
!     perlret = plperl_call_perl_func(prodesc, fcinfo);
      if (prodesc->fn_retistuple && SRF_IS_FIRSTCALL())
      {

--- 840,855 ----
      /* Find or compile the function */
      prodesc = compile_plperl_function(fcinfo->flinfo->fn_oid, false);
      /************************************************************
!      * Call the Perl function if not returning set
       ************************************************************/
!      if (!prodesc->fn_retistuple)
!         perlret = plperl_call_perl_func(prodesc, fcinfo);
!      else {
!         if (SRF_IS_FIRSTCALL()) /*call function only once*/
!             srf_perlret = plperl_call_perl_func(prodesc, fcinfo);
!         perlret = srf_perlret;
!     }
!
      if (prodesc->fn_retistuple && SRF_IS_FIRSTCALL())
      {