
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)


Mon, 05 Jul 2004 09:33:58 -0400
Andrew Dunstan <andrew@dunslane.net>
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 !

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

Mon, 5 Jul 2004 16:30:27 +0200
Darko Prenosil <darko.prenosil@finteh.hr>
Re: strange bug in plperl

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

Mon, 05 Jul 2004 11:37:04 -0400
Andrew Dunstan <andrew@dunslane.net>
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

Mon, 05 Jul 2004 12:21:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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

Mon, 05 Jul 2004 12:28:32 -0400
Andrew Dunstan <andrew@dunslane.net>
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

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

Mon, 5 Jul 2004 15:01:04 -0700
elein <elein@varlena.com>
Re: strange bug in plperl
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
>
>
Tue, 06 Jul 2004 12:03:40 +0300
Sergej Sergeev <ggray@inbox.ru>
Re: [Plperlng-devel] strange bug in plperl
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
>
>
Tue, 06 Jul 2004 12:47:25 +0300
ggray <ggray@bird.cris.net>
Re: 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:
> 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




Tue, 6 Jul 2004 08:09:23 -0400 (EDT)
"Andrew Dunstan" <andrew@dunslane.net>
Re: [Plperlng-devel] strange bug in plperl