From derby-dev-return-88942-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Mon Jul 4 01:27:47 2011 Return-Path: X-Original-To: apmail-db-derby-dev-archive@www.apache.org Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id DEBE0460C for ; Mon, 4 Jul 2011 01:27:47 +0000 (UTC) Received: (qmail 85604 invoked by uid 500); 4 Jul 2011 01:27:47 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 85540 invoked by uid 500); 4 Jul 2011 01:27:46 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 85533 invoked by uid 99); 4 Jul 2011 01:27:46 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 04 Jul 2011 01:27:46 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED,T_RP_MATCHES_RCVD X-Spam-Check-By: apache.org Received: from [140.211.11.116] (HELO hel.zones.apache.org) (140.211.11.116) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 04 Jul 2011 01:27:43 +0000 Received: from hel.zones.apache.org (hel.zones.apache.org [140.211.11.116]) by hel.zones.apache.org (Postfix) with ESMTP id E044B44251E for ; Mon, 4 Jul 2011 01:27:21 +0000 (UTC) Date: Mon, 4 Jul 2011 01:27:21 +0000 (UTC) From: "Mamta A. Satoor (JIRA)" To: derby-dev@db.apache.org Message-ID: <686388073.1270.1309742841915.JavaMail.tomcat@hel.zones.apache.org> In-Reply-To: <1482206918.9090.1299703379435.JavaMail.tomcat@hel.zones.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 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/DERBY-5120?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13059308#comment-13059308 ] Mamta A. Satoor commented on DERBY-5120: ---------------------------------------- A typical CREATE TRIGGER goes through following steps as far as adding/deleting rows from SYSDEPENDS tale 1)Any time a trigger is created, CreateTriggerConstantAction.executeConstantAction() sends CREATE_TRIGGER invalidation to the trigger table as shown below(The list of objects getting invalidated will include existing triggers defined on the trigger table). /* ** 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); 2)Next, CreateTriggerConstantAction.executeConstantAction() does the trigger action sps generation /* ** Create the trigger action */ actionspsd = createSPS(lcc, ddg, dd, tc, tmpTriggerId, triggerSd, actionSPSId, spsCompSchemaId, actionText, false, triggerTable); 3) During trigger action sps generation, SPSDescriptor.compileStatement removes the existing dependencies of trigger action sps in sysdepends as shown below (for a trigger getting created the first time, there will be no SPS dependencies for the trigger action SPS. The same code is called when a trigger is found invalid, in that case, there will be existing trigger action SPS dependencies which will get dropped here) /* ** Clear out all the dependencies that exist ** before we recreate them so we don't grow ** SYS.SYSDEPENDS forever. */ dm.clearDependencies(lcc, this, tc); 4)After clearing out existing dependencies, it adds the dependencies that it finds during this compile SPSDescriptor.compileStatement() /* ** Copy over all the dependencies to me */ dm.copyDependencies(preparedStatement, // from this, // to false, // persistent only cm, tc); 5)After finishing with trigger action SPS generation, CreateTriggerConstantAction.executeConstantAction adds the depdencies for the trigger descriptor on trigger action sps and on trigger table. Additionally, it adds depedency on trigger action sps on trigger table(this is the dependency which later gets dropped when a compile of trigger action sps had cleared existing trigger action sps dependencies before regenerating the trigger action sps. The trigger action sps regeneration does not add the dependency between trigger action sps and trigger table dm.addDependency(triggerd, actionspsd, lcc.getContextManager()); dm.addDependency(triggerd, triggerTable, lcc.getContextManager()); dm.addDependency(actionspsd, triggerTable, lcc.getContextManager()); Following the steps above (to find what rows get added into SYSDEPENDS)for the first trigger in the simpler example that I posted on 01/Jul/11 create trigger ATDC_13_TAB1_trigger_1 after update on ATDC_13_TAB1 for each row mode db2sql values(1); Step 4) will not find any dependencies for trigger action values(1); Step 5) will add three rows into SYSDEPENDS, namely a)dependency between triiger descriptor for ATDC_13_TAB1_trigger_1 and triiger action SPS b)dependency between trigger descriptor for ATDC_13_TAB1_trigger_1 and triiger table ATDC_13_TAB1 3)dependency between triiger action SPS and triiger table ATDC_13_TAB1 This is how, we end up with three rows for the first trigger ATDC_13_TAB1_trigger_1 When the 2nd trigger(ATDC_13_TAB1_trigger_2) is created, it also results into adding 3 rows into SYSDEPENDS but additionally in step 1), it invalidates the existing trigger ATDC_13_TAB1_trigger_1 > 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