db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-532) Support deferrable constraints
Date Mon, 11 Nov 2013 20:09:18 GMT

    [ https://issues.apache.org/jira/browse/DERBY-532?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13819311#comment-13819311
] 

Mike Matrigali commented on DERBY-532:
--------------------------------------

my proposal would be to hopefully use the exact same code to check for duplicates in all deferrable
cases.
The only difference would be the timing of the check. 

So in both cases just do the insert as normal into the duplicate allowing index.  

In the deferred case we track that we need to do a duplicate check and then do it later.
In the immediate case we just do the duplicate check right away.  Hopefully the checking code
is the same. 
And I do think there is an obvious benefit and clean to adding a new store interface to do
the check.  In 
both cases other transactions don't see the duplicate key because of locking, and deleting
the 
duplicate rows is taken care of by duplicate key abort.

I think knut's suggestion might work.   I would rather see us first implement stuff all the
same (deferred and
immedate) and measure.  then see if it is worth adding different codepaths for optimization.
 

+1 to later thinking about removing all the left/right checking and use this same approach
for 
nullable unique constraints.  

It does seem like it is well known in other systems that deferrable constraints are likely
to not perform
as well as deferrable constraints.  So I think it is ok for 1st implementation to not match
performance, just
be correct.  I do wonder how often users actually want to defer uniqueness constraint other
than possibly
initial loading situations.  I think the big request is really about deferred foriegn key
contraints, things like
loading a child before a parent, or some set of circular constraints that are hard to order.



> 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: deferredConstraints.html, deferredConstraints.html, deferredConstraints.html,
deferredConstraints.html, 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-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-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