Re: Plperl functions with OUT parameters crashing each other when used in the same connection

pgsql-general-owner(at)postgresql(dot)org wrote:

> Tom Lane wrote:
>> "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> writes:
>>> Here is a reduced example that shows the problem.
>>
>> Hm, I'm no Perl guru, but isn't the second script to be loaded going
>> to redefine those subroutines that the first script defined? I'm
>> pretty sure that there's not an implicit independent namespace for
>> each plperl function.
>>
>> regards, tom lane
>
> Hi Tom,
>
> I'm using PGSQL 8.1.4.
>
> I have deleted the subroutines now, but problem remains. Does
> that mean the variables created inside a plperl function are
> alive for the duration of the database connection?

It seems to be the case: if I rename all the variables in foo2 function, I do not have anymore problems.

Is there a way to "flush" all the variables explicitely?

------------------------------------------------------------
-- FUNCTION: foo1
------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.foo1
(
IN a integer,
IN b integer,
OUT c integer,
OUT d integer
)
RETURNS SETOF record
AS

$$

@i = ('a', 'b');
@io = ();
@o = ('c', 'd');

$c = 0;
foreach $i (@i) {$input{$i} = @_[$c++]};
foreach $io (@io) {$input{$io} = @_[$c]; $output{$io} = @_[$c++]};
foreach $o (@o) {$output{$o} = @_[$c++]};

$output{'c'} = $input{'a'} + $input{'b'};
$output{'d'} = $input{'a'} * $input{'b'};

return_next \%output;

return undef;

$$

LANGUAGE 'plperl' VOLATILE;

------------------------------------------------------------
-- FUNCTION: foo2
------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.foo2
(
IN n varchar(50),
IN m varchar(50),
OUT r integer,
OUT s varchar(50)
)
RETURNS SETOF record
AS

$$

@i2 = ('n', 'm');
@io2 = ();
@o2 = ('r', 's');

$c2 = 0;
foreach $i2 (@i2) {$input2{$i2} = @_[$c2++]};
foreach $io2 (@io2) {$input2{$io2} = @_[$c2]; $output2{$io2} = @_[$c2++]};
foreach $o2 (@o2) {$output2{$o2} = @_[$c2++]};

$output2{'r'} = $input2{'n'} + $input2{'m'};
$output2{'s'} = $input2{'n'} * $input2{'m'};

return_next \%output2;

return undef;

$$

LANGUAGE 'plperl' VOLATILE;

---------------
Philippe Lang
Attik System

On Mon, Sep 04, 2006 at 06:16:49PM +0200, Philippe Lang wrote:
> > I have deleted the subroutines now, but problem remains. Does
> > that mean the variables created inside a plperl function are
> > alive for the duration of the database connection?
>
> It seems to be the case: if I rename all the variables in foo2 function, I do not have anymore problems.
>
> Is there a way to "flush" all the variables explicitely?

It seems to me you should be using lexical block-scoped variables, along the
lines of:

$$
do {
my @i = ('a','b'); # etc.
}

$$

Of course, doing the above doesn't give you the benefits of using
'strict', which enforces declaration (and hence encourages local
scoping) of variables. The following article may cast some light on
using strict (and indeed, plperl in general) with postgres:

http://www.oreillynet.com/pub/a/databases/2006/05/25/the-future-of-perl-in-postgresql.html

Best,

Randall
--
Randall Lucas Tercent, Inc. DF93EAD1