SYSDEPENDS may be keeping redundant dependency info. Specific information for
trigger case in this jira but there might be other cases as well
----------------------------------------------------------------------------------------------------------------------------------------------
Key: DERBY-5323
URL: https://issues.apache.org/jira/browse/DERBY-5323
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.9.0.0
Reporter: Mamta A. Satoor
During DERBY-5120 investigation, Rick had suggestions on improving how Derby
keeps dependency in it's system table. Refer to that jira for more information
but information posted by Rick specifically for trigger is copied here
-------------- 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;
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
