Thread: Plperl functions with OUT parameters crashing each other when used in the same connection
Plperl functions with OUT parameters crashing each other when used in the same connection
From
"Philippe Lang"
Date:
Hi, I've got a strange bug with two plperl functions using OUT parameters: with a fresh ODBC or pgAdmin connection, I can callthe first function, but then all further calls to the second function fail, or call the the second function, but thenall further calls to the first function fail. Even more strange: when the second call fails, the message changes at eachnew try, mentioning one of the variables used as OUT parameters in the other function. Something is apprently not releasedfrom memory between each calls. I'm using plperlu further in the program, with CPAN libraries; is the mix between plperl and plperlu forbidden, or can thisbe a "plperlU" effect? Thanks for your help, Cheers, ------------------------------------------------------------ -- FUNCTION: volets_fiche_fab_1 ------------------------------------------------------------ CREATE OR REPLACE FUNCTION public.volets_fiche_fab_1 ( IN id_commande int4, OUT pos int4, OUT quant int4, OUT nbre_vtx int4, OUT nbre_vtx_total int4, OUT larg_maconnerie int4, OUT haut_maconnerie int4, OUT larg_vtx varchar(20), OUT haut_vtx int4, OUT ouv int4, OUT couvre_joints text, OUT coupe_verticale text, OUT vide_interieur varchar(20), OUT typ varchar(20) ) RETURNS SETOF record AS $$ #---------------------------------------------------------------------------- #-- Configuration des paramètres de la fonction #---------------------------------------------------------------------------- @i = ( 'id_commande' ); @io = (); @o = ( 'pos', 'quant', 'nbre_vtx', 'nbre_vtx_total', 'larg_maconnerie', 'haut_maconnerie', 'larg_vtx', 'haut_vtx', 'ouv', 'couvre_joints', 'coupe_verticale', 'vide_interieur', 'typ' ); #---------------------------------------------------------------------------- #-- Préparation des paramètres de la fonction #---------------------------------------------------------------------------- &start_sub(@_); #---------------------------------------------------------------------------- #-- Création de la fiche de fabrication #---------------------------------------------------------------------------- $lignes_query = 'SELECT * FROM lignes WHERE id_commande = ' . $input{'id_commande'} . ' ORDER BY pos;'; $lignes = spi_exec_query($lignes_query); $lignes_nrows = $lignes->{processed}; foreach my $lignes_rn (0 .. $lignes_nrows - 1) { # Fabrication de la ligne $fab = spi_exec_query('SELECT * FROM volets_fab(' . $lignes->{rows}[$lignes_rn]->{'id'} . ');'); $fab_nrows = $fab->{processed}; # Recherches des éventuels vantaux de gauche et droite my $vtxg; my $vtxd; for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail gauche') and ($j < $fab_nrows); $j = $j + 1) {}; if ($j < $fab_nrows) { $vtxg = $fab->{rows}[$j]->{'larg'}; } for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail droite') and ($j < $fab_nrows); $j = $j + 1) {}; if ($j < $fab_nrows) { $vtxd = $fab->{rows}[$j]->{'larg'}; } # Position $output{'pos'} = $lignes->{rows}[$lignes_rn]->{'pos'}; # Quantité $output{'quant'} = $lignes->{rows}[$lignes_rn]->{'quant'}; # Nombre de vantaux $output{'nbre_vtx'} = $lignes->{rows}[$lignes_rn]->{'nbre_vtx'}; # Nombre de vantaux total $output{'nbre_vtx_total'} = $lignes->{rows}[$lignes_rn]->{'nbre_vtx'} * $lignes->{rows}[$lignes_rn]->{'quant'}; # Largeur de maçonnerie for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de maçonnerie') and ($j < $fab_nrows); $j = $j + 1) {}; if ($j < $fab_nrows) { $output{'larg_maconnerie'} = $fab->{rows}[$j]->{'larg'}; } else { $output{'larg_maconnerie'} = ''; }; # Hauteur de maçonnerie for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Hauteur de maçonnerie') and ($j < $fab_nrows); $j = $j + 1) {}; if ($j < $fab_nrows) { $output{'haut_maconnerie'} = $fab->{rows}[$j]->{'haut'}; } else { $output{'haut_maconnerie'} = ''; }; # Largeur de vantail if (defined($vtxg) and defined($vtxd)) { # Vantaux asymétriques $output{'larg_vtx'} = $vtxg . " / " . $vtxd; } else { # Vantaux symétriques for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail') and ($j < $fab_nrows); $j = $j + 1) {}; if ($j < $fab_nrows) { $output{'larg_vtx'} = $fab->{rows}[$j]->{'larg'}; } else { $output{'larg_vtx'} = ''; }; } # Hauteur de vantail for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Hauteur de vantail') and ($j < $fab_nrows); $j = $j + 1) {}; if ($j < $fab_nrows) { $output{'haut_vtx'} = $fab->{rows}[$j]->{'haut'}; } else { $output{'haut_vtx'} = ''; }; # Type d'ouverture $output{'ouv'} = $lignes->{rows}[$lignes_rn]->{'ouv'}; # Image des couvre-joints for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Couvre-joints') and ($j < $fab_nrows); $j = $j + 1) {}; if ($j < $fab_nrows) { $output{'couvre_joints'} = $fab->{rows}[$j]->{'image'}; } else { $output{'couvre_joints'} = ''; }; # Image de la coupe verticape for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Coupe verticale') and ($j < $fab_nrows); $j = $j + 1) {}; if ($j < $fab_nrows) { $output{'coupe_verticale'} = $fab->{rows}[$j]->{'image'}; } else { $output{'coupe_verticale'} = ''; }; # Vide intérieur if (defined($vtxg) and defined($vtxd)) { # Vantaux asymétriques $output{'vide_interieur'} = ($vtxg - 106) . " / " . ($vtxd - 106); } else { # Vantaux symétriques for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail') and ($j < $fab_nrows); $j = $j + 1) {}; if ($j < $fab_nrows) { $output{'vide_interieur'} = $fab->{rows}[$j]->{'larg'} - 106; } else { $output{'vide_interieur'} = ''; }; } # Type de volet $output{'typ'} = $lignes->{rows}[$lignes_rn]->{'typ'}; # Sortie ret(@_); } #---------------------------------------------------------------------------- #-- Helper functions #---------------------------------------------------------------------------- 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: volets_fiche_fab_2 ------------------------------------------------------------ CREATE OR REPLACE FUNCTION public.volets_fiche_fab_2 ( IN id_commande int4, OUT pos int4, OUT article varchar(50), OUT montage varchar(50), OUT quant int4, OUT long int4, OUT larg int4, OUT haut int4 ) RETURNS SETOF record AS $$ #---------------------------------------------------------------------------- #-- Configuration des paramètres de la fonction #---------------------------------------------------------------------------- @i = ( 'id_commande' ); @io = (); @o = ( 'pos', 'article', 'montage', 'quant', 'long', 'larg', 'haut' ); #---------------------------------------------------------------------------- #-- Préparation des paramètres de la fonction #---------------------------------------------------------------------------- &start_sub(@_); #---------------------------------------------------------------------------- #-- Création de la fiche de fabrication #---------------------------------------------------------------------------- $lignes_query = 'SELECT * FROM lignes WHERE id_commande = ' . $input{'id_commande'} . ' ORDER BY pos;'; $lignes = spi_exec_query($lignes_query); $lignes_nrows = $lignes->{processed}; foreach my $lignes_rn (0 .. $lignes_nrows - 1) { # Fabrication de la ligne $fab = spi_exec_query('SELECT * FROM volets_fab(' . $lignes->{rows}[$lignes_rn]->{'id'} . ');'); $fab_nrows = $fab->{processed}; # On passe en revue l'éclaté des pièces de la ligne for ($j = 0; $j < $fab_nrows; $j = $j + 1) { if (($fab->{rows}[$j]->{'article'} =~ /^(Montant|Traverse|Palette|Panneau|Baguette|Couvre-joint)/) && ($fab->{rows}[$j]->{'t'} eq 'E')) { $output{'pos'} = $lignes->{rows}[$lignes_rn]->{'pos'}; $output{'article'} = $fab->{rows}[$j]->{'article'}; $output{'montage'} = $fab->{rows}[$j]->{'montage'}; $output{'quant'} = $fab->{rows}[$j]->{'quant'}; $output{'long'} = $fab->{rows}[$j]->{'long'}; $output{'larg'} = $fab->{rows}[$j]->{'larg'}; $output{'haut'} = $fab->{rows}[$j]->{'haut'}; ret(@_); } } } #---------------------------------------------------------------------------- #-- Helper functions #---------------------------------------------------------------------------- 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; ---------------------------------- Philippe Lang, Ing. Dipl. EPFL Attik System rte de la Fonderie 2 1700 Fribourg Switzerland http://www.attiksystem.ch Tel: +41 (26) 422 13 75 Fax: +41 (26) 422 13 76
Attachment
Re: Plperl functions with OUT parameters crashing each other when used in the same connection
From
"Philippe Lang"
Date:
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 eachtime, 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
Attachment
Re: Plperl functions with OUT parameters crashing each other when used in the same connection
From
Tom Lane
Date:
"Philippe Lang" <philippe.lang@attiksystem.ch> writes: > I've got a strange bug with two plperl functions using OUT parameters: What PG version is this? (If it's not recent, I'm wondering about the perl locale-reset issues we fixed awhile ago.) If it is an up-to-date version, please provide a self-contained test case --- it's not clear to me how I should invoke these functions to cause the error, and they seem to rely on tables you didn't provide. regards, tom lane
Re: Plperl functions with OUT parameters crashing each other when used in the same connection
From
Tom Lane
Date:
"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
Re: Plperl functions with OUT parameters crashing each other when used in the same connection
From
"Philippe Lang"
Date:
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 arealive 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
Attachment
Re: Plperl functions with OUT parameters crashing each other when used in the same connection
From
"Philippe Lang"
Date:
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
Attachment
Re: Plperl functions with OUT parameters crashing each other when used in the same connection
From
Randall Lucas
Date:
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