[
https://issues.apache.org/jira/browse/DERBY-5323?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-5323:
-----------------------------------
Urgency: Normal
Labels: derby_triage10_10 (was: )
> 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.1.0
> Reporter: Mamta A. Satoor
> Labels: derby_triage10_10
>
> 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.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
