db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5120) Row from SYSDEPENDS gets deleted when a table has update triggers defined on it and an upate is made to the table
Date Thu, 07 Jul 2011 20:28:16 GMT

    [ 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

        

Mime
View raw message