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-5681) When a foreign key constraint on a table is dropped, the associated statistics row for the conglomerate is not removed
Date Fri, 18 May 2012 22:41:09 GMT

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

Mamta A. Satoor updated DERBY-5681:
-----------------------------------

    Fix Version/s: 10.3.3.1
    
> When a foreign key constraint on a table is dropped, the associated statistics row for
the conglomerate is not removed
> ----------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5681
>                 URL: https://issues.apache.org/jira/browse/DERBY-5681
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL, Store
>    Affects Versions: 10.8.2.2
>            Reporter: Brett Bergquist
>            Assignee: Mamta A. Satoor
>             Fix For: 10.3.3.1, 10.4.2.1, 10.5.3.2, 10.6.2.3, 10.7.1.4, 10.8.2.3, 10.9.0.0
>
>         Attachments: DERBY5681_patch1_diff.txt, DERBY5681_patch2_diff.txt
>
>
> If you drop the foreign key constraint for a table, the statistics row does not get removed.
  This affects the indexStat daemon because it now finds these statistics row which always
appear as out of date, causing an update to be scheduled.
> Here is how to get it to happen:
> set schema app;
> CREATE TABLE TEST_TAB_1
> (
>     ID INTEGER PRIMARY KEY NOT NULL
> );
> CREATE TABLE TEST_TAB_2
> (
>    ID INTEGER PRIMARY KEY NOT NULL
> );
> ALTER TABLE TEST_TAB_2
> ADD CONSTRAINT TEST_TAB_2_FK_1
> FOREIGN KEY (ID) REFERENCES TEST_TAB_1(ID);
> insert into app.TEST_TAB_1 values (1);
> insert into test_tab_2 values(1);
> call syscs_util.syscs_update_statistics('APP', 'TEST_TAB_2', null);
> select
> c.TABLEID,
> c.CONGLOMERATENUMBER,
> c.CONGLOMERATENAME,
> c.ISINDEX,
> c.ISCONSTRAINT,
> c.CONGLOMERATEID,
> t.TABLEID,
> t.TABLENAME,
> t.TABLETYPE,
> s.STATID,
> s.REFERENCEID,
> s.TABLEID,
> s.CREATIONTIMESTAMP,
> s.TYPE,
> s.VALID,
> s.COLCOUNT,
> CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
> from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID = t.TABLEID join sys.SYSSTATISTICS
s on s.TABLEID = t.TABLEID
> where t.TABLENAME = 'TEST_TAB_2' and c.ISINDEX = false;
> -- At this point there are two statistic rows
> TABLEID CONGLOMERATENUMBER      CONGLOMERATENAME        ISINDEX ISCONSTRAINT    CONGLOMERATEID
 TABLEID TABLENAME       TABLETYPE       STATID  REFERENCEID     TABLEID CREATIONTIMESTAMP
      TYPE    VALID   COLCOUNT        STATISTICS
> 84490209-0136-6999-c1b4-000065089f97    348432  84490209-0136-6999-c1b4-000065089f97
   false   false   cccb420a-0136-6999-c1b4-000065089f97    84490209-0136-6999-c1b4-000065089f97
   TEST_TAB_2      T       edbc8255-0136-6999-c1b4-000065089f97    55410238-0136-6999-c1b4-000065089f97
   84490209-0136-6999-c1b4-000065089f97    2012-03-31 17:36:49.629 I       true    1     
 numunique= 1 numrows= 1
> 84490209-0136-6999-c1b4-000065089f97    348432  84490209-0136-6999-c1b4-000065089f97
   false   false   cccb420a-0136-6999-c1b4-000065089f97    84490209-0136-6999-c1b4-000065089f97
   TEST_TAB_2      T       05278254-0136-6999-c1b4-000065089f97    63454207-0136-6999-c1b4-000065089f97
   84490209-0136-6999-c1b4-000065089f97    2012-03-31 17:36:49.628 I       true    1     
 numunique= 1 numrows= 1
