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

pgsql-general-owner(at)postgresql(dot)org wrote:
> Hi,
>
> I've got a strange bug with two plperl functions using OUT
> parameters: with a fresh ODBC or pgAdmin connection, I can
> call the first function, but then all further calls to the
> second function fail, or call the the second function, but
> then all further calls to the first function fail. Even more
> strange: when the second call fails, the message changes at
> each new try, mentioning one of the variables used as OUT
> parameters in the other function. Something is apprently not
> released from memory between each calls.

Here is a reduced example that shows the problem. Calls at the end work independantly, but the second one called fails each time, mentioning columns from the other function.

------------------------------------------------------------
-- 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');

&start_sub(@_);

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

end_sub(@_);

sub start_sub
{
init(@_);
}

sub end_sub
{
return undef;
}

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

sub ret
{
while (($key, $value) = each %output) {if (!defined($value)) {elog(ERROR, 'Valeur indéfinie pour ' . $key)}};
return_next \%output;
init(@_);
}

$$

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

$$

@i = ('n', 'm');
@io = ();
@o = ('r', 's');

&start_sub(@_);

$output{'r'} = $input{'n'} + $input{'m'};
$output{'s'} = $input{'n'} * $input{'m'};
ret();

end_sub(@_);

sub start_sub
{
init(@_);
}

sub end_sub
{
return undef;
}

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

sub ret
{
while (($key, $value) = each %output) {if (!defined($value)) {elog(ERROR, 'Valeur indéfinie pour ' . $key)}};
return_next \%output;
init(@_);
}

$$

LANGUAGE 'plperl' VOLATILE;

------------------------------------------------------------
-- FUNCTION TESTS
------------------------------------------------------------
select * from foo1(45,10);
select * from foo2('45','10');

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

"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