Re: Plperl functions with OUT parameters crashing each other when used in the same connection
pgsql-general-owner@postgresql.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.lang@attiksystem.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
Tom Lane wrote:
"Philippe Lang" <philippe.lang@attiksystem.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?
------------------------------------------------------------
-- 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
$$
@i = ('n', 'm');
@io = ();
@o = ('r', 's');
$c = 0;
foreach $i (@i) {$input{$i} = @_[$c++]};
foreach $io (@io) {$input{$io} = @_[$c]; $output{$io} = @_[$c++]};
foreach $o (@o) {$output{$o} = @_[$c++]};
$output{'r'} = $input{'n'} + $input{'m'};
$output{'s'} = $input{'n'} * $input{'m'};
return_next \%output;
return undef;
$$
LANGUAGE 'plperl' VOLATILE;
------------------------------------------------------------
-- FUNCTION TESTS
------------------------------------------------------------
select * from foo1(45,10);
select * from foo2('45','10');
---------------
Philippe Lang
Attik System
Import Notes
Resolved by subject fallback
pgsql-general-owner@postgresql.org wrote:
Tom Lane wrote:
"Philippe Lang" <philippe.lang@attiksystem.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
Import Notes
Resolved by subject fallback
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