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, 09 Dec 2013 16:28:09 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/9/13 4:27 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. 

(Note: this run included the proposed patch for DERBY-6419 as well)

[Update 2013-12-09 Analysis added for all issues]

- 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()))
  {panel}
  Test case testDerby6045WithoutUpdateStatistics fails.
  In this case, Derby picks a table scan instead of an index scan for multi
  probe result set.  I see using Rick's tools to trace the optimizer that the
  selectivity for the start stop predicate on TERM_ID is increased from 0.1 in
  the no deferrable case to 1.0 in the deferrable case.
  {noformat}
  s.executeQuery("SELECT  *  FROM  " + whiteSpace + 
                    DERBY_6045_DATA_TABLE + 
                    " WHERE (TERM_ID = 11) OR " +
                    "(TERM_ID =21) OR (TERM_ID = 31)");
  {noformat}
  Presumably this makes the optimizer discard the index scan in favor of a table scan, cf.
this quote from DERBY-47:
  {noformat}
  So in the context of row counts, if the number of IN-list predicates
  multiplied by the estimated row count (after stat selectivity is applied)
  yields a high precentage row count (ex. all rows in the table) then the odds
  of the optimizer choosing to use that particular index are lower. It may
  still choose to use the index, in which case multi-probing will take effect,
  but it probably will not (it all depends).
  {noformat}
  Perhaps this could be improved upon (should?). For a "normal" non unique index this may
be
  the right choice but it does seem a bit pessimistic for the deferrable case.
  {panel}
- 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'.)
  {panel}
  Not an issue with last patch.
  {panel}
- ConglomerateSharingTest (number of physical conglomerates that exist
  for the received table: Expected: >3< Found: >4<
  {panel}
  We have the following scenario:
  {noformat}
        create table dropc_t2 (a int, b int not null, c int not null)
        create index dropc_ix1 on dropc_t2 (a,b)
        create unique index dropc_uix2 on dropc_t2 (c)
        alter table dropc_t2 add constraint dropc_uc1 unique (c)
        alter table dropc_t2 add constraint dropc_fk0 
            foreign key (a,b) references dropc_t0
        alter table dropc_t2 add constraint dropc_fk1 
            foreign key (a,b) references dropc_t0
        alter table dropc_t2 add constraint "dropc_fk2 
            foreign key (c) references dropc_t1

        /* Should have 3 conglomerates on DROPC_T2:
         *
         *  1. Heap
         *  2. DROPC_IX1 (shared by: DROPC_FK0, DROPC_FK1)
         *  3. DROPC_UIX2 (shared by: DROPC_UC1, DROPC_FK2)
         */
  {noformat}
  Here the unique constraint DROPC_UC1 is deferrable by default, so it doesn't
  share the unique index DROPC_UIX2. This is as designed.
  {panel}
- 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
  {panel}
  To be expected.  This fails in the test case testMixedInsertDelete
  which mentions an earlier problem with this fixture:
  DERBY-4097. Since we are now running a BTree scan to check for the
  duplicate instead of the check on the left sibling as done for
  unique not null in the non deferrable case, we don't get to retry if
  we can't lock the row. We lose here because we timeout trying to get
  a lock for the same reason as described in DERBY-4097; a conflict
  with the post commit work done by another thread. It happens in
  iteration four. As discussed in the thread, the issue can be avoided
  by turning off autocommit. Another thing is that in this case, it
  would be better to report the time-out rather report it as a
  duplicate I think: since we are running with immediate checking. 
  {panel}
- 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
  {panel}
  This exposed a bug: a broken predicate when recreating the index
  when going from UNQIUE NOT NULL to plain UNIQUE: the existing test
  missed the deferrable case, so the index was not recreated.
  Fixed in attached patch derby-532-fix-drop-not-nullable, which also
  adds a test case for this.
  {panel}
- UniqueConstraintMultiThreadedTest
  (junit.framework.AssertionFailedError: isolation levels: 1 1)
  {panel}
  This exposed a bug with the introduction of the no-wait scan, cf.
  DERBY-6419: we should only return immediately from the checking scan
  if we hit a timeout or deadlock iff the constraint mode is deferred.
  For immediate deferrable constraints, the check should wait obey the
  usual timeout values. This is fixed with a follow-up patch
  to DERBY-6419.
  {panel}
- 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]])
  {panel}
  The difference here as far as I can see consists of two items:
  - two rows are read from via the index instead of one (ok I think)
  - the lock mode is "instantaneous"; in the non-deferrable case I see
    the following in the execution plan:
    {noformat}
    Index Scan ResultSet for COUNTRIES using constraint COUNTRIES_UNQ_NM at
    read committed isolation level using share row locking chosen by the optimizer
    {noformat}
    whereas in the deferrable case I see:
    {noformat}
    Index Scan ResultSet for COUNTRIES using constraint COUNTRIES_UNQ_NM at 
    read committed isolation level using instantaneous share row locking chosen by the optimizer
    {noformat}
    I presume this is because we are now using a non-unique scan, but
    I am not 100% sure. As for now I'll treat this as an "expected difference".
  {panel}
- 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 >
  {panel}
  To be expected: the following statement gave the correct rows, but the ordering in the result
set was different from the master due to a different execution plan.
  {noformat}
   SELECT PNUM,EMPNUM                      
          FROM WORKS                              
          GROUP BY EMPNUM,PNUM,HOURS;
  {noformat}
  {panel}
- 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>)
  {panel}
  To be expected: There were four errors here, and all were instances of the same issue: we
