| Subject | Re: [firebird-support] Re: delayed suspend |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-06-04T23:46:47Z |
At 02:38 AM 5/06/2009, Peppe wrote:
If an exception occurs during an iteration of the loop, the SUSPEND doesn't happen and execution passes to the nearest exception handler.
-- If the loop doesn't provide its own exception handler, the loop stops executing and control passes to the next exception handler....and so on...but the engine has no way to *recall* output rows that have already been fetched or cached by the client.
-- If the loop does provide its own exception handler that just swallows the exception without passing control beyond the loop then execution of the loop resumes.
Of course, if the client included an ORDER BY, GROUP BY or DISTINCT in its SELECT call then, during the SUSPEND, the output rows pass from the procedure's output buffer to a SORT buffer, either in memory or on disk. In these conditions, there is nothing available to return to the client until *after* the entire set has reached the sort buffer.
If the loop dies from an unhandled exception before the entire set reaches the sort buffer then that intermediate set in the sort buffer becomes invalid and the client will never receive any output.
This is the situation apparently with *any* SSP output set in Oracle, ordered or not, and which it seems they (justifiably) want to rectify. It won't be the first time Oracle has pocketed a good idea from Firebird.
;-)
./heLen
>Ivan,No need. In isolation from a context, that statement was wrong in 2003 and it is still wrong six years later.
>
>iron that out with Helen Borrie :)
>
>http://tech.groups.yahoo.com/group/firebird-support/message/29774
>Also, anybody can experiment that SUSPEND is delayed until the very end.SUSPEND happens every time it is called. SUSPEND actually causes the output row to be sent to the output buffer and *suspend further execution* until the caller fetches it. If your application code is fetching the contents of that buffer into its own buffer (as would be the case for a Delphi dataset, unless the FOR UPDATE keyphrase were included in the call) then the SUSPEND ends once the row is fetched into the client buffer and the SP is able to continue executing.
If an exception occurs during an iteration of the loop, the SUSPEND doesn't happen and execution passes to the nearest exception handler.
-- If the loop doesn't provide its own exception handler, the loop stops executing and control passes to the next exception handler....and so on...but the engine has no way to *recall* output rows that have already been fetched or cached by the client.
-- If the loop does provide its own exception handler that just swallows the exception without passing control beyond the loop then execution of the loop resumes.
Of course, if the client included an ORDER BY, GROUP BY or DISTINCT in its SELECT call then, during the SUSPEND, the output rows pass from the procedure's output buffer to a SORT buffer, either in memory or on disk. In these conditions, there is nothing available to return to the client until *after* the entire set has reached the sort buffer.
If the loop dies from an unhandled exception before the entire set reaches the sort buffer then that intermediate set in the sort buffer becomes invalid and the client will never receive any output.
This is the situation apparently with *any* SSP output set in Oracle, ordered or not, and which it seems they (justifiably) want to rectify. It won't be the first time Oracle has pocketed a good idea from Firebird.
;-)
./heLen