db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3370) Derby should not support commit/rollback inside a SQL-invoked external function.
Date Fri, 01 Feb 2008 08:09:09 GMT

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

Mamta A. Satoor commented on DERBY-3370:
----------------------------------------

>From my research on DERBY-3037, I believe the problem is only when holdability is false
for the Statement that is making the function call with commit/rollback inside the function
body. 

Today, in Derby, when the commit is issued, we call(in BaseActivation.reset()) close on the
Language Resultsets with holdability false. For Language Resultsets associated with functions
(those Resultsets are of the type RowResultSet which extend NoPutResultSetImpl), the close
method simply marks itself as isOpen=false;(NoPutResultSetImpl.close:line 184). It does not
call a close on the Activation associated with it. The closing of the Activation happens when
the JDBC Resultset is closed (EmbedResultSet.close:line 575). The close of JDBC Resultset
calls close on single use Activations which call finish on the Language Resultset.

With the patch DERBY3037_patch_not_ready_for_commit_v2_diff.txt attached to DERBY_3037, we
want the single use Acitvations to close when it's Language Resultset is getting closed. So,
when the commit issued, we call (in BaseActivation.reset()) close on the Language Resultsets
with holdability false. For Language Resultsets associated with functions (those Resultsets
are of the type RowResultSet which extend NoPutResultSetImpl), the close method (with patch
DERBY3037_patch_not_ready_for_commit_v2_diff.txt) now not only marks itself as isOpen=false
 but it also closes it's Activation if it is a single use Activation. The close of Activation
at this point ends up calling finish on Language Resultset. At this point, if the user tries
to move around in the JDBC Resultset associated with function call, it will result in problems
because the activation associated with it has already been closed.

So, to summarize, I think if we commit the cleanup changes attached to DERBY_3037 as patch
DERBY3037_patch_not_ready_for_commit_v2_diff.txt, we will run into problems with function
calls with commit/rollback inside them if the function call is associated with a Resultset
with holdability false.

> Derby should not support commit/rollback inside a SQL-invoked external function.
> --------------------------------------------------------------------------------
>
>                 Key: DERBY-3370
>                 URL: https://issues.apache.org/jira/browse/DERBY-3370
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>            Reporter: Mamta A. Satoor
>
> SQL foundation spec section 10.4<routine invocation> GR 8)f)ii)6)B) says 
> "If, before the completion of the execution of P, an attempt is made to execute an SQLtransaction
statement that is not <savepoint statement> or <release savepoint statement>,
or is a <rollback statement> that does not specify a <savepoint clause>, then
an exception condition is raised: external routine exception — prohibited SQL-statement
attempted." 
> The P above is the program identified by the external name of R, where R is in an external
routine. 
> The Part 13 of the SQL spec (which is specific to behavior of SQL-invoked routines which
are external and written in Java) does not include any modification to the general rule above.
(The place to check in Part 13 would be Section 8.3 <routine invocation> Page 34 and
couple pages after that.) 
> Based on these 2 specifications, Derby is not following SQL specification by allowing
commit and rollbacks inside SQL-invoked functions.
> A SQL-invoked function for instance can be called from a SELECT statement and SELECT
statement has resultset associated with it. If the SQL-invoked function does a commit inside
it, what should happen to the resultset associated with SELECT statement if the resultset
set is created with holdability false? Because of this, I do not think Derby should support
commit and rollback inside of a SQL-invoked function. 
> This behavior was discovered while researching on DERBY-3037. More information can be
found in comments for DERBY-3037 starting with Dan's comment on Jan 22nd 2008.

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