Plperl functions not re-entrant, wrong results - Mailing list pgsql-bugs

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