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] [Updated] (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 Tue, 12 Jul 2011 17:09:01 GMT

     [ https://issues.apache.org/jira/browse/DERBY-5120?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Mamta A. Satoor updated DERBY-5120:

    Attachment: DERBY5120_patch2_stat.txt

I am attaching another patch which has two test problems resolved which I saw seeing with
the earlier patch. The two tests that were failing with patch 1 were 1)AlterTableTest,testDropColumn
This was failing because  the number of rows in sysdepends were not as expected by the test.
I found the problem was that AlterTableConstantAction does a drop and recreate trigger and
it was adding the dependency between trigger action sps and trigger table. It does not need
to add that dependency anymore because it is taken care of by SPSDescriptor. Removing the
dependency add code from AlterTableConstantAction resolved the problem.
2)triggerGeneral.sql was failing because a trigger action was using select * from trigger
table to insert into another table. Later an alter table was performed to add one more column
to the trigger table. With missing dependency before, trigger action was not getting regenerated
and hence trigger didn't detect that it is not valid anymore. After I have made the changes
for the dependency, trigger action gets invalidated when a new column is added to trigger
table and this will cause the trigger to fail.I have fixed the test to not do select * in
trigger action so add column will not impact it. The test in triggerGeneral has been written
for a specific purpose and that is why I didn't want the test to fail with select *. I will
add another test which will catch a case like insert into select * in trigger action and it
getting invalid with add column.

The only tasks left on this jira is to add the test I mentioned. Additionally, I plan to add
an upgrade test.

If anyone has any feedback, please let me know.

> 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:,
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>         Attachments: DERBY5120_patch1_diff.txt, DERBY5120_patch1_stat.txt, DERBY5120_patch2_diff.txt,
> 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


View raw message