[
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.
