You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mamta A. Satoor (JIRA)" <ji...@apache.org> on 2008/01/31 18:47:11 UTC
[jira] Created: (DERBY-3370) Derby should not support
commit/rollback inside a SQL-invoked external function.
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.
[jira] Commented: (DERBY-3370) Derby should not support
commit/rollback inside a SQL-invoked external function.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3370?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12564516#action_12564516 ]
Mamta A. Satoor commented on DERBY-3370:
----------------------------------------
If Derby decides to disallow commit/rollback, the existing applications that use commit/rollback inside their functions will see exception along the line "external routine exception — prohibited SQL-statement attempted."
> 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.
[jira] Commented: (DERBY-3370) Derby should not support
commit/rollback inside a SQL-invoked external function.
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3370?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12564489#action_12564489 ]
Daniel John Debrunner commented on DERBY-3370:
----------------------------------------------
What's the impact on existing applications?
> 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.
[jira] Commented: (DERBY-3370) Derby should not support
commit/rollback inside a SQL-invoked external function.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3370?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12564682#action_12564682 ]
Mamta A. Satoor commented on DERBY-3370:
----------------------------------------
My analysis in the earlier comment is for a function call inside a SELECT statement. I haven't looked at what the impact might be on DML statements using a function that has commit/rollback inside the function.
> 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.
[jira] Commented: (DERBY-3370) Derby should not support
commit/rollback inside a SQL-invoked external function.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-3370) Derby should not support
commit/rollback inside a SQL-invoked external function.
Posted by "Tiago R. Espinha (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Tiago R. Espinha updated DERBY-3370:
------------------------------------
Urgency: Normal
Triaged for 10.5.2.
Assigned normal urgency.
> 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.
[jira] Commented: (DERBY-3370) Derby should not support
commit/rollback inside a SQL-invoked external function.
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ 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 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.