Derby
  1. Derby
  2. DERBY-1621

Trigger action statement is not recompile when there is a change that would affect it.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: 10.2.1.6
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Urgent
    • Issue & fix info:
      Release Note Needed

      Description

      A trigger action statement, such as an INSERT statement is not recompiled when there is some DDL change on the underlying table, such as a CREATE INDEX.

      In the example below a unique index is added to the table (t2) inserted into by the trigger's action statement. When the tirgger fires it does not raise any error (should raise a unique constraint violated error) and does not insert the value into the index. A select from t2 does not show the additional rows in t2 as it is performing an index scan, once the index is dropped the rows appear to the select.

      ij version 10.2
      ij> connect 'jdbc:derby:cs;create=true';
      ij> create table t (i int);
      0 rows inserted/updated/deleted
      ij> create table t2 (i int);
      0 rows inserted/updated/deleted
      ij> create trigger tt after insert on t for each statement mode db2sql
      insert into t2 values 1;
      0 rows inserted/updated/deleted
      ij> insert into t values 1;
      1 row inserted/updated/deleted
      ij> select * from t2;
      I
      -----------
      1

      1 row selected
      ij> create unique index tu on t2;
      0 rows inserted/updated/deleted
      ij> insert into t values 1;
      1 row inserted/updated/deleted
      ij> select * from t2;
      I
      -----------
      1

      1 row selected
      ij> insert into t values 1;
      1 row inserted/updated/deleted
      ij> select * from t2;
      I
      -----------
      1

      1 row selected
      ij> drop index tu;
      0 rows inserted/updated/deleted
      ij> select * from t2;
      I
      -----------
      1
      1
      1

      3 rows selected

      1. derby1621trunkstat04.txt
        1.0 kB
        Yip Ng
      2. derby1621trunkstat03.txt
        1.0 kB
        Yip Ng
      3. derby1621trunkstat02.txt
        1.0 kB
        Yip Ng
      4. derby1621trunkstat01.txt
        0.9 kB
        Yip Ng
      5. derby1621trunkdiff04.txt
        32 kB
        Yip Ng
      6. derby1621trunkdiff03.txt
        31 kB
        Yip Ng
      7. derby1621trunkdiff02.txt
        29 kB
        Yip Ng
      8. derby1621trunkdiff01.txt
        30 kB
        Yip Ng

        Issue Links

          Activity

          Hide
          Daniel John Debrunner added a comment -

          DERBY-1613 may be a symptom of the same issue.

          Show
          Daniel John Debrunner added a comment - DERBY-1613 may be a symptom of the same issue.
          Hide
          Daniel John Debrunner added a comment -

          DERBY-1435 may be a symptom of this issue.

          Show
          Daniel John Debrunner added a comment - DERBY-1435 may be a symptom of this issue.
          Hide
          Mamta A. Satoor added a comment -

          While on the topic of recompilation, I think there might be an issue with views as well. I don't see any code in ViewDescriptor invalidation related methods where the view gets recompiled. May be it is being done somewhere else but I wanted to raise the possible issue. If there indeed is a problem, we can open another Jira entry for it if required.

          Show
          Mamta A. Satoor added a comment - While on the topic of recompilation, I think there might be an issue with views as well. I don't see any code in ViewDescriptor invalidation related methods where the view gets recompiled. May be it is being done somewhere else but I wanted to raise the possible issue. If there indeed is a problem, we can open another Jira entry for it if required.
          Hide
          Daniel John Debrunner added a comment -

          Thanks for looking at this Yip - note that Derby has an existing dependency system already, thus it should be possible to fix this using the existing scheme rather than inventing anything new. E.g. in my example script the select * from t2 does receive to invalidations for the create and drop indexes which allow it to be recompiled to go against the base table or index.

          Show
          Daniel John Debrunner added a comment - Thanks for looking at this Yip - note that Derby has an existing dependency system already, thus it should be possible to fix this using the existing scheme rather than inventing anything new. E.g. in my example script the select * from t2 does receive to invalidations for the create and drop indexes which allow it to be recompiled to go against the base table or index.
          Hide
          Yip Ng added a comment -

          Here is an analysis of the problem and the cause:

          At create trigger time, the stored prepared statement 'insert into t2 values 1' will get compiled. All the dependencies of this stored prep stmt will be cleared out and will be copied to the SPSDescriptor
          which will be serialized to SYSDEPENDS as a stored dependency (provider is table descriptor t2)

          When inserting the value 1 to table t1, the table descriptor t1 will have all the relevent trigger(s) associated with it at bind time. At execution time, the after trigger action is fired. The StatementTriggerExecutor will request the trigger descriptor tt for its action routine via the SPSDescriptor. Since it is not in the sps cache, it will load a
          copy of SPSDescriptor from SYSSTATEMENTS system table. Since the valid field is still true, there is no need to recompile the stored prep statement. The trigger action gets executed. Also note that the trigger descriptor tt associated with the 'insert into t1 values 1' statement has saved the reference for the SPSDescriptor.

          At execution time of the create unique index statement, it will invalidated all the dependents of the table descriptor t2. The stored dependency SPSDescriptor will load from SYSDEPENDS, so Derby is invalidating this "copy" and also successfully updates the SYSSTATEMENTS entry to make this trigger action 's valid column to false.

          Now the problem arises when we execute "insert into t1 values 1" again since its trigger descriptor tt still references with its copy of the SPSDescriptor which is still valid, it won't recompile the stored prep stmt causing the symptoms described above.

          Also note that with the drop index tu statement, at execution time, it invalidates all its dependents but the SPSDescriptor is not one of its dependents.

          Show
          Yip Ng added a comment - Here is an analysis of the problem and the cause: At create trigger time, the stored prepared statement 'insert into t2 values 1' will get compiled. All the dependencies of this stored prep stmt will be cleared out and will be copied to the SPSDescriptor which will be serialized to SYSDEPENDS as a stored dependency (provider is table descriptor t2) When inserting the value 1 to table t1, the table descriptor t1 will have all the relevent trigger(s) associated with it at bind time. At execution time, the after trigger action is fired. The StatementTriggerExecutor will request the trigger descriptor tt for its action routine via the SPSDescriptor. Since it is not in the sps cache, it will load a copy of SPSDescriptor from SYSSTATEMENTS system table. Since the valid field is still true, there is no need to recompile the stored prep statement. The trigger action gets executed. Also note that the trigger descriptor tt associated with the 'insert into t1 values 1' statement has saved the reference for the SPSDescriptor. At execution time of the create unique index statement, it will invalidated all the dependents of the table descriptor t2. The stored dependency SPSDescriptor will load from SYSDEPENDS, so Derby is invalidating this "copy" and also successfully updates the SYSSTATEMENTS entry to make this trigger action 's valid column to false. Now the problem arises when we execute "insert into t1 values 1" again since its trigger descriptor tt still references with its copy of the SPSDescriptor which is still valid, it won't recompile the stored prep stmt causing the symptoms described above. Also note that with the drop index tu statement, at execution time, it invalidates all its dependents but the SPSDescriptor is not one of its dependents.
          Hide
          Yip Ng added a comment -

          Running some initial tests on this patch as well, will post it up for review soon. While going through the SPSDescriptor implementation, I found something perplexing and need some clarifications. I'll address
          them on a follow up comment.

          Show
          Yip Ng added a comment - Running some initial tests on this patch as well, will post it up for review soon. While going through the SPSDescriptor implementation, I found something perplexing and need some clarifications. I'll address them on a follow up comment.
          Hide
          Yip Ng added a comment -

          Another problem with SPS recompilation is that it is using 2 different transactions - one from the user for stored dependencies (SYSDEPENDS) updates and the other one is using an internal nested transaction to update the SYSSTATEMENTS system table. If the user transaction rollback due to a constraint violation error at execution time, Derby will get an inconsistent view of the catalog. In the above example, it will fail to invalidate the SPS when a drop index statement is issued since the conglomerate tu (index) is not registered as a provider for the SPS.

          Show
          Yip Ng added a comment - Another problem with SPS recompilation is that it is using 2 different transactions - one from the user for stored dependencies (SYSDEPENDS) updates and the other one is using an internal nested transaction to update the SYSSTATEMENTS system table. If the user transaction rollback due to a constraint violation error at execution time, Derby will get an inconsistent view of the catalog. In the above example, it will fail to invalidate the SPS when a drop index statement is issued since the conglomerate tu (index) is not registered as a provider for the SPS.
          Hide
          Yip Ng added a comment -

          Attaching patch for DERBY-1621. This patch addresses the trigger recompilation problems mentioned in my previous comments. It will now perform recompilation
          of a trigger when it is invalidated. Also, to address the other problem with having 2 separate transactions (user transaction + internal nested transaction) in the recompilation process, it will now use either one but not both so that the system catalog is consistent. To do this, additional interface methods has to be introduced to dependency manager since currently there is no way of providing a transaction controller to its API. Likewise, additional interface methods have been added so that the caller can specify whether to wait or not for the scan. I ran derbyall before and it passes all the testcases but since the code has changed, I have to remerge my modifications, so I'll need to run derbyall again. Will post the result when it is completed. But the patch is ready for review.

          Show
          Yip Ng added a comment - Attaching patch for DERBY-1621 . This patch addresses the trigger recompilation problems mentioned in my previous comments. It will now perform recompilation of a trigger when it is invalidated. Also, to address the other problem with having 2 separate transactions (user transaction + internal nested transaction) in the recompilation process, it will now use either one but not both so that the system catalog is consistent. To do this, additional interface methods has to be introduced to dependency manager since currently there is no way of providing a transaction controller to its API. Likewise, additional interface methods have been added so that the caller can specify whether to wait or not for the scan. I ran derbyall before and it passes all the testcases but since the code has changed, I have to remerge my modifications, so I'll need to run derbyall again. Will post the result when it is completed. But the patch is ready for review.
          Hide
          Yip Ng added a comment -

          Resubmitting patch derby1621trunkdiff02.txt for code cleanup. Although the patch ran fine with derbyall, there is something not working properly with the wait mechanism. I'll have to look into that.

          On the code change side, I am alittle reluctant to modify the dependency manager(DM) internals, perhaps there is a cleaner way to inform DM to use another transaction controller(tc) object at execution time e.g. add a method to alter the current transaction at execute time in LanguageConnectionContext (lcc) and modify the lcc's getTransactionExecute() accordingly to return the appropriate tc object. However, I am not entirely sure if that is a good idea since there may be multiple threads running on the same connection, one thread is doing SPS recompilation and sets the "alternative" transaction controller in lcc, the other thread is doing an insert statement and when it queries the lcc for its transaction controller, it may get the nested transaction controller which is not right. Is this scenario possible? Comments?

          Show
          Yip Ng added a comment - Resubmitting patch derby1621trunkdiff02.txt for code cleanup. Although the patch ran fine with derbyall, there is something not working properly with the wait mechanism. I'll have to look into that. On the code change side, I am alittle reluctant to modify the dependency manager(DM) internals, perhaps there is a cleaner way to inform DM to use another transaction controller(tc) object at execution time e.g. add a method to alter the current transaction at execute time in LanguageConnectionContext (lcc) and modify the lcc's getTransactionExecute() accordingly to return the appropriate tc object. However, I am not entirely sure if that is a good idea since there may be multiple threads running on the same connection, one thread is doing SPS recompilation and sets the "alternative" transaction controller in lcc, the other thread is doing an insert statement and when it queries the lcc for its transaction controller, it may get the nested transaction controller which is not right. Is this scenario possible? Comments?
          Hide
          Yip Ng added a comment -

          Attaching patch derby1621trunkdiff03.txt, this patch adds a new overloaded open method with a wait option in RowChanger interface and implements it in RowChangerImpl.java.

          After investigating abit, I realized that TransactionController.OPENMODE_LOCK_NOWAIT option only applies to table or table intent locks and not row locks which explains why in some scenarios the recompilation for the nested transaction is still waiting for a lock. e.g.:

          ...
          create unique index tu on t2;
          create trigger tt after insert on t1 for each statement mode db2sql insert into t2 values 1;
          autocommit off;
          – invalidates the trigger since it depends on tu
          drop index tu; <=== got a S row lock on one of the SYSDEPENDS row.
          insert into t1 values 1; <=== waiting for X row lock on one of the SYSDEPENDS row
          with the nested transaction... not what we want...
          ...

          Is there a way to provide NOWAIT on fetch operation to the store currently?

          Show
          Yip Ng added a comment - Attaching patch derby1621trunkdiff03.txt, this patch adds a new overloaded open method with a wait option in RowChanger interface and implements it in RowChangerImpl.java. After investigating abit, I realized that TransactionController.OPENMODE_LOCK_NOWAIT option only applies to table or table intent locks and not row locks which explains why in some scenarios the recompilation for the nested transaction is still waiting for a lock. e.g.: ... create unique index tu on t2 ; create trigger tt after insert on t1 for each statement mode db2sql insert into t2 values 1; autocommit off; – invalidates the trigger since it depends on tu drop index tu; <=== got a S row lock on one of the SYSDEPENDS row. insert into t1 values 1; <=== waiting for X row lock on one of the SYSDEPENDS row with the nested transaction... not what we want... ... Is there a way to provide NOWAIT on fetch operation to the store currently?
          Hide
          Yip Ng added a comment -

          Attaching patch derby1621trunkdiff04.txt to reflect latest changes in trunk. In summary this patch should address some of the trigger recompilation issues that I have encountered.

          1) Trigger does not get invalidated when the underlying objects it references get modified or invalidated. - resolved.

          2) SPS recompilation may use internal nested transaction and user transaction to update system tables. If the user transaction rolls back due to a constraint violation, the SYSDEPENDS row entries will also be removed, leaving the catalog in an inconsistent state. - resolved.

          3) In transaction mode, if a SPS recompilation occurs, the nested transaction that updates the SYSDEPENDS table might still wait for lock. An improvement would be to allow:
          a) Fetch with NOWAIT in store or
          b) Update to system tables should be done using internal system transaction.

          • Will file a jira improvement for this.

          Let's move this patch forward for review.

          Show
          Yip Ng added a comment - Attaching patch derby1621trunkdiff04.txt to reflect latest changes in trunk. In summary this patch should address some of the trigger recompilation issues that I have encountered. 1) Trigger does not get invalidated when the underlying objects it references get modified or invalidated. - resolved. 2) SPS recompilation may use internal nested transaction and user transaction to update system tables. If the user transaction rolls back due to a constraint violation, the SYSDEPENDS row entries will also be removed, leaving the catalog in an inconsistent state. - resolved. 3) In transaction mode, if a SPS recompilation occurs, the nested transaction that updates the SYSDEPENDS table might still wait for lock. An improvement would be to allow: a) Fetch with NOWAIT in store or b) Update to system tables should be done using internal system transaction. Will file a jira improvement for this. Let's move this patch forward for review.
          Hide
          Rick Hillegas added a comment -

          Thanks, Yip, this looks good. I have committed derby1621trunkdiff04.txt at subversion revision 434046.

          Show
          Rick Hillegas added a comment - Thanks, Yip, this looks good. I have committed derby1621trunkdiff04.txt at subversion revision 434046.
          Hide
          Mamta A. Satoor added a comment -

          Yip, thanks for working on this ciritical issue.

          DERBY-1613 is a duplicate of this bug. I just wondered if the test changes for this bug included the test case mentioned in DERBY-1613.

          Show
          Mamta A. Satoor added a comment - Yip, thanks for working on this ciritical issue. DERBY-1613 is a duplicate of this bug. I just wondered if the test changes for this bug included the test case mentioned in DERBY-1613 .
          Hide
          Rick Hillegas added a comment -

          Turning off the patch-available bit since this has been committed to the trunk.

          Show
          Rick Hillegas added a comment - Turning off the patch-available bit since this has been committed to the trunk.
          Hide
          Rick Hillegas added a comment -

          Ported DERBY-1621 (434046) to 10.2 branch at subversion revision 436921.

          Show
          Rick Hillegas added a comment - Ported DERBY-1621 (434046) to 10.2 branch at subversion revision 436921.
          Hide
          Yip Ng added a comment -

          Mamta, yes, I included the testcases for all the related duplicate jiras into this jira.
          Thanks for porting this to 10.2, Rick.

          Show
          Yip Ng added a comment - Mamta, yes, I included the testcases for all the related duplicate jiras into this jira. Thanks for porting this to 10.2, Rick.
          Hide
          Yip Ng added a comment -

          Release note for this jira:

          Release Note for DERBY-1621
          ---------------------------

          PROBLEM
          Trigger action statement is not recompile when there is a change that would affect it.

          SYMPTOMS

          (1) Trigger action such as an INSERT statement does not get recompiled when the underlying
          table is affected by a CREATE or DROP INDEX statement. e.g.:

          create table t (i int);
          create table t2 (i int);
          create trigger tt after insert on t for each statement mode db2sql insert into t2 values 1;
          insert into t values 1;
          select * from t2;
          create unique index tu on t2;
          insert into t values 1;
          select * from t2;
          insert into t values 1;
          1 row inserted/updated/deleted

          The above example creates an unique index on table t2. when the trigger is fired, it did not
          raise an unique constraint error.

          (2) When the trigger action statement underlying view gets dropped, the trigger statement did not get
          recompiled. e.g.:

          create table t11 (c111 int not null primary key, c112 int);
          insert into t11 values(1,1);
          insert into t11 values(2,2);
          create view v21 as select * from user1.t11;
          create table t31 (c311 int);
          create table t32 (c321 int);
          create trigger tr31t31 after insert on t31 for each statement mode db2sql insert into t32 values (select c111 from user1.v21 where c112=1);
          insert into t31 values(1);
          select * from t31;
          select * from t32;
          drop view v21;
          insert into t31 values(1);

          In the above example, a view which the trigger action references is dropped; however, the last SQL
          INSERT statement did not throw an error.

          (3) Conglomerate does not exist occurs in a specific case after dropping a table referenced by a trigger.
          The trigger action is not being recompiled and raises SQLSTATE XSAI2 even though the table being
          dropped was recreated again. e.g.:

          create table t1 (id int, name varchar(20));
          create table t2 (id int);
          create trigger test_trigger after insert on t2 for each row mode db2sql insert into t1 values(100, 'hundred');
          insert into t2 values(1);
          insert into t2 values(1);
          select * from t1;
          drop table t1;
          insert into t2 values(1);
          create table t1 (id int, name varchar(20));
          insert into t2 values(1);

          In the above example, a table which the trigger action references is dropped. The last INSERT
          statement should execute successfully but it raises SQLSTATE XSAI2: The conglomerate (896)
          requested does not exist.

          CAUSE
          Derby did not perform invalidation of the trigger action when object(s) that the trigger
          references are modified or dropped; hence, resulting in the stated problem above. The
          affected versions are Derby 10.0 and 10.1.

          SOLUTION
          A fix to resolve the above Derby symptoms is available in 10.2.

          WORKAROUND
          None.

          Show
          Yip Ng added a comment - Release note for this jira: Release Note for DERBY-1621 --------------------------- PROBLEM Trigger action statement is not recompile when there is a change that would affect it. SYMPTOMS (1) Trigger action such as an INSERT statement does not get recompiled when the underlying table is affected by a CREATE or DROP INDEX statement. e.g.: create table t (i int); create table t2 (i int); create trigger tt after insert on t for each statement mode db2sql insert into t2 values 1; insert into t values 1; select * from t2; create unique index tu on t2 ; insert into t values 1; select * from t2; insert into t values 1; 1 row inserted/updated/deleted The above example creates an unique index on table t2. when the trigger is fired, it did not raise an unique constraint error. (2) When the trigger action statement underlying view gets dropped, the trigger statement did not get recompiled. e.g.: create table t11 (c111 int not null primary key, c112 int); insert into t11 values(1,1); insert into t11 values(2,2); create view v21 as select * from user1.t11; create table t31 (c311 int); create table t32 (c321 int); create trigger tr31t31 after insert on t31 for each statement mode db2sql insert into t32 values (select c111 from user1.v21 where c112=1); insert into t31 values(1); select * from t31; select * from t32; drop view v21; insert into t31 values(1); In the above example, a view which the trigger action references is dropped; however, the last SQL INSERT statement did not throw an error. (3) Conglomerate does not exist occurs in a specific case after dropping a table referenced by a trigger. The trigger action is not being recompiled and raises SQLSTATE XSAI2 even though the table being dropped was recreated again. e.g.: create table t1 (id int, name varchar(20)); create table t2 (id int); create trigger test_trigger after insert on t2 for each row mode db2sql insert into t1 values(100, 'hundred'); insert into t2 values(1); insert into t2 values(1); select * from t1; drop table t1; insert into t2 values(1); create table t1 (id int, name varchar(20)); insert into t2 values(1); In the above example, a table which the trigger action references is dropped. The last INSERT statement should execute successfully but it raises SQLSTATE XSAI2: The conglomerate (896) requested does not exist. CAUSE Derby did not perform invalidation of the trigger action when object(s) that the trigger references are modified or dropped; hence, resulting in the stated problem above. The affected versions are Derby 10.0 and 10.1. SOLUTION A fix to resolve the above Derby symptoms is available in 10.2. WORKAROUND None.
          Hide
          Andrew McIntyre added a comment -

          This issue has been resolved for over a year with no further movement. Closing.

          Show
          Andrew McIntyre added a comment - This issue has been resolved for over a year with no further movement. Closing.

            People

            • Assignee:
              Yip Ng
              Reporter:
              Daniel John Debrunner
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development