Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Fri, 18 Jul 2014 20:04:05 +0000 (UTC)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12727966.1405615135258.74349.1405713845847@arcas>
In-Reply-To: <JIRA.12727966.1405615135258@arcas>
References: <JIRA.12727966.1405615135258@arcas>
Subject: [jira] [Commented] (DERBY-6670) Rollback to savepoint allows
 violation of deferrable constraints
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Rick Hillegas commented on DERBY-6670:
--------------------------------------

Some relief may be provided by the fix for DERBY-6665. That should eliminate some of the code paths which clear the memory of deferred violations. Right now, the memory is cleared when conglomerate ids change, but by using the logical constraint ids, there should be no need to clear the memory.

> 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
>
>
> 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)