Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4182

SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE does not reclaim space lost from an aborted insert

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.3.1, 10.2.2.0, 10.3.3.0, 10.4.2.0, 10.5.1.1, 10.6.1.0
    • Fix Version/s: 10.3.3.1, 10.4.2.1, 10.5.2.0, 10.6.1.0
    • Component/s: Store
    • Labels:
      None
    • Issue & fix info:
      High Value Fix

      Description

      Because of DERBY-691 an aborted insert (for example due to a constraint violation or rollback) can cause space to not be reclaimed. SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE should reclaim this space. Even if it cannot return the space to the operating system it should show up as free pages after calling the procedure, but it currently does not. SYSCS_UTIL.SYSCS_COMPRESS_TABLE works properly and returns the space to the operating system.

      See the attached program TestInPlaceCompressWithPKViolation.java to reproduce this problem. The program attempts 10 3MB inserts. One is successful and 9 fail. SpaceTable queries show only 18 pages are marked as free after inplace compress and none returned to the os.

      The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or un
      ique index identified by 'SQL090422155557650' defined on 'TAB'.
      The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or un
      ique index identified by 'SQL090422155557650' defined on 'TAB'.
      The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or un
      ique index identified by 'SQL090422155557650' defined on 'TAB'.
      The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or un
      ique index identified by 'SQL090422155557650' defined on 'TAB'.
      The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or un
      ique index identified by 'SQL090422155557650' defined on 'TAB'.
      The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or un
      ique index identified by 'SQL090422155557650' defined on 'TAB'.
      The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or un
      ique index identified by 'SQL090422155557650' defined on 'TAB'.
      The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or un
      ique index identified by 'SQL090422155557650' defined on 'TAB'.
      The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or un
      ique index identified by 'SQL090422155557650' defined on 'TAB'.
      *****spaceTable before SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP','TAB',1,1,1)
      CONGLOMERATENAME

      ISIND& NUMALLOCATEDPAGES NUMFREEPAGES NUMUNFILLEDPAGES PAGESIZE ESTIMSPACESAVING
      ------------------------------------------------------------------------------------------------------------------------
      ---------------------------------------------------------------------------------------------------------------
      TAB
      0 931 0 2 32768 0
      *****spaceTable after SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP','TAB',1,1,1)
      CONGLOMERATENAME
      ISIND& NUMALLOCATEDPAGES NUMFREEPAGES NUMUNFILLEDPAGES PAGESIZE ESTIMSPACESAVING
      ------------------------------------------------------------------------------------------------------------------------
      ---------------------------------------------------------------------------------------------------------------
      TAB
      0 913 18 1 32768 589824
      *****spaceTable after SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP','TAB',1)
      CONGLOMERATENAME
      ISIND& NUMALLOCATEDPAGES NUMFREEPAGES NUMUNFILLEDPAGES PAGESIZE ESTIMSPACESAVING
      ------------------------------------------------------------------------------------------------------------------------
      ---------------------------------------------------------------------------------------------------------------
      TAB
      0 95 0 1 32768 0

        Attachments

        1. DERBY4182.diff
          7 kB
          Mike Matrigali
        2. TestInPlaceCompressWithPKViolation.java
          2 kB
          Katherine Marsden

          Issue Links

            Activity

              People

              • Assignee:
                mikem Mike Matrigali
                Reporter:
                kmarsden Katherine Marsden
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: