db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-268) Add Support for truncate table
Date Fri, 24 Sep 2010 17:57:33 GMT

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

Rick Hillegas updated DERBY-268:
--------------------------------

    Attachment: TruncateConcurrency.java

Attaching TruncateConcurrency.java. This test program explores how TRUNCATE TABLE interacts
with holdable cursors. As a result of running this program, I believe:

i) Derby's behavior is consistent with the Standard.

ii) However, the test uncovers other bugs.


------------

The program creates two connections, which may be the same, depending on whether "same" or
"different" is specified.

o Selector - This connection opens a holdable cursor for reading a table.

o Truncator - This connection truncates the table.

After creating the table and putting 2 rows in it, the program does the following:

o Selector opens its holdable cursor (sensitive or insensitive, depending on the argument)
then reads a row.

o If commitSelector is set, Selector commits its transaction.

o Truncator truncates the table.

o If commitTruncator is set, Truncator commits its transaction.

o Selector reads the remaining rows from the table.


---------


Here's how to run the program:

   java TruncateConcurrency $transaction $sensitivity [ $commit ]*

   where

   $transaction  =    same | different (whether the reader and truncator do their work in
the same transaction)
   $sensitivity    =    sensitive | insensitive (whether the reader should use a sensitive
or insensitive cursor)
   $commit       =    commitSelector | commitTruncator

   If commitSelector is specified, then the Selector commits after
   reading a row but before the truncation.

   If commitTruncator is specified, then the Truncator commits
   immediately after truncation.

   E.g.:

   java TruncateConcurrency different sensitive commitTruncator commitSelector

-----------

I see the following behavior:

1) When the cursor is open in the SAME transaction which truncates the table, then the TRUNCATE
TABLE command fails. Sensitivity is irrelevant. Whether the transaction commits after reading
the first row is also irrelevant.

That is, in the following experiments, the TRUNCATE TABLE raises "Operation 'TRUNCATE TABLE'
cannot be performed on object 'T' because there is an open ResultSet dependent on that object.":

java TruncateConcurrency same sensitive
java TruncateConcurrency same sensitive commitTruncator
java TruncateConcurrency same sensitive commitSelector
java TruncateConcurrency same sensitive commitTruncator commitSelector
java TruncateConcurrency same insensitive
java TruncateConcurrency same insensitive commitTruncator
java TruncateConcurrency same insensitive commitSelector
java TruncateConcurrency same insensitive commitTruncator commitSelector



2) When the Selector and Truncator are DIFFERENT transactions, then Derby's behavior is bizarre.
Regardless of sensitivity, after truncation the Selector is able to read the last row from
the table. Then on the subsequent ResultSet.next() call, Derby misbehaves. There are two kinds
of misbehavior here. The sub-cases are distinguished by whether the Truncator committed immediately
after truncating the table. Whether the Selector committed after reading its first row is
also irrelevant.

2a) If the Truncator committed immediately after truncating the table, then the Selector trips
across an NPE when calling ResultSet.next().

That is, in the following experiments, the Selector triggers an NPE when calling ResultSet.next():

java TruncateConcurrency different sensitive commitTruncator
java TruncateConcurrency different sensitive commitTruncator commitSelector
java TruncateConcurrency different insensitive commitTruncator
java TruncateConcurrency different insensitive commitTruncator commitSelector



2b) If the Truncator did NOT commit after truncating the table, then the Selector hangs on
ResultSet.next().

That is, the following experiments hang the Selector when calling ResultSet.next():

java TruncateConcurrency different sensitive
java TruncateConcurrency different sensitive commitSelector
java TruncateConcurrency different insensitive
java TruncateConcurrency different insensitive commitSelector


------------

The SQL Standard, part 2, section 14.10 <truncate table statement>, General Rules 2-4
provide guidance for case (1), that is, for when a cursor on the table is open in the same
transaction which truncates the table. In this case, Derby refuses to truncate the table and
raises an exception. One could quibble about the wording of the exception, but I do not think
that is a serious divergence from the Standard. Derby's behavior in this case seems to be
correct to me.

The Standard does not provide guidance for case (2). I believe that we should fix Derby to
behave like case (1). That is, the TRUNCATE TABLE command should raise an exception if there
is a cursor open on the table.


> Add Support for truncate table
> ------------------------------
>
>                 Key: DERBY-268
>                 URL: https://issues.apache.org/jira/browse/DERBY-268
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Lance Andersen
>            Assignee: Eranda Sooriyabandara
>            Priority: Minor
>         Attachments: derby-268-01-ab-enableForInsaneBuilds.diff, derby-268-02-aa-permsTest.diff,
Derby-268.diff, tests.diff, TruncateConcurrency.java
>
>
> Adding support for truncate table command will aid to portability

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message