Here is a test case to show that plperl functions are not re-entrant.

Level_one: one row
Level_two: one row
Level_thr: one row

The idea is that an AFTER STATEMENT UPDATE trigger on Level_thr calls a
plperl routine. The plperl routine has a hardcoded 3-pass loop that
updates table Level_two.

The Level_Two table also has an AFTER STATEMENT UPDATE trigger that
calls the same plperl routine. The routine this time goes into its
3-pass loop and updates Level_one.

The result is that there should be 9 passes altogether, and the
accumulator in table level_one should have the value 9. Except it only
has the value 5, because the values from these two invocations of the
routine are leaking into each other.

Here is the code to reproduce:

/*
* This code builds the three tables, they are
* all the same and all simple
*/
create table level_one ( col1 char(5),total int);
create table level_two ( col1 char(5),total int);
create table level_thr ( col1 char(5),total int);

/*
* Here is the (non) re-entrant code. For this example it
* is simplified and hard-coded.
*/
create or replace function ReEntrantTester() returns trigger as
$BODY$
if($_TD->{relname} eq'level_thr') {
$table_to_update = 'level_two' ;
}
else {
$table_to_update = 'level_one' ;
}
elog(NOTICE,"We are in ".$_TD->{relname}." and we'd hit
$table_to_update");
$increment =1;
for(my $i=1; $i <= 3; $i++) {
elog(NOTICE,"Updating $table_to_update, pass $i of 3, adding
$increment");
$qu="UPDATE $table_to_update SET total = total + $increment";
elog(NOTICE,$qu);
spi_exec_query($qu);
}
return;
$BODY$
language plperl SECURITY DEFINER;

/*
* Now create two statement level triggers on level 3
* and level 2 tables that each rolls up to the next
* higher level.
*/
CREATE TRIGGER level_thr_aft_stm
AFTER UPDATE ON level_thr
FOR EACH STATEMENT EXECUTE PROCEDURE ReEntrantTester();
CREATE TRIGGER level_two_aft_stm
AFTER UPDATE ON level_two
FOR EACH STATEMENT EXECUTE PROCEDURE ReEntrantTester();


/*
* This code clears the tables to get ready for a run
*/
delete from level_one;
insert into level_one (col1,total) values ('LEV-1',0);

delete from level_two;
insert into level_two (col1,total) values ('LEV-2',0);

delete from level_thr;
insert into level_thr (col1,total) values ('LEV-3',0);

/*
* Before running the test, make sure the value in
* the level_one table is zero:
*/
select * from level_one;


/*
* PULL THE TRIGGER: This is the code that shows if it
* works or not. If the perl routine is re-entrant, then
* the value in level_one will be 9. Else it will not.
*/
UPDATE level_thr SET total=99;


The text that I get is this:


NOTICE: We are in level_thr and we'd hit level_two
NOTICE: Updating level_two, pass 1 of 3, adding 1
NOTICE: UPDATE level_two SET total = total + 1
NOTICE: We are in level_two and we'd hit level_one
CONTEXT: SQL statement "UPDATE level_two SET total = total + 1"
NOTICE: Updating level_one, pass 1 of 3, adding 1
CONTEXT: SQL statement "UPDATE level_two SET total = total + 1"
NOTICE: UPDATE level_one SET total = total + 1
CONTEXT: SQL statement "UPDATE level_one SET total = total + 1"
NOTICE: Updating level_one, pass 2 of 3, adding 1
CONTEXT: SQL statement "UPDATE level_one SET total = total + 1"
NOTICE: UPDATE level_one SET total = total + 1
CONTEXT: SQL statement "UPDATE level_one SET total = total + 1"
NOTICE: Updating level_one, pass 3 of 3, adding 1
CONTEXT: SQL statement "UPDATE level_one SET total = total + 1"
NOTICE: UPDATE level_one SET total = total + 1
CONTEXT: SQL statement "UPDATE level_one SET total = total + 1"
NOTICE: Updating level_one, pass 2 of 3, adding 1
NOTICE: UPDATE level_one SET total = total + 1
NOTICE: Updating level_one, pass 3 of 3, adding 1
NOTICE: UPDATE level_one SET total = total + 1


Notice that the outermost loop does not finish correctly, it has somehow
changed its mind on what table to update, is not actually executing the
queries, but still has kept track of its iterations.

SELECT * FROM level_one gives:

"LEV-1";5


--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com www.andromeda-project.org
631-379-7200 Fax: 631-689-0527


--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com www.andromeda-project.org
631-379-7200 Fax: 631-689-0527




--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com www.andromeda-project.org
631-379-7200 Fax: 631-689-0527


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Kenneth Downs <ken@secdat.com> writes:
> Here is a test case to show that plperl functions are not re-entrant.

I'm very bad in perl, but I think you need to declare all your variables
with "my" to make them local instead of global.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane wrote:
> Kenneth Downs <ken@secdat.com> writes:
>
>> Here is a test case to show that plperl functions are not re-entrant.
>>
>
> I'm very bad in perl, but I think you need to declare all your variables
> with "my" to make them local instead of global.
>
> regards, tom lane
>

Thanks Tom, that did it! I had one "my" in there, but there was another
spot where one was needed.


--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com www.andromeda-project.org
631-379-7200 Fax: 631-689-0527

On Mon, Apr 30, 2007 at 12:12:11PM -0400, Kenneth Downs wrote:
> Tom Lane wrote:
> >Kenneth Downs <ken@secdat.com> writes:
> >
> >>Here is a test case to show that plperl functions are not re-entrant.
> >>
> >
> >I'm very bad in perl, but I think you need to declare all your variables
> >with "my" to make them local instead of global.
> >
> > regards, tom lane
>
> Thanks Tom, that did it! I had one "my" in there, but there was another
> spot where one was needed.

There's a setting you can set in your postgresql.conf that will help
you find these problems more automatically:

custom_variable_classes = 'plperl'
plperl.use_strict = true

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings