db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yip Ng (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.
Date Thu, 03 Aug 2006 18:50:15 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12425587 ] 
            
Yip Ng commented on DERBY-1621:
-------------------------------

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.
 

> Trigger action statement is not recompile when there is a change that would affect it.
> --------------------------------------------------------------------------------------
>
>                 Key: DERBY-1621
>                 URL: http://issues.apache.org/jira/browse/DERBY-1621
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.0.0
>            Reporter: Daniel John Debrunner
>         Assigned To: Yip Ng
>            Priority: Critical
>             Fix For: 10.2.0.0
>
>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message