[
https://issues.apache.org/jira/browse/DERBY-5681?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13256838#comment-13256838
]
Mamta A. Satoor commented on DERBY-5681:
----------------------------------------
I debugged Derby code for this jira and found the problem to be in
iapi.sql.dictionary.ConglomerateDescriptor:drop() method
One of the checks being made in this method is as follows
if (congDescs.length == 1)
dropConglom = true;
else
{
In case of this jira, we have multiple constraints sharing the same backing
index and hence we go to the else code of the above if else code. In this code,
based on some conditions, we check if we need to drop and recreate the backing
index. I need to study this code further to see how we decide whether we should
recreate a new backing index. But it appears, in our specific case, we do not
drop and recreate the backing index and that might be the cause behind
statistics not getting dropped when foreign constraint is dropped. I will post
more as I find more info.
> 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
> Attachments: DERBY5681_patch1_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
