You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik (JIRA)" <ji...@apache.org> on 2014/02/24 23:18:23 UTC
[jira] [Commented] (DERBY-532) Support deferrable constraints
[ https://issues.apache.org/jira/browse/DERBY-532?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13910884#comment-13910884 ]
Dag H. Wanvik commented on DERBY-532:
-------------------------------------
Uploading a first patch {code:red}derby-532-check-constraints-1{color} which implements deferred check constraints and supporting tests. Comments are welcome.
The high level approach is as follows. When a violation occurs, we note the row location in the base table of the offending row. At commit time (or when switching a constraint to immediate), we revisit those rows using the row locations if they are still valid, and validate those rows again. This is achieved by positioning to the saved row locations in combination with a specially crafted result set: {color:blue}ValidateCheckConstraintResultSet{color} (see {color:blue}ProjectRestrictResultSet#getNextRowCore{color}) which positions to the offending base row using {color:blue}ValidateCheckConstraintResultSet#positionScanAtRowLocation{color} before letting {color:blue}ValidateCheckConstraintResultSet{color} read the row. If the row locations are no longer valid, e.g. an intervening compress happened, we do a full table scan to verify the constraints instead.
Adding a constraint in deferred constraint mode is currently sub-optimal, since we currently do a full table scan via an internally generated {color:blue}"SELECT .. WHERE NOT <constraints>"{color}, and we don't have a way the get at the row locations of the offending rows in this case. I might add a specially tailored result set for that purpose later.
Normally, when a row is inserted or updated, we execute a generated method which combines evaluation of all check constraints on the table relevant for the inserted or updated columns. This evaluation is performed using McCarthy boolean evaluation (short-circuits as soon as result is known). This isn't optimal for deferred constraints, as we'd need to assume all constraints were violated in such a case. The implementation replaces the short-circuited evaluation with a full evaluation, so we can remember exactly which constraints were violated, cf. {color:blue}AndNoShortCircuitNode{color} and {color:blue}SQLBoolean#throwExceptionIfImmediateAndFalse{color}. A violation in {color:blue}throwExceptionIfImmediateAndFalse{color} when we have a deferred constraint is noted ({color:blue}DMLWriteResultSet#rememberConstraint{color} implemented by {color:blue}UpdateResultSet{color} and {color:blue}InsertResultSet{color}) by adding the violation to a list for that row. After the insert/update is completed, the set of violations is remembered for posterity, cf. {color:blue}InsertResultSet#normalInsertCode{color} and {color:blue}UpdateResultSet#collectAffectedRows{color} by inspecting the lists ({color:blue}#violatingCheckConstraints{color}).
Note that we currently do not note which constraints were violated *for each individual row*, only per table in the transaction. This means that we visit potentially more rows over again when a single constraint is changed to immediate. This could be improved further by storing the set of violated constraints along with the row location.
For bulk insert and deferred (see panel 1 below) insert row processing there is special code paths, cf. {color:blue}InsertResultSet#offendingRowLocation{color} which is invoked via a callback from {color:blue}HeapController#load{color} and another path in {color:blue}InsertResultSet#normalInsertCode{color} respectively.
For update, the code for deferred treatment is in in one of {color:blue}UpdateResultSet#collectAffectedRows{color} and {color:blue}UpdateResultSet#updateDeferredRows{color} depending on whether there are triggers.
The existing test {color:blue}ConstraintCharacteristcsTest{color} has been built out by adding check constraint to those fixture for which it is relevant, as well as adding new ones which are only relevant for check constraints.
{quote} {color:blue}*1*{color} This "deferred" refers to Derby special handling of rows in certain situation, for example when doing an insert which uses the same table as a source result set, we need to make sure we don't get confused and see the incrementally inserted rows "again" as we process the original result set, essentially we do a snapshot of the source result set, hence "deferred rows". {quote}
All regressions passed.
Detailed code comments:
{quote}
M java/engine/org/apache/derby/iapi/sql/conn/SQLSessionContext.java
M java/engine/org/apache/derby/impl/sql/conn/SQLSessionContextImpl.java
M java/engine/org/apache/derby/iapi/sql/conn/LanguageConnectionContext.java
M java/engine/org/apache/derby/impl/sql/conn/GenericLanguageConnectionContext.java
D java/engine/org/apache/derby/impl/sql/execute/DeferredDuplicates.java
A java/engine/org/apache/derby/impl/sql/execute/DeferredConstraintsMemory.java
{quote}
Extended and refactored slightly existing mechanism for deferred primary key/unique constraints to also cater for check constraints. Since the hash key we used for the memory of primary key and unique constraints was the conglomerate id of the indexes, and those are guaranteed to be disjoint from the conglomerate ids of the base tables having deferred constraints, we can use the same hash table to find the "memory" in the form of the disk based hash table (BackingStoreHashtable), cf. LCC#getDeferredHashTables.--
{quote}
M java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java-
{quote}
Code to drop any deferred constraints memory in the transaction when a constraint is dropped.-
{quote}
M java/engine/org/apache/derby/impl/store/access/heap/HeapController.java
{quote}
Call back added for bulk insert in the presence of deferrable check constraints.
{quote}
M java/engine/org/apache/derby/iapi/sql/execute/NoPutResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java
M java/engine/org/apache/derby/iapi/store/access/RowLocationRetRowSource.java
{quote}
Extra plumbing to be able to signal to HeapController that we need to do a callback with the inserted row location (for bulk insert)
{quote}
M java/engine/org/apache/derby/iapi/sql/execute/TargetResultSet.java
{quote}
Extra interface method, offendingRowLocation. Only implemented with meaningful semantics for NoPutResultSetImpl which calls it for its targetResultSet, an InsertResultSet.
{quote}
M java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
M java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
{quote}
More parameters to getProjectRestrictResult set to do the magic mention in the overview for that result set, pass along schema and table name to InsertResultSet so we can remember them for check violations. They are used to produced checking SQL statements. This may be a bit fragile, since a rename schema or table could make those invalid. However, there is presently no RENAME SCHEMA in Derby and the RENAME TABLE is illegal in certain cases, notably if there is a check constraint defined on it, so the solution should be OK for now. Also adds an interface method, getValidateCheckConstraintResultSet, to allow the execution run-time to build one of those, cf. code generation logic in NestedLoopStrategy#resultSetMethodName.
{quote}
M java/engine/org/apache/derby/iapi/sql/execute/RowChanger.java
M java/engine/org/apache/derby/impl/sql/execute/RowChangerImpl.java
{quote}
Extra parameter to insertRow to get at the row location if needed.
{quote}
M java/engine/org/apache/derby/iapi/store/access/BackingStoreHashtable.java
M java/engine/org/apache/derby/iapi/store/access/ScanController.java
{quote}
Javadoc fixes.
{quote}
M java/engine/org/apache/derby/iapi/types/BooleanDataValue.java
M java/engine/org/apache/derby/iapi/types/SQLBoolean.java
{quote}
Extra method throwExceptionIfImmediateAndFalse used by deferred check constraints to make a note of all violated constraints as evaluated by the generated method. Picked up by InsertResultSet or UpdateResultSet.
{quote}
A java/engine/org/apache/derby/impl/sql/compile/AndNoShortCircuitNode.java
M java/engine/org/apache/derby/impl/sql/compile/AndNode.java
M java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
{quote}
AndNoShortCircuitNode is used to represent a non-McCarthy evaluation of the combined check constraints. See usage in DMLModStatementNode#generateCheckTree.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
{quote}
Extra dummy parameter added for call to super#bindConstraints (DMLModStatementNode). Only used by insert.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
{quote}
Pick up the DERBY_PROPERTIES value for property "validateCheckConstraint = <conlomerateId>" we provide to the checking query (internal syntax only) generated by DeferredConstraintsMemory#validateCheck. The conglomerate id is used to retrieve the violating rows information set up by ProjectRestrictResultSet#openCore to drive ValidateCheckConstraintResultSet.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
{quote}
Boolean member variable to know if we have a deferrable check constraint; also pass only schema and table name to the result set. Passed on to the InsertConstantAction from which InsertResultSet can pick it up.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
{quote}
Logic to keep track of whether we are used by the special internal query to check violated check constraints. In this case we also do not push the check predicates down to store for simpler handling.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
{quote}
Code to parse a long value from "--DERBY-PROPERTIES" property.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/SetConstraintsNode.java
{quote}
Extra code to comply with the sane mode parse tree printing conventions.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/TestConstraintNode.java
{quote}
Handle different code generation for deferrable check contraints.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
{quote}
Pass on more info: schema and table name + small refactoring.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
{quote}
Handle the new internal query to validate violated check constraints. Cf. query in DeferredConstraintsMemory#validateCheck.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/AlterConstraintConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/CreateConstraintConstantAction.java
{quote}
Open up for check constraints.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/ConstraintConstantAction.java
{quote}
ATCA: Special handling of adding a deferred check constraint: need different code path to get the UUID of constraint soon enough to be able to note any constraint violations. CCA: note any violation and remember it. We'd like to remember that row locations of the offending rows here, but not done for now, so at checking time, we'll need a full table scan. This can be improved upon, see code comment.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java
M java/engine/org/apache/derby/impl/sql/execute/UpdateConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/InsertConstantAction.java
{quote}
Pass on more info to InsertConstantAction and UpdateConstantAction needed by the result sets.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
{quote}
Drives the checking for check constraints, and picks up the result. If we have violations and deferred constraints, we remember that. Also some refactorings to avoid local variables shadowing globals.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
{quote}
Drives the checking for check constraints, and picks up the result. If we have violations and deferred constraints, we remember that.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java
{quote}
Removed unused method.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/ProjectRestrictResultSet.java
{quote}
Drive the special result set, ValidateCheckConstraintResultSet by positioning it correctly for each row retrieved, using the remembered row locations from violation time.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/SetConstraintsConstantAction.java
{quote}
Added logic for check constraints. Also added a new check that the user don't specify the same constraint twice, cf new test case for it.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/TableScanResultSet.java
{quote}
Make some members protected rather than private, to let the new result set ValidateCheckConstraintResultSet inherit from it.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/TemporaryRowHolderResultSet.java
M java/engine/org/apache/derby/impl/store/access/sort/MergeScanRowSource.java
M java/engine/org/apache/derby/impl/store/access/sort/SortBufferRowSource.java
M java/engine/org/apache/derby/impl/sql/execute/CardinalityCounter.java
M java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java
{quote}
Boiler plate to comply with interface (not used).
{quote}
M java/engine/org/apache/derby/impl/sql/execute/UniqueIndexSortObserver.java
M java/engine/org/apache/derby/impl/sql/execute/UniqueWithDuplicateNullsIndexSortObserver.java
M java/engine/org/apache/derby/impl/sql/execute/IndexChanger.java
M java/engine/org/apache/derby/impl/sql/execute/CreateIndexConstantAction.java
{quote}
Refactoring only.
{quote}
A java/engine/org/apache/derby/impl/sql/execute/ValidateCheckConstraintResultSet.java
{quote}
The new result we use to check violating rows only based on row location
{quote}
M java/engine/org/apache/derby/iapi/sql/compile/JoinStrategy.java
M java/engine/org/apache/derby/impl/sql/compile/HashJoinStrategy.java
M java/engine/org/apache/derby/impl/sql/compile/NestedLoopJoinStrategy.java
{quote}
New boolean to signal that we want ValidateCheckConstraintResultSet
{quote}
M java/engine/org/apache/derby/jdbc/EmbedXAResource.java
M java/engine/org/apache/derby/jdbc/XATransactionState.java
{quote}
Extra logic to handle check constraints (already had it for primary key and unique).
{quote}
M java/engine/org/apache/derby/iapi/error/ExceptionUtil.java
{quote}
Utility method to determine if an exception if a transaction deferred constraint violation. Needed by the XA code.
{quote}
M java/engine/org/apache/derby/loc/messages.xml
M java/shared/org/apache/derby/shared/common/reference/SQLState.java
{quote}
New error messages
{quote}
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java
{quote}
New test cases and extension of present ones to include check constraints
{quote}
M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java
{quote}
Extension of present test cases to include check constraints.
> 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-allow-pk-unique-1.diff, derby-532-allow-pk-unique-1.status, 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-speedup.diff, derby-532-test-speedup.status, 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-upgrade-1.diff, derby-532-upgrade-1.status, derby-532-upgrade-1b.diff, 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.5#6160)