Thread: plperl function called > once in cascading triggers
Consider the case where an AFTER STATEMENT trigger calls a plperl function which performs a loop. Inside of the loop it updates at least one other table. The table being updated has an AFTER STATEMENT trigger that calls the same plperl function (w/different parms of course), which goes into another loop. Presumably this could go to any number of levels. 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). Is this right? Am I missing something?
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? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Martijn van Oosterhout wrote:
Yes, but it is all tied up in my framework. I'll put together a hardcoded example.
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? Have a nice day,
Yes, but it is all tied up in my framework. I'll put together a hardcoded example.
Martijn van Oosterhout wrote:
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
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