db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel John Debrunner (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3370) Derby should not support commit/rollback inside a SQL-invoked external function.
Date Thu, 31 Jan 2008 22:01:39 GMT

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

Daniel John Debrunner commented on DERBY-3370:

I was trying to understand the scope of the impact, if applications today are happily working
with commit/rollback in a function then why would we restrict it?

On the other hand if such applications just appear to be working, but might actually be causing
issues then there is justification to disallow commit/rollback in a function. So I was wondering
if any analysis had been performed of which situations a commit() would be troublesome and
which would be ok.

I think probably a simple single rule of no commit/rollback in a function is best, rather
than allowing it in a number of limited cases, but I'd like to see how wide an impact such
a rule would have. E.g. if it only causes issues for non-holdable result sets in a SELECT
then that's a minor subset of the places a function can be used.

> 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
> The P above is the program identified by the external name of R, where R is in an external
> 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.

View raw message