[jira] [Updated] (DERBY-5323) SYSDEPENDS may be keeping redundant dependency info. Specific information for trigger case in this jira but there might be other cases as well

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

     [ 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