You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik (JIRA)" <ji...@apache.org> on 2013/12/02 15:23:35 UTC
[jira] [Comment Edited] (DERBY-532) Support deferrable constraints
[ https://issues.apache.org/jira/browse/DERBY-532?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13835590#comment-13835590 ]
Dag H. Wanvik edited comment on DERBY-532 at 12/2/13 2:22 PM:
--------------------------------------------------------------
Here are the rest of the failures and errors I saw running the JUnit tests with default deferrable (the experiment mentioned above). I'll be analyzing them to see if they are to be expected or not.
- LangProcedureTest (lock difference)
{panel}
=> To be expected: we are running with serializable isolation mode
and the index used is no longer physically unique, so the
query reads the previous key also. Locks expected in the non deferred case:
{noformat}
XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
--- ---- ---- --------- -------- ----- --------- --------- ---------
{345,TABLE,IS,T1,Tablelock,GRANT,T,2,null}
{345,ROW,S,T1,(1,8),GRANT,T,1,null}
{noformat}
what we see in the deferrable case is:
{noformat}
XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
--- ---- ---- --------- -------- ----- --------- --------- ---------
{503,TABLE,IS,T1,Tablelock,GRANT,T,2,null}
{503,ROW,S,T1,(1,7),GRANT,T,1,null}
{503,ROW,S,T1,(1,8),GRANT,T,1,null}
{noformat}
{panel}
- LangScripts update (lock difference)
- LojReorderTest (different execution plan)
- InListMultiProbeTest (assertTrue(rtsp.usedIndexScan()))
- CollationTest2 (java.sql.SQLIntegrityConstraintViolationException:
The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index
identified by 'SQL131129120107383' defined on 'DERBY_5367'.)
- ConglomerateSharingTest (number of physical conglomerates that exist
for the received table: Expected: >3< Found: >4<
To be expected? (check): deferrable doesn't share index
- NullableUniqueConstraintTest (junit.framework.ComparisonFailure:
Unexpected SQL state. expected:<[23505]> but was:<[XJ001]>)
if (SanityManager.DEBUG) { // deferrable: we use a non-unique index
SanityManager.ASSERT(
insertStatus != ConglomerateController.ROWISDUPLICATE); <=====!!
}
++ more
- UniqueConstraintSetNullTest
(java.sql.SQLIntegrityConstraintViolationException: The statement
was aborted because it would have caused a duplicate key value in a
unique or primary key constraint or unique index identified by
'U_CON' defined on 'CONSTRAINTEST'.)
++ more
- UniqueConstraintMultiThreadedTest
(junit.framework.AssertionFailedError: isolation levels: 1 1)
- XplainStatisticsTest ( expected rows:
[[COUNTRIES_UNQ_NM, C, BTREE, RC, 1, 1, 1, SH, R, 2, ALL]]
actual result:
[[COUNTRIES_UNQ_NM, C, BTREE, RC, 2, 1, 1, IS, R, 2, ALL]])
- dml019(org.apache.derbyTesting.functionTests.tests.nist.NistScripts)
failed: junit.framework.ComparisonFailure: Output at line 85
expected:<E1 |P[1 |4]0 > but was:<E1 |P[2 |2]0 >
- UpdateStatisticsTest (junit.framework.AssertionFailedError: failed
to get statistics for table TEST_TAB_1 (#expected=0, timeout=0)
Index statistics for TEST_TAB_1 1:
{tableId=2f2b17ef-0142-a377-c079-0000766f8c2f, tableName=TEST_TAB_1,
indexName=SQL131129120747740, lcols=1, rows=2, unique/card=2,
created=2013-11-29 12:07:47.742} expected:<0> but was:<1>)
(Note: this run included the proposed patch for DERBY-6419 as well)
was (Author: dagw):
Here are the rest of the failures and errors I saw running the JUnit tests with default deferrable (the experiment mentioned above). I'll be analyzing them to see if they are to be expected or not.
- LangProcedureTest (lock difference)
- LangScripts update (lock difference)
- LojReorderTest (different execution plan)
- InListMultiProbeTest (assertTrue(rtsp.usedIndexScan()))
- CollationTest2 (java.sql.SQLIntegrityConstraintViolationException:
The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index
identified by 'SQL131129120107383' defined on 'DERBY_5367'.)
- ConglomerateSharingTest (number of physical conglomerates that exist
for the received table: Expected: >3< Found: >4<
To be expected? (check): deferrable doesn't share index
- NullableUniqueConstraintTest (junit.framework.ComparisonFailure:
Unexpected SQL state. expected:<[23505]> but was:<[XJ001]>)
if (SanityManager.DEBUG) { // deferrable: we use a non-unique index
SanityManager.ASSERT(
insertStatus != ConglomerateController.ROWISDUPLICATE); <=====!!
}
++ more
- UniqueConstraintSetNullTest
(java.sql.SQLIntegrityConstraintViolationException: The statement
was aborted because it would have caused a duplicate key value in a
unique or primary key constraint or unique index identified by
'U_CON' defined on 'CONSTRAINTEST'.)
++ more
- UniqueConstraintMultiThreadedTest
(junit.framework.AssertionFailedError: isolation levels: 1 1)
- XplainStatisticsTest ( expected rows:
[[COUNTRIES_UNQ_NM, C, BTREE, RC, 1, 1, 1, SH, R, 2, ALL]]
actual result:
[[COUNTRIES_UNQ_NM, C, BTREE, RC, 2, 1, 1, IS, R, 2, ALL]])
- dml019(org.apache.derbyTesting.functionTests.tests.nist.NistScripts)
failed: junit.framework.ComparisonFailure: Output at line 85
expected:<E1 |P[1 |4]0 > but was:<E1 |P[2 |2]0 >
- UpdateStatisticsTest (junit.framework.AssertionFailedError: failed
to get statistics for table TEST_TAB_1 (#expected=0, timeout=0)
Index statistics for TEST_TAB_1 1:
{tableId=2f2b17ef-0142-a377-c079-0000766f8c2f, tableName=TEST_TAB_1,
indexName=SQL131129120747740, lcols=1, rows=2, unique/card=2,
created=2013-11-29 12:07:47.742} expected:<0> but was:<1>)
(Note: this run included the proposed patch for DERBY-6419 as well)
> Support deferrable constraints
> ------------------------------
>
> Key: DERBY-532
> URL: https://issues.apache.org/jira/browse/DERBY-532
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Jörg von Frantzius
> Assignee: Dag H. Wanvik
> Labels: derby_triage10_11
> Attachments: IndexDescriptor.html, IndexDescriptorImpl.html, IndexRowGenerator.html, SortObserver.html, deferredConstraints.html, deferredConstraints.html, deferredConstraints.html, deferredConstraints.html, deferredConstraints.html, derby-532-fix-metadata-1.diff, derby-532-fix-metadata-1.status, derby-532-import-1.diff, derby-532-import-1.status, derby-532-import-2.diff, derby-532-import-3.diff, derby-532-import-3.status, derby-532-more-tests-1.diff, derby-532-more-tests-1.stat, derby-532-post-scan-1.diff, derby-532-post-scan-1.stat, derby-532-post-scan-2.diff, derby-532-post-scan-2.stat, derby-532-post-scan-3.diff, derby-532-post-scan-3.stat, derby-532-post-scan-4.diff, derby-532-post-scan-4.stat, derby-532-serializable-scan-1.diff, derby-532-serializable-scan-2.diff, derby-532-serializable-scan-2.stat, derby-532-syntax-binding-dict-1.diff, derby-532-syntax-binding-dict-1.status, derby-532-syntax-binding-dict-2.diff, derby-532-syntax-binding-dict-2.status, derby-532-syntax-binding-dict-all-1.diff, derby-532-test-with-default-deferrable-all-over.diff, derby-532-testAlterConstraintInvalidation.diff, derby-532-testAlterConstraintInvalidation.status, derby-532-unique-pk-1.diff, derby-532-unique-pk-1.status, derby-532-unique-pk-2.diff, derby-532-unique-pk-3.diff, derby-532-unique-pk-3.status, derby-532-xa-1.diff, derby-532-xa-2.diff, derby-532-xa-3.diff, derby-532-xa-3.status
>
>
> In many situations it is desirable to have constraints checking taking place only at transaction commit time, and not before. If e.g. there is a chain of foreign key constraints between tables, insert statements have to be ordered to avoid constraint violations. If foreign key references are circular, the DML has to be split into insert statements and subsequent update statements by the user.
> In other words, with deferred constraints checking, life is much easier for the user. Also it can create problems with softwares such as object-relational mapping tools that are not prepared for statement ordering and thus depend on deferred constraints checking.
--
This message was sent by Atlassian JIRA
(v6.1#6144)