[
https://issues.apache.org/jira/browse/DERBY-5120?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13061940#comment-13061940
]
Rick Hillegas commented on DERBY-5120:
--------------------------------------
Hi Dag,
I think that SYSDEPENDS exists in order to make it fast to reconstruct the
Dependency Graph. It doesn't need to contain arcs representing the relationship
between tables and conglomerates because those can be figured out quickly by
looking at other catalogs. But the arc backward from a triggering table to its
trigger is hard to reconstruct, so that arc is put into SYSDEPENDS.
The issue of bidirectional arcs (cycles) is interesting. I don't know if there
are any examples of this in SYSDEPENDS. However, the cycles should exist in the
Dependency Graph I think. The example of tables and conglomerates comes to mind:
T1 -> C1 models the fact that dropping the conglomerate (e.g. by bulk import)
should invalidate the table.
C1 -> T1 models the fact that dropping the table should also drop the
conglomerate.
I don't think that Derby systematically implements the Dependency Graph I have
described. My impression is that dependency management is a hodge-podge today.
Some of the relationships are managed by the dependency subsystem. Other
relationships are managed by little winks and nods in other parts of the SQL
interpreter. In my opinion this hodge-podge makes dependency management brittle
and hard to reason about.
Hi Mamta,
I may not be following your analysis. But it sounds to me as though the
following arc has vanished from SYSDEPENDS:
A -> triggeringTable
I am suggesting that this is a non-Minimal arc and doesn't need to be in
SYSDEPENDS. Derby should be able to construct this arc on the fly via
Transitivity from the following arcs:
A -> TR (this is known from SYSTRIGGERS)
TR -> triggeringTable (this is still in SYSDEPENDS if I understand correctly)
Thanks,
-Rick
> 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
