Обсуждение: strange bug in plperl
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)
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
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 > > >
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
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
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
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()) {
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()) {