strange bug in plperl

Started by Andrew Dunstanover 21 years ago9 messages

andrew@dunslane.net

over 21 years ago

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)

Darko Prenosil

darko.prenosil@finteh.hr

over 21 years ago

In reply to: Andrew Dunstan (#1)

Re: strange bug in plperl

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 !

Show quoted text

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

Andrew Dunstan

andrew@dunslane.net

over 21 years ago

In reply to: Darko Prenosil (#2)

Re: strange bug in plperl

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

cheers

andrew

Darko Prenosil wrote:

Show quoted text

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

Tom Lane

tgl@sss.pgh.pa.us

over 21 years ago

In reply to: Andrew Dunstan (#1)

Re: strange bug in plperl

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

Andrew Dunstan

andrew@dunslane.net

over 21 years ago

In reply to: Tom Lane (#4)

Re: strange bug in plperl

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

elein

elein@varlena.com

over 21 years ago

In reply to: Andrew Dunstan (#5)

Re: strange bug in plperl

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

Show quoted text

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

Sergej Sergeev

ggray@inbox.ru

over 21 years ago

In reply to: Andrew Dunstan (#1)

1 attachment(s)

Re: [Plperlng-devel] strange bug in plperl

Atached patch fix this bug

Serg

Andrew Dunstan wrote:

Show quoted text

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

Attachments:

plperl.c.patchtext/plain; name=plperl.c.patchDownload

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

ggray

ggray@bird.cris.net

over 21 years ago

In reply to: Andrew Dunstan (#1)

1 attachment(s)

Re: strange bug in plperl

Atached patch fix the problem.

Serg

Andrew Dunstan wrote:

Show quoted text

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

Attachments:

plperl.c.patchtext/plain; name=plperl.c.patchDownload

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

Andrew Dunstan

andrew@dunslane.net

over 21 years ago

In reply to: Sergej Sergeev (#7)

Re: [Plperlng-devel] strange bug in plperl

Thanks. I have 2 questions regarding this.

1. Is prodesc->fn_retistuple true if and only if this is a set returning
function? (what about setof int? what about a function returning a single
composite?)

2. I am suspicious about the use of these globals to stash data (and they
should all be marked static in any case so we don't pollute the namespace)
and already have it on my TODO list to examine them more closely. Won't they
get clobbered if our function makes an spi call which in turn calls this or
another perl function? If they will, we might need to use some sort of very
simple stack structure for this data, up to some reasonable level of
recursion (any bids?).

cheers

andrew

Sergej Sergeev said:

Show quoted text

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