found statistics where none were expected due to 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.". Since primary key indexes are not physically unique with deferrable
indexes, the skipping of the statistics generation did not happen.
  {panel}



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)
  {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()))
  {panel}
  Test case testDerby6045WithoutUpdateStatistics fails.
  In this case, Derby picks a table scan instead of an index scan for multi
  probe result set.  I see using Rick's tools to trace the optimizer that the
  selectivity for the start stop predicate on TERM_ID is increased from 0.1 in
  the no deferrable case to 1.0 in the deferrable case.
  {noformat}
  s.executeQuery("SELECT  *  FROM  " + whiteSpace + 
                    DERBY_6045_DATA_TABLE + 
                    " WHERE (TERM_ID = 11) OR " +
                    "(TERM_ID =21) OR (TERM_ID = 31)");
  {noformat}
  Presumably this makes the optimizer discard the index scan in favor of a table scan, cf.
this quote from DERBY-47:
  {noformat}
  So in the context of row counts, if the number of IN-list predicates
  multiplied by the estimated row count (after stat selectivity is applied)
  yields a high precentage row count (ex. all rows in the table) then the odds
  of the optimizer choosing to use that particular index are lower. It may
  still choose to use the index, in which case multi-probing will take effect,
  but it probably will not (it all depends).
  {noformat}
  Perhaps this could be improved upon (should?). For a "normal" non unique index this may
be
  the right choice but it does seem a bit pessimistic for the deferrable case.
  {panel}
- 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'.)
  {panel}
  Not an issue with last patch.
  {panel}
- ConglomerateSharingTest (number of physical conglomerates that exist
  for the received table: Expected: >3< Found: >4<
  {panel}
  We have the following scenario:
  {noformat}
        create table dropc_t2 (a int, b int not null, c int not null)
        create index dropc_ix1 on dropc_t2 (a,b)
        create unique index dropc_uix2 on dropc_t2 (c)
        alter table dropc_t2 add constraint dropc_uc1 unique (c)
        alter table dropc_t2 add constraint dropc_fk0 
            foreign key (a,b) references dropc_t0
        alter table dropc_t2 add constraint dropc_fk1 
            foreign key (a,b) references dropc_t0
        alter table dropc_t2 add constraint "dropc_fk2 
            foreign key (c) references dropc_t1

        /* Should have 3 conglomerates on DROPC_T2:
         *
         *  1. Heap
         *  2. DROPC_IX1 (shared by: DROPC_FK0, DROPC_FK1)
         *  3. DROPC_UIX2 (shared by: DROPC_UC1, DROPC_FK2)
         */
  {noformat}
  Here the unique constraint DROPC_UC1 is deferrable by default, so it doesn't
  share the unique index DROPC_UIX2. This is as designed.
  {panel}
- 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
  {panel}
  To be expected.  This fails in the test case testMixedInsertDelete
  which mentions an earlier problem with this fixture:
  DERBY-4097. Since we are now running a BTree scan to check for the
  duplicate instead of the check on the left sibling as done for
  unique not null in the non deferrable case, we don't get to retry if
  we can't lock the row. We lose here because we timeout trying to get
  a lock for the same reason as described in DERBY-4097; a conflict
  with the post commit work done by another thread. It happens in
  iteration four. As discussed in the thread, the issue can be avoided
  by turning off autocommit. Another thing is that in this case, it
  would be better to report the time-out rather report it as a
  duplicate I think: since we are running with immediate checking. 
  {panel}
- 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
  {panel}
  This exposed a bug: a broken predicate when recreating the index
  when going from UNQIUE NOT NULL to plain UNIQUE: the existing test
  missed the deferrable case, so the index was not recreated.
  Fixed in attached patch derby-532-fix-drop-not-nullable, which also
  adds a test case for this.
  {panel}
- UniqueConstraintMultiThreadedTest
  (junit.framework.AssertionFailedError: isolation levels: 1 1)
  {panel}
  This exposed a bug with the introduction of the no-wait scan, cf.
  DERBY-6419: we should only return immediately from the checking scan
  if we hit a timeout or deadlock iff the constraint mode is deferred.
  For immediate deferrable constraints, the check should wait obey the
  usual timeout values. This is fixed with a follow-up patch
  to DERBY-6419.
  {panel}
- 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]])
  {panel}
  The difference here as far as I can see consists of two items:
  - two rows are read from via the index instead of one (ok I think)
  - the lock mode is "instantaneous"; in the non-deferrable case I see
    the following in the execution plan:
    {noformat}
    Index Scan ResultSet for COUNTRIES using constraint COUNTRIES_UNQ_NM at
    read committed isolation level using share row locking chosen by the optimizer
    {noformat}
    whereas in the deferrable case I see:
    {noformat}
    Index Scan ResultSet for COUNTRIES using constraint COUNTRIES_UNQ_NM at 
    read committed isolation level using instantaneous share row locking chosen by the optimizer
    {noformat}
    I presume this is because we are now using a non-unique scan, but
    I am not 100% sure. As for now I'll treat this as an "expected difference".
  {panel}
- 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 >
  {panel}
  To be expected: the following statement gave the correct rows, but the ordering in the result
set was different from the master due to a different execution plan.
  {noformat}
   SELECT PNUM,EMPNUM                      
          FROM WORKS                              
          GROUP BY EMPNUM,PNUM,HOURS;
  {noformat}
  {panel}
- 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-drop-not-nullable.diff,
derby-532-fix-drop-not-nullable.status, 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-nullableUniqueFix.diff,
derby-532-nullableUniqueFix.status, 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.4#6159)

Mime
View raw message