[
https://issues.apache.org/jira/browse/DERBY-5120?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13061570#comment-13061570
]
Rick Hillegas commented on DERBY-5120:
--------------------------------------
The following theory and example may help people reason about how to fix the
problem described by this JIRA.
-------------- THEORY ---------------------
The following discussion relies on these definitions and assumptions:
i) Invaliding events - These include "object dropped" and "object modified".
ii) "A -> B" - This is a dependency arc. It is shorthand for "A depends on
B". Invalidating events travel backward along the dependency arcs, allowing
each object to decide how to respond to the event. Possible responses include:
"raise an exception because RESTRICT semantics are violated" and "recompile me".
iii) Dependency Graph - This is a graph of all dependency arcs needed by Derby.
The nodes in this graph are the persistent objects plus PreparedStatements.
There is an arrow from A to B iff "A -> B".
iv) Transitivity - The Dependency Graph obeys the following rule:
if "A -> B" and "B -> C", then "A -> C"
v) SYSDEPENDS contains dependency arcs between persistent objects.
vi) Sufficient - SYSDEPENDS is said to be sufficient if it contains enough
dependency arcs to reconstruct the entire Dependency Graph. Note that
SYSDEPENDS is not the only input to constructing the Dependency Graph. Some
arcs are implicitly described by other catalogs. Transitivity can be used to
construct further arcs.
vii) Minimal - SYSDEPENDS is said to be minimal if it contains the smallest
number of arcs needed to reconstruct the entire Dependency Graph. For instance,
if SYSDEPENDS contains the arcs "A -> B" and "B -> C" then SYSDEPENDS does not
need to contain the "A -> C" arc because Derby can reconstruct that arc from
the Transitivity rule.
viii) Fuzzy - SYSDEPENDS is said to be fuzzy if it contains arcs that are not
in the Dependency Graph.
I would venture the following:
I) SYSDEPENDS should be Sufficient and not Fuzzy.
II) Even if SYSDEPENDS is Sufficient, Derby may have a bug which prevents it
from constructing the complete Dependency Graph. For instance, Derby may be
ignoring relevant information in other catalogs.
III) I do not believe that SYSDEPENDS is Minimal. When DDL creates new arcs in
the Dependency Graph, Derby does not recompute the contents of SYSDEPENDS just
to guarantee a Minimal representation.
------------- EXAMPLE ------------------
Let's apply this to a trigger example.
INSERTs into table T1 fire a trigger which INSERTs into table T2
This example gives rise to the following persistent objects:
Tables T1 and T2
Corresponding conglomerates C1 and C2
Trigger TR
Action statement A
The following would be a Minimal representation in SYSDEPENDS:
TR -> T1
A -> T2
Note that the following additional arcs do not need to be modelled in
SYSDEPENDS, but can be constructed by Derby from information in other catalogs:
T1 -> C1
C1 -> T1
T2 -> C2
C2 -> T2
TR -> A
A -> TR
Other arcs arise via the Transitive rule.
What we actually see in SYSDEPENDS is the following Sufficient, non-Minimal
representation:
TR -> T1
TR -> A (non-Minimal, could be constructed from SYSTRIGGERS)
A -> T1 (non-Minimal, could be constructed by Transitivity)
A -> T2
A -> C2 (non-Minimal, could be constructed by Transitivity)
Here is a script which shows this example:
connect 'jdbc:derby:memory:db;create=true';
create table t1( a int );
create table t2( a int );
create trigger trig after insert on t1 for each statement insert into t2( a )
values( 1 );
select * from sys.sysdepends order by dependentid, providerid;
select tablename, tableid from sys.systables where tablename like 'T%';
select t.tablename, c.conglomerateid
from sys.systables t, sys.sysconglomerates c
where tablename like 'T%'
and t.tableid = c.tableid;
select triggerid from sys.systriggers;
> Row from SYSDEPENDS gets deleted when a table has update triggers defined on
> it and an upate is made to the table
> -----------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-5120
> URL: https://issues.apache.org/jira/browse/DERBY-5120
> Project: Derby
> Issue Type: Task
> Components: SQL
> Affects Versions: 10.2.2.0, 10.8.1.2
> Reporter: Mamta A. Satoor
> Assignee: Mamta A. Satoor
>
> I have an ij script below which shows that the number of rows in SYSDEPENDS
> go down by 1 for the following test case after an update is made to a table
> with update triggers defined on it. Am not sure what kind of problems the
> missing dependnecy might cause.
> connect 'jdbc:derby:c:/dellater/db1;create=true';
> CREATE TABLE ATDC_13_TAB1(c11 int, c12 int);
> insert into ATDC_13_TAB1 values (1,11);
> create table ATDC_13_TAB2(c21 int, c22 int);
> insert into ATDC_13_TAB2 values (1,11);
> create table ATDC_13_TAB3(c31 int, c32 int);
> insert into ATDC_13_TAB3 values (1,11);
> create table ATDC_13_TAB1_backup(c11 int, c12 int);
> insert into ATDC_13_TAB1_backup values (1,11);
> create trigger ATDC_13_TAB1_trigger_1 after update
> on ATDC_13_TAB1 for each row mode db2sql
> INSERT INTO ATDC_13_TAB1_BACKUP(C11)
> SELECT C21 from ATDC_13_TAB2;
> create trigger ATDC_13_TAB1_trigger_2 after update
> on ATDC_13_TAB1 for each row mode db2sql
> INSERT INTO ATDC_13_TAB1_BACKUP
> SELECT C31, C32 from ATDC_13_TAB3;
> -- following shows 14 rows
> select * from sys.sysdepends;
> update ATDC_13_TAB1 set c12=11;
> -- following shows only 13 rows
> I tried this on 10.2 and 10.8 and saw the same behavior on both. It seems
> like the dependency that gets dropped is between the stored prepared
> statement and a table. Have not spent enough time to find out more details
> but I thought it is worth pointing out the behavior
> select * from sys.sysdepends;
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
