[jira] [Comment Edited] (DERBY-6670) Rollback to savepoint allows violation of deferrable constraints

["Dag H. Wanvik (JIRA)" <ji...@apache.org>]

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

Dag H. Wanvik edited comment on DERBY-6670 at 7/29/14 5:17 PM:
---------------------------------------------------------------

Uploading a modified version of the patch derby-6665-01-ae-deferredCheckAndDroppedFK.diff: [^derby-6670-2-a.diff]. While investigating that patch it was found that it made the repro in this issue go away. It turned out this was due to an accidental error in the "ae" patch, but it points the way to a solution:

The logic in lcc.forgetDeferredConstraintsData failed to match with the ae patch because it compare the UUID of a constraint with the UUID of the index supporting it.  

By making the checking at commit time use UUIDs of the constraints (PK/unique/FK) and base table (check constraints), the approach of the "ae" patch, we can just skip the checking at commit iff the (transactional) dictionary objects no longer exist. This patch utilizes this by making the logic in the three validation methods in DeferredConstraintsMemory bail out if the dictionary objects cannot be found. This makes the logic immune to the rollback of the drops of tables and/or constraints.

The patch adds test cases for various "rollback to savepoint" scenarios in fixture testDerby6670.

This patch isn't for commit yet - I am still reviewing the rest of it.


was (Author: dagw):
Uploading a modified version of the patch derby-6665-01-ae-deferredCheckAndDroppedFK.diff: [^derby-6670-2-a.diff]. While investigating that patch it was found that it made the repro in this issue go away. It turned out this was due to an accidental error in the "ae" patch, but it points the way to a solution:

The logic in lcc.forgetDeferredConstraintsData failed to match with the ae patch because it compare the UUID of a constraint with the UUID of the index supporting it.  

By making the checking at commit time use UUIDs of the constraints (PK/unique/FK) and base table (check constraints), the approach of the "ae" patch, we can just skip the checking at commit iff the transaction dictionary objects no longer exists. So this patch utilizes this by making the logic in the three validation methods in DeferredConstraintsMemory bail out if the dictionary objects cannot be found. This make the logic immune to the rollback of the drops.

The patch adds test cases for various "rollback to savepoint" scenarios.

This patch isn't for commit yet - I am still reviewing the rest of it.

> 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
>         Attachments: derby-6670-01-aa-forbidSavepointsWithDeferredConstraints.diff, derby-6670-2-a.diff, derby-6670-2-a.status
>
>
> 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)