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

• Key: DERBY-1621
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Critical
• Assignee: Yip Ng
• Reporter: Daniel John Debrunner
• Component/s: SQL
• Affects Version/s: 10.2.1.6
• Fix Version/s: 10.2.1.6

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(i);
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

Comments

[Daniel John Debrunner]

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

[Daniel John Debrunner]

DERBY-1435 may be a symptom of this issue.

[Mamta A. Satoor]

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.

[Daniel John Debrunner]

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.

[Yip Ng]

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.
 

[Yip Ng]

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.

[Yip Ng]

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.

[Yip Ng]

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.

[Yip Ng]

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?

[Yip Ng]

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(i);
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?

[Yip Ng]

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.

[Rick Hillegas]

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

[Mamta A. Satoor]

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.

[Rick Hillegas]

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

[Rick Hillegas]

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

[Yip Ng]

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

[Yip Ng]

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(i);
         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.

[Andrew McIntyre]

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