db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (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 Wed, 06 Jul 2011 17:58:16 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5120?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13060723#comment-13060723
] 

Mamta A. Satoor commented on DERBY-5120:
----------------------------------------

Dag, I tend to agree with your comments. The cooment in the code does not seem to agree with
the kinds of dependencies added by first 2 addDepency statements. I tried looking through
the history of this code and it appears that we have this code from the very beginning.
 
But, I debugged further to find out why one of the rows added during CREATE TRIGGER time gets
dropped later on. The issue seems to be around trigger action sps getting invalidated and
later getting recompiled when it gets fired next time around. Going back to the example test
case provided earlier

connect 'jdbc:derby:db1_2;create=true'; 
CREATE TABLE ATDC_13_TAB1(c11 int, c12 int); 
insert into ATDC_13_TAB1 values (1,11); 

create trigger ATDC_13_TAB1_trigger_1 after update 
         on ATDC_13_TAB1 for each row mode db2sql 
          values(1); 

create trigger ATDC_13_TAB1_trigger_2 after update 
         on ATDC_13_TAB1 for each row mode db2sql 
         values(1,2); 

-- following shows 6 rows 
select * from sys.sysdepends; 
update ATDC_13_TAB1 set c12=11; 
-- following shows only 5 rows 
select * from sys.sysdepends; 

When the 2nd trigger(ATDC_13_TAB1_trigger_2) gets created, CreateTriggerConstantAction sends
CREATE_TRIGGER invalidation to the trigger table as shown below(This sends invalidation code
to the first trigger ATDC_13_TAB1_trigger_1)
		/*
		** Send an invalidate on the table from which
		** the triggering event emanates.  This it
		** to make sure that DML statements on this table
		** will be recompiled.  Do this before we create
		** our trigger spses lest we invalidate them just
		** after creating them.
		*/
		dm.invalidateFor(triggerTable, DependencyManager.CREATE_TRIGGER, lcc);

Later when, "update ATDC_13_TAB1 set c12=11" statement is executed, it fires both the triggers.
The first trigger
(ATDC_13_TAB1_trigger_1) during it's execution finds that it is invalid. During recompilation,
SPSDescriptor.compileStatement 
removes the existing dependencies recorded in SYSDEPENDS table for the trigger action sps
as shown below.This step ends up
removing the dependency recorded between the trigger action sps and trigger table(added by
CreateTriggerConstantAction at the time trigger creation) and that is where we can one row
short.
	/*
	** Clear out all the dependencies that exist
	** before we recreate them so we don't grow
	** SYS.SYSDEPENDS forever.
	*/
	dm.clearDependencies(lcc, this, tc);
After clearing out existing dependencies for invalid trigger action sps, the trigger action
sps regeneration process adds the dependencies that it finds during this recompile SPSDescriptor.compileStatement()
	/*
	** Copy over all the dependencies to me
	*/
	dm.copyDependencies(preparedStatement, 	// from
		this, 	// to
		false,	// persistent only
		cm,
		tc);
But the dependency between trigger action SPSD and trigger table never gets added back during
the recompilation and that is where we loose a row from SYSDEPENDS.

> 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