[
https://issues.apache.org/jira/browse/DERBY-4182?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mike Matrigali updated DERBY-4182:
----------------------------------
Attachment: DERBY4182.diff
posting preliminary patch file: derby4182.diff
Still need to run a set of tests and add a new test to the suite, but this
seems to fix the posted repro.
I believe this has always been a problem, and the behavior was
incorrectly thought to be DERBY-691.
The problem is not with compress table. When compress table runs it finds the
deleted rows and reclaims them along with the 1st overflow page that that point
to, so 2 pages per failed unique constraint insert in the sample program. The
problem is that the abort results in all of the other links of the overflow
pages being broken and thus compress table can't find them.
The fix is to use some existing functionality in the store to process the
broken link pages during the abort insert as purges rather than deletes which
will move them to free pages. This is what is currently done when updates
result in new overflow chains are aborted.
> SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE does not reclaim space lost from an
> aborted insert
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-4182
> URL: https://issues.apache.org/jira/browse/DERBY-4182
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.3.3.0, 10.4.2.0, 10.5.1.1, 10.6.0.0
> Reporter: Kathey Marsden
> Assignee: Mike Matrigali
> Attachments: DERBY4182.diff, TestInPlaceCompressWithPKViolation.java
>
>
> 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
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