> -- Now drop the constraint
> alter table TEST_TAB_2
> drop constraint TEST_TAB_2_FK_1;
> select
> c.TABLEID,
> c.CONGLOMERATENUMBER,
> c.CONGLOMERATENAME,
> c.ISINDEX,
> c.ISCONSTRAINT,
> c.CONGLOMERATEID,
> t.TABLEID,
> t.TABLENAME,
> t.TABLETYPE,
> s.STATID,
> s.REFERENCEID,
> s.TABLEID,
> s.CREATIONTIMESTAMP,
> s.TYPE,
> s.VALID,
> s.COLCOUNT,
> CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
> from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID = t.TABLEID join sys.SYSSTATISTICS
s on s.TABLEID = t.TABLEID
> where t.TABLENAME = 'TEST_TAB_2' and c.ISINDEX = false;
> -- There are still two statistic rows
> TABLEID CONGLOMERATENUMBER      CONGLOMERATENAME        ISINDEX ISCONSTRAINT    CONGLOMERATEID
 TABLEID TABLENAME       TABLETYPE       STATID  REFERENCEID     TABLEID CREATIONTIMESTAMP
      TYPE    VALID   COLCOUNT        STATISTICS
> 84490209-0136-6999-c1b4-000065089f97    348432  84490209-0136-6999-c1b4-000065089f97
   false   false   cccb420a-0136-6999-c1b4-000065089f97    84490209-0136-6999-c1b4-000065089f97
   TEST_TAB_2      T       edbc8255-0136-6999-c1b4-000065089f97    55410238-0136-6999-c1b4-000065089f97
   84490209-0136-6999-c1b4-000065089f97    2012-03-31 17:36:49.629 I       true    1     
 numunique= 1 numrows= 1
> 84490209-0136-6999-c1b4-000065089f97    348432  84490209-0136-6999-c1b4-000065089f97
   false   false   cccb420a-0136-6999-c1b4-000065089f97    84490209-0136-6999-c1b4-000065089f97
   TEST_TAB_2      T       05278254-0136-6999-c1b4-000065089f97    63454207-0136-6999-c1b4-000065089f97
   84490209-0136-6999-c1b4-000065089f97    2012-03-31 17:36:49.628 I       true    1     
 numunique= 1 numrows= 1
> -- Add another row
> insert into app.TEST_TAB_1 values (2);
> insert into test_tab_2 values(2);
> -- Update the statistics
> call syscs_util.syscs_update_statistics('APP', 'TEST_TAB_2', null);
> select
> c.TABLEID,
> c.CONGLOMERATENUMBER,
> c.CONGLOMERATENAME,
> c.ISINDEX,
> c.ISCONSTRAINT,
> c.CONGLOMERATEID,
> t.TABLEID,
> t.TABLENAME,
> t.TABLETYPE,
> s.STATID,
> s.REFERENCEID,
> s.TABLEID,
> s.CREATIONTIMESTAMP,
> s.TYPE,
> s.VALID,
> s.COLCOUNT,
> CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
> from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID = t.TABLEID join sys.SYSSTATISTICS
s on s.TABLEID = t.TABLEID
> where t.TABLENAME = 'TEST_TAB_2' and c.ISINDEX = false;
> -- There are still two rows but now one show 1 row and one shows 2 rows
> TABLEID CONGLOMERATENUMBER      CONGLOMERATENAME        ISINDEX ISCONSTRAINT    CONGLOMERATEID
 TABLEID TABLENAME       TABLETYPE       STATID  REFERENCEID     TABLEID CREATIONTIMESTAMP
      TYPE    VALID   COLCOUNT        STATISTICS
> 84490209-0136-6999-c1b4-000065089f97    348432  84490209-0136-6999-c1b4-000065089f97
   false   false   cccb420a-0136-6999-c1b4-000065089f97    84490209-0136-6999-c1b4-000065089f97
   TEST_TAB_2      T       edbc8255-0136-6999-c1b4-000065089f97    55410238-0136-6999-c1b4-000065089f97
   84490209-0136-6999-c1b4-000065089f97    2012-03-31 17:36:49.629 I       true    1     
 numunique= 1 numrows= 1
