Derby
  1. Derby
  2. DERBY-1261

Two triggers on same table cause "ERROR 54038: Maximum depth of nested triggers was exceeded."

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.0.2.0
    • Fix Version/s: None
    • Component/s: SQL
    • Environment:
      Embedded on Solaris x86
    • Urgency:
      Normal
    • Issue & fix info:
      High Value Fix, Repro attached

      Description

      Two triggers on same table may lead to self-recursion:

      ij> create table t3(i integer primary key, j integer, t timestamp);
      0 rows inserted/updated/deleted
      ij> create trigger tr3i after insert on t3 referencing new as new for each row mode db2sql update t3 set t = current_timestamp where i = new.i;
      0 rows inserted/updated/deleted
      ij> insert into t3 values (1, 1, NULL);
      1 row inserted/updated/deleted
      ij> create trigger tr3u after update on t3 referencing old as old for each row mode db2sql update t3 set t = current_timestamp where i = old.i;
      0 rows inserted/updated/deleted
      ij> insert into t3 values (2, 1, NULL);
      ERROR 54038: Maximum depth of nested triggers was exceeded.
      ij> update t3 set j=j+1;
      1 row inserted/updated/deleted
      ij> create trigger tr3u2 after update on t3 referencing old as old for each row mode db2sql update t3 set j = 0 where i = old.i and j > 2;
      0 rows inserted/updated/deleted
      ij> update t3 set j=j+1;
      ERROR 54038: Maximum depth of nested triggers was exceeded.

      From derby.log:
      2006-04-27 10:03:54.792 GMT Thread[main,5,main] (XID = 1274), (SESSIONID = 0), (DATABASE = testDB), (DRDAID = null), Cleanup action starting
      2006-04-27 10:03:54.792 GMT Thread[main,5,main] (XID = 1274), (SESSIONID = 0), (DATABASE = testDB), (DRDAID = null), Failed Statement is: insert into t3 values (2, 1, NULL)
      ERROR 54038: Maximum depth of nested triggers was exceeded.
      at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:301)
      at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.pushTriggerExecutionContext(GenericLanguageConnectionContext.java:2104)
      at org.apache.derby.impl.sql.execute.InternalTriggerExecutionContext.<init>(InternalTriggerExecutionContext.java:179)
      at org.apache.derby.impl.sql.execute.GenericExecutionFactory.getTriggerExecutionContext(GenericExecutionFactory.java:302)
      at org.apache.derby.impl.sql.execute.TriggerEventActivator.<init>(TriggerEventActivator.java:105)
      at org.apache.derby.impl.sql.execute.UpdateResultSet.fireBeforeTriggers(UpdateResultSet.java:798)
      at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:283)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:361)
      at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:169)
      at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(RowTriggerExecutor.java:110)
      at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:277)
      at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(InsertResultSet.java:1134)
      at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:522)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:361)
      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1161)
      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:567)
      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:497)
      at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:313)
      at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)
      at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:310)
      at org.apache.derby.impl.tools.ij.Main.go(Main.java:203)
      at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:169)
      at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)
      at org.apache.derby.tools.ij.main(ij.java:60)
      Cleanup action completed
      2006-04-27 10:06:18.589 GMT Thread[main,5,main] (XID = 1293), (SESSIONID = 0), (DATABASE = testDB), (DRDAID = null), Cleanup action starting
      2006-04-27 10:06:18.589 GMT Thread[main,5,main] (XID = 1293), (SESSIONID = 0), (DATABASE = testDB), (DRDAID = null), Failed Statement is: update t3 set j=j+1
      ERROR 54038: Maximum depth of nested triggers was exceeded.
      at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:301)
      at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.pushTriggerExecutionContext(GenericLanguageConnectionContext.java:2104)
      at org.apache.derby.impl.sql.execute.InternalTriggerExecutionContext.<init>(InternalTriggerExecutionContext.java:179)
      at org.apache.derby.impl.sql.execute.GenericExecutionFactory.getTriggerExecutionContext(GenericExecutionFactory.java:302)
      at org.apache.derby.impl.sql.execute.TriggerEventActivator.<init>(TriggerEventActivator.java:105)
      at org.apache.derby.impl.sql.execute.UpdateResultSet.fireBeforeTriggers(UpdateResultSet.java:798)
      at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:283)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:361)
      at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:169)
      at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(RowTriggerExecutor.java:110)
      at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:277)
      at org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTriggers(UpdateResultSet.java:825)
      at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:288)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:361)
      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1161)
      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:567)
      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:497)
      at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:313)
      at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)
      at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:310)
      at org.apache.derby.impl.tools.ij.Main.go(Main.java:203)
      at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:169)
      at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)
      at org.apache.derby.tools.ij.main(ij.java:60)
      Cleanup action completed

      1. repro.sql
        0.6 kB
        Kathey Marsden

        Issue Links

          Activity

          Hide
          Daniel John Debrunner added a comment -

          Why is this a bug. Isn't the second trigger (tr3u) defined as a recursive trigger?

          What behaviour are you expecting?

          Show
          Daniel John Debrunner added a comment - Why is this a bug. Isn't the second trigger (tr3u) defined as a recursive trigger? What behaviour are you expecting?
          Hide
          Øystein Grøvlen added a comment -

          I am not quite sure what the definition of a recursive trigger is, and I did not find any definition of that in the documentation. Is a trigger recursive if it updates the record it is fired on? I would think that would be very useful to able to do in many cases, and in fact, it seems like you can do that in Derby as long as you do not define more than one trigger on a table. If I only create the tr3u trigger, everything works fine. However, if I add an insert trigger or another update trigger recursion overflow occur.

          I have not looked at the code, but from the outside it looks like there is a mechanism to prevent recursion in the event that a single update trigger updates the record it was fire on. However, if the trigger is triggered by an update caused by another trigger, recursion is not prevented.

          I guess the behavior I am expecting is that whether a trigger is recursive or not, should not depend on whether it was fired by a direct user update or by a update performed by a trigger.

          Show
          Øystein Grøvlen added a comment - I am not quite sure what the definition of a recursive trigger is, and I did not find any definition of that in the documentation. Is a trigger recursive if it updates the record it is fired on? I would think that would be very useful to able to do in many cases, and in fact, it seems like you can do that in Derby as long as you do not define more than one trigger on a table. If I only create the tr3u trigger, everything works fine. However, if I add an insert trigger or another update trigger recursion overflow occur. I have not looked at the code, but from the outside it looks like there is a mechanism to prevent recursion in the event that a single update trigger updates the record it was fire on. However, if the trigger is triggered by an update caused by another trigger, recursion is not prevented. I guess the behavior I am expecting is that whether a trigger is recursive or not, should not depend on whether it was fired by a direct user update or by a update performed by a trigger.
          Hide
          Daniel John Debrunner added a comment -

          Trigger recursion occurs when there is a cycle in the firing of triggers that leads to recursion. (is that a recursive definition!)

          I would say that this bug is really that behave of triggers is inconsistent when there is potential recursion.

          I expected in your example the first update statement to lead to recursion and 54038 exception.

          And when I ran your test in a clean database I do not see any exception on the second INSERT,
          though if I drop the table and re-run the same SQL statements I do see the 54038 error

          Seems like we need to define clean consistent behaviour, which may come from the SQL standard.

          Show
          Daniel John Debrunner added a comment - Trigger recursion occurs when there is a cycle in the firing of triggers that leads to recursion. (is that a recursive definition!) I would say that this bug is really that behave of triggers is inconsistent when there is potential recursion. I expected in your example the first update statement to lead to recursion and 54038 exception. And when I ran your test in a clean database I do not see any exception on the second INSERT, though if I drop the table and re-run the same SQL statements I do see the 54038 error Seems like we need to define clean consistent behaviour, which may come from the SQL standard.
          Hide
          Bernt M. Johnsen added a comment -

          I have tried to unsetsand the significance of section 4.38 w.r.t. the
          above example, but I am not sure what to think yet.....

          Another interesting (and simpler) example seem to be a bit easier to
          uderstand, and might shed some light on the problem:

          create table tab(i integer primary key, j integer);
          insert into tab values (1, null);
          insert into tab values (2, null);

          create trigger t1
          after update
          on tab
          referencing old as old for each row
          mode db2sql
          update tab set j=1 where i = old.i;

          create trigger t2
          after update
          on tab
          referencing old as old for each row
          mode db2sql
          update tab set j=2 where i = old.i;

          update tab set j=3;

          The way I interpret section 4.38, this should not cause a
          recursion. The update sentence will cause both t1 and t2 to be
          executed in that order and the set of rows/columns changed is the
          same, so trigger t1 should be executed only once.

          Disclaimer: Try to understand section 4.28 yourself......

          Show
          Bernt M. Johnsen added a comment - I have tried to unsetsand the significance of section 4.38 w.r.t. the above example, but I am not sure what to think yet..... Another interesting (and simpler) example seem to be a bit easier to uderstand, and might shed some light on the problem: create table tab(i integer primary key, j integer); insert into tab values (1, null); insert into tab values (2, null); create trigger t1 after update on tab referencing old as old for each row mode db2sql update tab set j=1 where i = old.i; create trigger t2 after update on tab referencing old as old for each row mode db2sql update tab set j=2 where i = old.i; update tab set j=3; The way I interpret section 4.38, this should not cause a recursion. The update sentence will cause both t1 and t2 to be executed in that order and the set of rows/columns changed is the same, so trigger t1 should be executed only once. Disclaimer: Try to understand section 4.28 yourself......
          Hide
          Bernt M. Johnsen added a comment -

          Since I'm not allowed to change my comment (why?):
          In the previous comment: unsetsand=understand

          The result of the update statement should be (1,2),(2,2)

          Show
          Bernt M. Johnsen added a comment - Since I'm not allowed to change my comment (why?): In the previous comment: unsetsand=understand The result of the update statement should be (1,2),(2,2)
          Hide
          Bernt M. Johnsen added a comment -

          And, I forgot t mention that my example fails with
          ERROR 54038: Maximum depth of nested triggers was exceeded.

          Show
          Bernt M. Johnsen added a comment - And, I forgot t mention that my example fails with ERROR 54038: Maximum depth of nested triggers was exceeded.
          Hide
          Deepa Remesh added a comment -

          In the context of DERBY-551 (enabling procedures in triggers), I have been looking at behaviour of triggers and recursion. I came across this issue and on looking at it, I tend to think the examples above are cases of recursion. Even with just the first trigger, I think we should be getting "ERROR 54038: Maximum depth of nested triggers was exceeded. ". I am basing this on the following paragraph from section 4.38 of the spec:

          "During the execution of an SQL-statement, zero or more trigger execution contexts exist, no more
          than one of which is active. The execution of an SQL-data change statement Si creates at least one
          new trigger execution context TECi and causes TECi to become active. TECi remains in existence
          until the completion of Si. An SQL-data change statement Sj that is executed before the completion
          of Si preserves TECi and creates a new trigger execution context TECj that becomes the active one
          and remains in existence until the completion of Sj. At the completion of Sj, TECj ceases to exist
          and TECi is restored as the active trigger execution context."

          I think the example below with just one trigger should cause recursion when we try to fire the trigger. But currently it does not and this looks like a bug to me.

          create table tab(i integer primary key, j integer);
          insert into tab values (1, null);
          insert into tab values (2, null);

          create trigger t1
          after update
          on tab
          referencing old as old for each row
          mode db2sql
          update tab set j=1 where i = old.i;

          update tab set j=3;

          When we execute the update statement ( update tab set j=3; ), it will create a trigger execution context (TEC). The trigger firing will cause another update statement (update tab set j=1 where i = old.i; — this is the triggered-sql-statement) to get executed. This new statement will in turn create a new TEC. This will cause the same trigger to fire and that will create another TEC and so on. Each TEC can execute the trigger once (if trigger event is satisfied). As there will new TECs for each new update statement, the trigger will get fired by each of the associated TECs. So I think this is a case of recursion. I have not read the code surrounding this. This is just my interpretation of the spec.

          Thoughts/comments?

          Show
          Deepa Remesh added a comment - In the context of DERBY-551 (enabling procedures in triggers), I have been looking at behaviour of triggers and recursion. I came across this issue and on looking at it, I tend to think the examples above are cases of recursion. Even with just the first trigger, I think we should be getting "ERROR 54038: Maximum depth of nested triggers was exceeded. ". I am basing this on the following paragraph from section 4.38 of the spec: "During the execution of an SQL-statement, zero or more trigger execution contexts exist, no more than one of which is active. The execution of an SQL-data change statement Si creates at least one new trigger execution context TECi and causes TECi to become active. TECi remains in existence until the completion of Si. An SQL-data change statement Sj that is executed before the completion of Si preserves TECi and creates a new trigger execution context TECj that becomes the active one and remains in existence until the completion of Sj. At the completion of Sj, TECj ceases to exist and TECi is restored as the active trigger execution context." I think the example below with just one trigger should cause recursion when we try to fire the trigger. But currently it does not and this looks like a bug to me. create table tab(i integer primary key, j integer); insert into tab values (1, null); insert into tab values (2, null); create trigger t1 after update on tab referencing old as old for each row mode db2sql update tab set j=1 where i = old.i; update tab set j=3; When we execute the update statement ( update tab set j=3; ), it will create a trigger execution context (TEC). The trigger firing will cause another update statement (update tab set j=1 where i = old.i; — this is the triggered-sql-statement) to get executed. This new statement will in turn create a new TEC. This will cause the same trigger to fire and that will create another TEC and so on. Each TEC can execute the trigger once (if trigger event is satisfied). As there will new TECs for each new update statement, the trigger will get fired by each of the associated TECs. So I think this is a case of recursion. I have not read the code surrounding this. This is just my interpretation of the spec. Thoughts/comments?
          Hide
          Fernanda Pizzorno added a comment -

          I agree with Deepa's interpretation that a trigger should be able to fire itself, but based on the following paragraph on section 4.38 of the spec, I believe that the same row-level trigger should not be executed more than once for the same row.

          "If a row-level trigger RLT is considered as executed for some row R in SC, then RLT is not subsequently executed for R."

          I looked into this issue when I was working on other trigger related issues (DERBY-1043 and DERBY-1064) and I found out that the behavior of the trigger (recursive or not) changed when changes were made to the definition of the table. Regardless of which interpretation of the spec is correct, the sudden change on the behavior of the trigger is definitively a bug.

          When I create the trigger "tr1" on table "t1" it is not recursive.

          create table t1 (a int, b int);

          insert into t1 (a) values (1), (2), (3), (4), (5);

          create trigger tr1
          after update on t1
          referencing old as old
          for each row mode db2sql
          update t1 set b = 1 where a = old.a + 1;

          update t1 set a = a + 10 where a = 1;

          select * from t1;
          A |B
          -----------------------
          11 |NULL
          2 |1
          3 |NULL
          4 |NULL
          5 |NULL

          5 rows selected

          If I later create a new trigger on table "t1" or add a column to table "t1" as the two examples below show, the trigger "tr1" becomes recursive.

          Example 1: creating a new trigger

          create trigger tr2
          after update on t1
          referencing old as old
          for each row mode db2sql
          values(old.a);

          update t1 set a = a + 10 where a = 1;

          select * from t1;
          A |B
          -----------------------
          11 |NULL
          2 |1
          3 |1
          4 |1
          5 |1

          5 rows selected

          Example 2: adding a column

          alter table t1
          add column c int;

          update t1 set a = a + 10 where a = 1;

          select * from t1;
          A |B |C
          -----------------------------------
          11 |NULL |NULL
          2 |1 |NULL
          3 |1 |NULL
          4 |1 |NULL
          5 |1 |NULL

          5 rows selected

          It seems that changing the definition of "t1" (by creating a new trigger, adding a new column, etc) caused the update statement inside the trigger "tr1" to be invalidated and later recompiled. I suspect that the first time the update statement is compiled table "t1" does not have a trigger "tr1" and therefore the update statement does not fire triggers (UpdateResultSet with deferred = false). When the update statement is recompiled (after changes in the definition of the table), the table "t1" does have a trigger "tr1" and the update statement fires triggers (deferred = true).

          Show
          Fernanda Pizzorno added a comment - I agree with Deepa's interpretation that a trigger should be able to fire itself, but based on the following paragraph on section 4.38 of the spec, I believe that the same row-level trigger should not be executed more than once for the same row. "If a row-level trigger RLT is considered as executed for some row R in SC, then RLT is not subsequently executed for R." I looked into this issue when I was working on other trigger related issues ( DERBY-1043 and DERBY-1064 ) and I found out that the behavior of the trigger (recursive or not) changed when changes were made to the definition of the table. Regardless of which interpretation of the spec is correct, the sudden change on the behavior of the trigger is definitively a bug. When I create the trigger "tr1" on table "t1" it is not recursive. create table t1 (a int, b int); insert into t1 (a) values (1), (2), (3), (4), (5); create trigger tr1 after update on t1 referencing old as old for each row mode db2sql update t1 set b = 1 where a = old.a + 1; update t1 set a = a + 10 where a = 1; select * from t1; A |B ----------------------- 11 |NULL 2 |1 3 |NULL 4 |NULL 5 |NULL 5 rows selected If I later create a new trigger on table "t1" or add a column to table "t1" as the two examples below show, the trigger "tr1" becomes recursive. Example 1: creating a new trigger create trigger tr2 after update on t1 referencing old as old for each row mode db2sql values(old.a); update t1 set a = a + 10 where a = 1; select * from t1; A |B ----------------------- 11 |NULL 2 |1 3 |1 4 |1 5 |1 5 rows selected Example 2: adding a column alter table t1 add column c int; update t1 set a = a + 10 where a = 1; select * from t1; A |B |C ----------------------------------- 11 |NULL |NULL 2 |1 |NULL 3 |1 |NULL 4 |1 |NULL 5 |1 |NULL 5 rows selected It seems that changing the definition of "t1" (by creating a new trigger, adding a new column, etc) caused the update statement inside the trigger "tr1" to be invalidated and later recompiled. I suspect that the first time the update statement is compiled table "t1" does not have a trigger "tr1" and therefore the update statement does not fire triggers (UpdateResultSet with deferred = false). When the update statement is recompiled (after changes in the definition of the table), the table "t1" does have a trigger "tr1" and the update statement fires triggers (deferred = true).
          Hide
          Deepa Remesh added a comment -

          Thanks Fernanda for sharing your thoughts on this. I have a slightly different understanding of the following statement:

          "If a row-level trigger RLT is considered as executed for some row R in SC, then RLT is not subsequently executed for R."

          I had read both these statements together and thought "(in a given trigger execution context)" implicitly applies to row-level triggers too.

          "
          A statement-level trigger that is considered as executed for a state change SC (in a given trigger execution context) is not subsequently executed for SC.
          If a row-level trigger RLT is considered as executed for some row R in SC, then RLT is not subsequently executed for R.
          "

          To me, it looked like a SC is tied to a TEC. So I had interpreted it as:
          "If a row-level trigger RLT is considered as executed for some row R in SC (in a given trigger execution context), then RLT is not subsequently executed for R."

          Is this the right interpretation? If yes, I think the same row-level trigger can be executed more than once for the same row from different TECs. It can be executed only once for the same row from the same TEC.

          Show
          Deepa Remesh added a comment - Thanks Fernanda for sharing your thoughts on this. I have a slightly different understanding of the following statement: "If a row-level trigger RLT is considered as executed for some row R in SC, then RLT is not subsequently executed for R." I had read both these statements together and thought "(in a given trigger execution context)" implicitly applies to row-level triggers too. " A statement-level trigger that is considered as executed for a state change SC (in a given trigger execution context) is not subsequently executed for SC. If a row-level trigger RLT is considered as executed for some row R in SC, then RLT is not subsequently executed for R. " To me, it looked like a SC is tied to a TEC. So I had interpreted it as: "If a row-level trigger RLT is considered as executed for some row R in SC (in a given trigger execution context), then RLT is not subsequently executed for R." Is this the right interpretation? If yes, I think the same row-level trigger can be executed more than once for the same row from different TECs. It can be executed only once for the same row from the same TEC.
          Hide
          Yip Ng added a comment -

          This issue seems to be related to DERBY-1652.

          Show
          Yip Ng added a comment - This issue seems to be related to DERBY-1652 .
          Hide
          Kathey Marsden added a comment -

          Verified this still reproduces on trunk 10.11 with attached repro.sql

          Show
          Kathey Marsden added a comment - Verified this still reproduces on trunk 10.11 with attached repro.sql

            People

            • Assignee:
              Unassigned
              Reporter:
              Øystein Grøvlen
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:

                Development