[
https://issues.apache.org/jira/browse/DERBY-6670?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14080034#comment-14080034
]
ASF subversion and git services commented on DERBY-6670:
--------------------------------------------------------
Commit 1614777 from [~dagw] in branch 'code/trunk'
[ https://svn.apache.org/r1614777 ]
DERBY-6670 Rollback to savepoint allows violation of deferrable constraints
Patch derby-6670-2-c. This patch is a rewrite of the prototype patch
derby-6665-01-ae-deferredCheckAndDroppedFK.diff which moves from using
physical conglomerate ids as keys in the internal identification of
constraints and their indexes for deferrable constraints to
- UUID for the constraints for primary key, unique and foreign key
- UUID for base table (or constraint dependening on where in the code)
for check constraints
This is essentially only a refactoring which simplifies the code. But
it also makes possible the fix for this issue, which is to stop
removing the deferrable constraint violation information when
constraints are dropped (directory or as a result of the table being
dropped), since a rollback to savepoint might undo drops, at which
point we need the violation information again. The solution is to make
the validation code robust against the absence of dictionary objects
(tables, constraints): if at commit or "set immediate", the objects
are gone we know there is no checking to be done.
The patch also adds rollback to savepoint test cases in
ConstraintCharacteristicsTest#testDerby6670_a, including the repro for
this issue.
Also removed the release of violation information at a successful
change to immediate constraint mode as part of a SET CONSTRAINTS
statement, since a rollback to savepoint might re-introduce the
violations. New test cases added for this, too (#testDerby6670_b).
> Rollback to savepoint allows violation of deferrable constraints
> ----------------------------------------------------------------
>
> Key: DERBY-6670
> URL: https://issues.apache.org/jira/browse/DERBY-6670
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.11.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Dag H. Wanvik
> Attachments:
> derby-6670-01-aa-forbidSavepointsWithDeferredConstraints.diff,
> derby-6670-2-a.diff, derby-6670-2-a.status, derby-6670-2-b.diff,
> derby-6670-2-b.status, derby-6670-2-c.diff
>
>
> The bug is illustrated by the following code snippet:
> {code}
> Connection c =
> DriverManager.getConnection("jdbc:derby:memory:db;create=true");
> c.setAutoCommit(false);
> Statement s = c.createStatement();
> s.execute("create table t1(x int primary key initially deferred)");
> s.execute("insert into t1 values 1,1,1,1");
> Savepoint sp = c.setSavepoint();
> s.execute("drop table t1");
> c.rollback(sp);
> // Since there are four identical rows in T1, this call should have
> // failed because the primary key was violated.
> c.commit();
> // Instead, it succeeds, and all four rows are committed, as can
> // be seen here:
> ResultSet rs = s.executeQuery("select * from t1");
> while (rs.next()) {
> System.out.println(rs.getInt(1));
> }
> // Insert yet another row, so that we have five identical rows ...
> s.execute("insert into t1 values 1");
> // ... and now commit complains ...
> c.commit();
> {code}
> With auto-commit off, add duplicates into a deferred primary key. Then set a
> savepoint, drop the table, and roll back to the savepoint.
> Apparently, when you drop the table, information about any constraint
> violations seen on that table is lost, and that information is not restored
> when the drop table operation is undone by the rollback to savepoint.
> So when you commit the transaction after having rolled back the drop
> operation, no deferred checking of constraints happens, and the duplicates
> you have inserted are committed.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