> 84490209-0136-6999-c1b4-000065089f97    348432  84490209-0136-6999-c1b4-000065089f97
   false   false   cccb420a-0136-6999-c1b4-000065089f97    84490209-0136-6999-c1b4-000065089f97
   TEST_TAB_2      T       18438274-0136-6999-c1b4-000065089f97    63454207-0136-6999-c1b4-000065089f97
   84490209-0136-6999-c1b4-000065089f97    2012-03-31 17:41:19.164 I       true    1     
 numunique= 2 numrows= 2
> -- Add the constraint back on
> ALTER TABLE TEST_TAB_2
> ADD CONSTRAINT TEST_TAB_2_FK_1
> FOREIGN KEY (ID) REFERENCES TEST_TAB_1(ID);
> -- Insert another row
> insert into app.TEST_TAB_1 values (3);
> insert into test_tab_2 values(3);
> -- Update the statistics
> call syscs_util.syscs_update_statistics('APP', 'TEST_TAB_2', null);
> select
> c.TABLEID,
> c.CONGLOMERATENUMBER,
> c.CONGLOMERATENAME,
> c.ISINDEX,
> c.ISCONSTRAINT,
> c.CONGLOMERATEID,
> t.TABLEID,
> t.TABLENAME,
> t.TABLETYPE,
> s.STATID,
> s.REFERENCEID,
> s.TABLEID,
> s.CREATIONTIMESTAMP,
> s.TYPE,
> s.VALID,
> s.COLCOUNT,
> CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
> from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID = t.TABLEID join sys.SYSSTATISTICS
s on s.TABLEID = t.TABLEID
> where t.TABLENAME = 'TEST_TAB_2' and c.ISINDEX = false;
> -- Now there are 3 rows
> TABLEID CONGLOMERATENUMBER      CONGLOMERATENAME        ISINDEX ISCONSTRAINT    CONGLOMERATEID
 TABLEID TABLENAME       TABLETYPE       STATID  REFERENCEID     TABLEID CREATIONTIMESTAMP
      TYPE    VALID   COLCOUNT        STATISTICS
> 84490209-0136-6999-c1b4-000065089f97    348432  84490209-0136-6999-c1b4-000065089f97
   false   false   cccb420a-0136-6999-c1b4-000065089f97    84490209-0136-6999-c1b4-000065089f97
   TEST_TAB_2      T       edbc8255-0136-6999-c1b4-000065089f97    55410238-0136-6999-c1b4-000065089f97
   84490209-0136-6999-c1b4-000065089f97    2012-03-31 17:36:49.629 I       true    1     
 numunique= 1 numrows= 1
> 84490209-0136-6999-c1b4-000065089f97    348432  84490209-0136-6999-c1b4-000065089f97
   false   false   cccb420a-0136-6999-c1b4-000065089f97    84490209-0136-6999-c1b4-000065089f97
   TEST_TAB_2      T       45eb02e8-0136-6999-c1b4-000065089f97    63454207-0136-6999-c1b4-000065089f97
   84490209-0136-6999-c1b4-000065089f97    2012-03-31 17:46:00.211 I       true    1     
 numunique= 3 numrows= 3
> 84490209-0136-6999-c1b4-000065089f97    348432  84490209-0136-6999-c1b4-000065089f97
   false   false   cccb420a-0136-6999-c1b4-000065089f97    84490209-0136-6999-c1b4-000065089f97
   TEST_TAB_2      T       0ea502e9-0136-6999-c1b4-000065089f97    7ab90278-0136-6999-c1b4-000065089f97
   84490209-0136-6999-c1b4-000065089f97    2012-03-31 17:46:00.212 I       true    1     
 numunique= 3 numrows= 3
> Note that dropping that recreating the constraint or compressing the table does not fix
the problem.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message