[
https://issues.apache.org/jira/browse/DERBY-5681?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13695631#comment-13695631
]
ASF subversion and git services commented on DERBY-5681:
--------------------------------------------------------
Commit 1497868 from [~mamtas]
[ https://svn.apache.org/r1497868 ]
DERBY-5680( indexStat daemon processing tables over and over even when there
are no changes in the tables )
Backporting the 3 commits that went in for DERBY-5680 to 10.8. The 3 commits
were 1340549, 1341622, 1341629. The first two commits were easy to backport
using svn merge command but the third commit 1341629 ran into conflicts. For
that backport, hand made the changes since there were not too many changes.
The changes for this jira has added a new property
derby.storage.indexStats.debug.keepDisposableStats. The intention of the
property is that if the property is set to true, we do not delete the
orphaned/disposable stats. If the property is set to false, the
orphaned/disposable stats will get dropped by the index stats daemon. Currently
known reasons for orphaned/disposable stats are
1)DERBY-5681(When a foreign key constraint on a table is dropped, the
associated statistics row for the conglomerate is not removed). Fix for this
has been backported all the way to 10.3
2)DERBY-3790(Investigate if request for update statistics can be skipped for
certain kind of indexes, one instance may be unique indexes based on one
column.) Fix for this is in 10.9 and higher
A junit test was added for this new property but it went in as part of
DERBY-3790. The name of the junit test is
store.KeepDisposableStatsPropertyTest. Had to make changes to this test to
backport it to 10.8 but without the fix for DEBRY-3790 and with the absence of
drop statistics procedure, the test really does not make much sense for 10.8
codeline. The test uses drop statistics procedure and it is mainly testing
DERBY-3790 to make sure that the orphaned stats are being deleted or left
behind based on whether the property is set to true or false. But since we do
not have drop statistics procedure and we do not have DERBY-3790 fixed in 10.8,
we can't really meaningfully run the KeepDisposableStatsPropertyTest in 10.8.
In any case, I have changed the test so that atleast it will not fail in 10.8
but it is not able to truly test the property. May be we can test this property
through upgrade suite where we will create orphaned stats because of DERBY-5681
on older releases and we will find that when the property is set to true, even
after upgrade, we will have orphaned stats but when property is set to false,
after upgrade, orphaned stats are deleted.
> 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.3.0, 10.9.1.0
>
> Attachments: derby-5681-3a-test.diff, 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
For more information on JIRA, see: http://www.atlassian.com/software/jira
