Derby
  1. Derby
  2. DERBY-5681

When a foreign key constraint on a table is dropped, the associated statistics row for the conglomerate is not removed

    Details

    • Issue & fix info:
      Patch Available, Repro attached

      Description

      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.

      1. DERBY5681_patch1_diff.txt
        11 kB
        Mamta A. Satoor
      2. DERBY5681_patch2_diff.txt
        7 kB
        Mamta A. Satoor
      3. derby-5681-3a-test.diff
        3 kB
        Kristian Waagan

        Issue Links

          Activity

          Hide
          Brett Bergquist added a comment -

          This is one way in which the DERBY-5680 will find statistics rows that don't make sense and try to update over and over.

          Show
          Brett Bergquist added a comment - This is one way in which the DERBY-5680 will find statistics rows that don't make sense and try to update over and over.
          Hide
          Mike Matrigali added a comment -

          just changing the component, the problem is most likely in the drop constraint code in the sql layer of the system. The storage layer does not
          know anything about the sysstatistics catalog.

          Show
          Mike Matrigali added a comment - just changing the component, the problem is most likely in the drop constraint code in the sql layer of the system. The storage layer does not know anything about the sysstatistics catalog.
          Hide
          Mamta A. Satoor added a comment -

          I have added some test cases which are attached as a patch to this jira(DERBY5681_patch1_diff.txt)

          I am seeing left over statistics row for the test case provided by Brett in this jira in my junit test as well but Unique key on nullable and non-nullable column and primary key constraints are working fine ie no left over statistics after those constraints are dropped.

          Show
          Mamta A. Satoor added a comment - I have added some test cases which are attached as a patch to this jira(DERBY5681_patch1_diff.txt) I am seeing left over statistics row for the test case provided by Brett in this jira in my junit test as well but Unique key on nullable and non-nullable column and primary key constraints are working fine ie no left over statistics after those constraints are dropped.
          Hide
          Mamta A. Satoor added a comment -

          I am debugging this jira. Not finished yet but it may be related to the changes that went in for DERBY-3299 (Uniqueness violation error (23505) occurs after dropping a PK constraint if there exists a foreign key on the same columns). Will debug further and post the finidings. I think this is yet another case where same backing index is being used by multiple constraints.

          Show
          Mamta A. Satoor added a comment - I am debugging this jira. Not finished yet but it may be related to the changes that went in for DERBY-3299 (Uniqueness violation error (23505) occurs after dropping a PK constraint if there exists a foreign key on the same columns). Will debug further and post the finidings. I think this is yet another case where same backing index is being used by multiple constraints.
          Hide
          Mamta A. Satoor added a comment -

          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.

          Show
          Mamta A. Satoor added a comment - 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.
          Hide
          Mamta A. Satoor added a comment -

          While going through the code, I found following comment in CreateIndexConstantAction about what will determine if a backing index will be shared by multiple constraints.

          /* The conditions which allow an index to share an existing

          • conglomerate are as follows:
            *
          • 1. the set of columns (both key and include columns) and their
          • order in the index is the same as that of an existing index AND
            *
          • 2. the ordering attributes are the same AND
            *
          • 3. one of the following is true:
          • a) the existing index is unique, OR
          • b) the existing index is non-unique with uniqueWhenNotNulls
          • set to TRUE and the index being created is non-unique, OR
          • c) both the existing index and the one being created are
          • non-unique and have uniqueWithDuplicateNulls set to FALSE.
            */
          Show
          Mamta A. Satoor added a comment - While going through the code, I found following comment in CreateIndexConstantAction about what will determine if a backing index will be shared by multiple constraints. /* The conditions which allow an index to share an existing conglomerate are as follows: * 1. the set of columns (both key and include columns) and their order in the index is the same as that of an existing index AND * 2. the ordering attributes are the same AND * 3. one of the following is true: a) the existing index is unique, OR b) the existing index is non-unique with uniqueWhenNotNulls set to TRUE and the index being created is non-unique, OR c) both the existing index and the one being created are non-unique and have uniqueWithDuplicateNulls set to FALSE. */
          Hide
          Mamta A. Satoor added a comment -

          Attaching a patch which fixes the problem associated with this jira. Basically, when two constraints share the same backing index, we conditionally dropped the statistics. Instead, this fix will make sure that the statistics are always dropped even if the underneath backing index is still valid(and hence won't be dropped and recreated) for other constraints. I ran derbyall and junit suite and they both ran fine with no errors. Please let me know if there is any feedback on the patch. I have also added few tests for the issue.

          Show
          Mamta A. Satoor added a comment - Attaching a patch which fixes the problem associated with this jira. Basically, when two constraints share the same backing index, we conditionally dropped the statistics. Instead, this fix will make sure that the statistics are always dropped even if the underneath backing index is still valid(and hence won't be dropped and recreated) for other constraints. I ran derbyall and junit suite and they both ran fine with no errors. Please let me know if there is any feedback on the patch. I have also added few tests for the issue.
          Hide
          Mamta A. Satoor added a comment -

          If we go with patch DERBY5681_patch2_diff.txt, then it will be fine to backport it to earlier releases.

          Show
          Mamta A. Satoor added a comment - If we go with patch DERBY5681_patch2_diff.txt, then it will be fine to backport it to earlier releases.
          Hide
          Mamta A. Satoor added a comment - - edited

          Have backported the changes to 10.4 but in trunk , for this jira, we added some tests which use upgrade statistics procedure but since that procedure doesn't exist in 10.4 and before, the new tests for this jira from trunk couldn't be backported to 10.4 without changes. In 10.4, I added those tests to a new class, namely Derby5681Test.java and these tests donot use update statisitcs procedure. But, because of the absence of update statisitcs procedure and DERBY-5702, we can't quite have a statistics row for a constraint which shares a backing index in 10.4 and before. The test in 10.4 will just have a test to drop constraint(that shares a backing index with another constraint) and show that it doesn't break anything. Once DERBY-5702 is fixed and backported to 10.4 and before, we will have a statistics row for a constraint which shares a backing index and we will be able to show that as a fix of this jira, that statistics row will get dropped when the constraint is dropped.

          Show
          Mamta A. Satoor added a comment - - edited Have backported the changes to 10.4 but in trunk , for this jira, we added some tests which use upgrade statistics procedure but since that procedure doesn't exist in 10.4 and before, the new tests for this jira from trunk couldn't be backported to 10.4 without changes. In 10.4, I added those tests to a new class, namely Derby5681Test.java and these tests donot use update statisitcs procedure. But, because of the absence of update statisitcs procedure and DERBY-5702 , we can't quite have a statistics row for a constraint which shares a backing index in 10.4 and before. The test in 10.4 will just have a test to drop constraint(that shares a backing index with another constraint) and show that it doesn't break anything. Once DERBY-5702 is fixed and backported to 10.4 and before, we will have a statistics row for a constraint which shares a backing index and we will be able to show that as a fix of this jira, that statistics row will get dropped when the constraint is dropped.
          Hide
          Kristian Waagan added a comment - - edited

          Attaching patch 3a, which makes the test testIndexAndColumnNamedStatistics less sensitive to statistics created by other tests.

          Committed to trunk with revision 1341002.
          Cleanup code in testDisposableStatsEagerness was added as part of addressing DERBY-5774.

          Show
          Kristian Waagan added a comment - - edited Attaching patch 3a, which makes the test testIndexAndColumnNamedStatistics less sensitive to statistics created by other tests. Committed to trunk with revision 1341002. Cleanup code in testDisposableStatsEagerness was added as part of addressing DERBY-5774 .
          Hide
          Mamta A. Satoor added a comment -

          Kristian, thanks for making the test more independent of other tests.

          Show
          Mamta A. Satoor added a comment - Kristian, thanks for making the test more independent of other tests.
          Hide
          ASF subversion and git services added a comment -

          Commit 1497868 from Mamta A. Satoor
          [ 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.

          Show
          ASF subversion and git services added a comment - Commit 1497868 from Mamta A. Satoor [ 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.
          Hide
          ASF subversion and git services added a comment -

          Commit 1497868 from Mamta A. Satoor
          [ 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.

          Show
          ASF subversion and git services added a comment - Commit 1497868 from Mamta A. Satoor [ 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.

            People

            • Assignee:
              Mamta A. Satoor
              Reporter:
              Brett Bergquist
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development