db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-532) Support deferrable constraints
Date Mon, 02 Dec 2013 19:48:36 GMT

    [ 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 7:48 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)
 {panel}
 => To be expected. We have this table:
 {noformat}
   create table tab1 (c1 int not null primary key, c2 int)
   insert into tab1 values (1, 8)
 {noformat}
 and then we do:
 {noformat}
   update tab1 set c2 = c2 + 3 where c1 = 1;
 {noformat}
 Read committed, deferrable
 {noformat}
  XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
  --- ---- ---- --------- -------- ----- --------- --------- ---------
  {184,TABLE,IX,TAB1,Tablelock,GRANT,T,2,null}
  {184,ROW,X,TAB1,(1,7),GRANT,T,3,null}
 {noformat}
 Serializable , deferrable
 {noformat}
  XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
  --- ---- ---- --------- -------- ----- --------- --------- ---------
 {186,TABLE,IX,TAB1,Tablelock,GRANT,T,2,null}
 {186,ROW,X,TAB1,(1,3),GRANT,T,1,null}
 {186,ROW,X,TAB1,(1,7),GRANT,T,3,null}
 {noformat}
 i.e. we also have a lock on the first (control) row (before row
 containing 1).  In the non-deferrable case, again we have a unique
 physical index on c1, so a previous lock is not required. Note, for
 read committed there is no difference.
 {panel}
- LojReorderTest (different execution plan)
  {panel}
  => To be expected. The original plan looked like this:
  {noformat}	     
     "Sort ResultSet:",
     "Source result set:",
     "_Hash Left Outer Join ResultSet:",
     "_Left result set:",
     "__Nested Loop Left Outer Join ResultSet:",
     "__Left result set:",
     "___Nested Loop Left Outer Join ResultSet:",
     "___Left result set:",
     "____Index Scan ResultSet for A",
     "___Right result set:",
     "____Index Scan ResultSet for B",
     "__Right result set:",
     "___Index Scan ResultSet for C",
     "_Right result set:",
     "__Hash Scan ResultSet for D"});
  {noformat}
  With deferrable constraints it looks like this:
  {noformat}
    "Sort ResultSet:",
    "Source result set:",
    "_Hash Left Outer Join ResultSet:",
    "_Left result set:",
    "__Nested Loop Left Outer Join ResultSet:",
    "__Left result set:",
    "___Hash Left Outer Join ResultSet:",
    "___Left result set:",
    "____Index Scan ResultSet for A",
    "___Right result set:",
    "____Hash Scan ResultSet for B",
    "__Right result set:",
    "___Index Scan ResultSet for C",
    "_Right result set:",
    "__Hash Scan ResultSet for D"});
  {noformat}
  As we can see, the deferrable the inner (leftmost) LOJ uses as Hash
  left order join with and index scan for A and a hash scan for B,
  whereas the non deferrable case uses nested loop over two index
  scans for A and B, presumably because the physical uniqeness of the
  index on B gives the optimizer another cardinality estimate. In any
  case both plans are good.
  {panel}
- 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)
  {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)
 {panel}
 => To be expected. We have this table:
 {noformat}
   create table tab1 (c1 int not null primary key, c2 int)
   insert into tab1 values (1, 8)
 {noformat}
 and then we do:
 {noformat}
   update tab1 set c2 = c2 + 3 where c1 = 1;
 {noformat}
 Read committed, deferrable
 {noformat}
  XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
  --- ---- ---- --------- -------- ----- --------- --------- ---------
  {184,TABLE,IX,TAB1,Tablelock,GRANT,T,2,null}
  {184,ROW,X,TAB1,(1,7),GRANT,T,3,null}
 {noformat}
 Serializable , deferrable
 {noformat}
  XID,TYPE,MODE,TABLENAME,LOCKNAME,STATE,TABLETYPE,LOCKCOUNT,INDEXNAME
  --- ---- ---- --------- -------- ----- --------- --------- ---------
 {186,TABLE,IX,TAB1,Tablelock,GRANT,T,2,null}
 {186,ROW,X,TAB1,(1,3),GRANT,T,1,null}
 {186,ROW,X,TAB1,(1,7),GRANT,T,3,null}
 {noformat}
 i.e. we also have a lock on the first (control) row (before row
 containing 1).  In the non-deferrable case, again we have a unique
 physical index on c1, so a previous lock is not required. Note, for
 read committed there is no difference.
 {panel}
- 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)

Mime
View raw message