Re: plperl functions not re-entrant? - Mailing list pgsql-general

Martijn van Oosterhout wrote:

On Wed, Mar 14, 2007 at 08:09:24PM -0400, Kenneth Downs wrote: 

What I have noticed is that once the innermost instance exits, none of 
the outer instances execute any further, suggesting that the plperl 
routine is not "re-entrant" (if I am using that term correctly).   

Doesn't sound right, do you have a test case?
 

I've finally gotten back around to this.  Here is my test case.  There are three tables with one row in each.

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 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 update 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