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] [Updated] (DERBY-532) Support deferrable constraints
Date Wed, 20 Nov 2013 19:08:37 GMT

     [ https://issues.apache.org/jira/browse/DERBY-532?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Dag H. Wanvik updated DERBY-532:
--------------------------------

    Attachment: derby-532-post-scan-2.stat
                derby-532-post-scan-2.diff

Thanks for the comments, Mike. Please see my replies inlined..

> Index: java/engine/org/apache/derby/catalog/IndexDescriptor.java
> nit: maybe would be good to add comments here describing that unique
> deferrable indexes are implemented using store non-uniqe indexes, and
> that sql layer owns enforcing uniqueness.

Done, cf. attachment IndexDescriptor.html and IndexDescriptorImpl.html

> 
> Index: java/engine/org/apache/derby/catalog/types/IndexDescriptorImpl.java
> nit: same comment as about about more detail about deferrable indexes.
> 
> some document table describing the use of the following associating it with
> deferred and non-deffered and different contraint types might help to make
> it clearer:
> 
> boolean isUnique,
> boolean isUniqueWithDuplicateNulls,
> boolean isUniqueDeferrable,
> boolean hasDeferrableChecking,

See above.

> java/engine/org/apache/derby/iapi/sql/dictionary/IndexRowGenerator.java
> nit: same comment about expanding on what isUniqueDeferrable means
  for the index

Fixed, see attached IndexRowGenerator.html.

> 
> java/engine/org/apache/derby/iapi/store/access/SortObserver.java
> would be nice for the 3 new interfaces to have javadoc comments for each,
> explaing their use in more detail.

Added, see SortObserver.html

> Index: java/engine/org/apache/derby/impl/sql/execute/AlterConstraintConstantActi
> on.java
> 
> o would be good to have tests that alter back and forth from deferred and
> not deffered and make sure subsequent actions do the right thing. Could
> looks like existing mechanism should work, but seems a good code path to
> test.

I have one test already and added more in ConstraintCharactericsTest#testBasicDeferral on
these lines and below

// Try to set immediate mode, and detect violation
assertStatementError("23507", s, sCF + " immediate");
// Once more, error above should not roll back
assertStatementError("23507", s, sCF + " immediate");


> 
> Index: java/engine/org/apache/derby/impl/sql/execute/CreateIndexConstantAction.java
> o
> some comment on why sharing is turned off for deferable constraints would
> be good. A user is going to complain at some point when they see duplicate
> indexes taking up twice space that they don't think they need.

Added a comment. I guess we could introduce sharing in a follow-up patch with any non-unique
indexes now that all checking happens in language.

> 
> Index: java/engine/org/apache/derby/impl/sql/execute/DeferredDuplicates.java
> 
> o This code throws SQLState.LANG_DEFERRED_DUPLICATE_KEY_CONSTRAINT_T or
> SQLState.LANG_DEFERRED_DUPLICATE_KEY_CONSTRAINT_S. If these are not
> caught and remapped can someone comment on if this is the standard. Is it
> ok that this type of constrain will throw a different error than previous
> constraints? I think it is ok with standard as they all start with
> 23xxx.

These are indeed thrown. As far as I can understand, the prefix 23 is correct.

> can you comment on statement vs transaction level rollback. While thinking
> about this I have always assumed transaction level rollback to get the
> rows out of the table. I think statement level must be when savepoints
> are involved.

The _S variant doesn't roll back - the transaction is still active and if the offending rows
aren't deleted, the commit will throw the _T variant and roll back. This is tested in ConstraintCharactericsTest#testBasicDeferral.



> 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-post-scan-1.diff, derby-532-post-scan-1.stat, derby-532-post-scan-2.diff, derby-532-post-scan-2.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-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