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] [Commented] (DERBY-532) Support deferrable constraints
Date Wed, 13 Nov 2013 17:29:23 GMT

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

Dag H. Wanvik commented on DERBY-532:

Thanks, Mike & Knut!

> question: Is it possible for 2 transactions to be operating at the
> same time on a deferrable constraint and in one case the checking is
> deferred and in the 2nd case the checking is immediate?

Yes, the constraint mode is session local according to the standard. The present patch allows
this, I think.

>>I had to make one small concession: the unique nullable constraints
>>needed an extra predicate inside BtreeController to allow them to
>>insert a duplicate. An alternative would be to mark these indexes as
>>something else than "uniqueWithDuplicateNulls" when constraints are
>In the deferable case I think these should be just standard
>duplicate allowing index, and do the "allow duplicate" checking in
>language. I believe it is very easy. If any key in the row is null
>always allow it, otherwise it becomes the same case as the other
>defered uniqueness constraints.

Yes, and this is indeed what we do for nullable unique for deferrable indexes. I think I can
fix that by just omitting to make the index conglomerate with uniqueWithDuplicateNulls in
the deferrable case, yes.

As for the locking case, xact 2 would block. I agree with Knut we could optimize the locking
as suggested later.

> If a new version is needed, could you comment on why?

Cf. the above: if we handle the uniqueWithDuplicateNulls as discussed above, the need to mark
the conglomerate with hasDeferrableChecking would go away (and we no longer need the isUniqueDeferrable
at the BTree level), so we don't need the format change: I'll roll a new patch.

> 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-serializable-scan-1.diff, 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,
> 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

View raw message