WL#6406: Stacked diagnostic areas
Affects: Server-5.7 — Status: Complete
Implement support for stacked diagnostic areas according to the SQL-standard. This includes updating current implementation of HANDLER statements, RESIGNAL and stored program execution. Also extend GET DIAGNOSTICS to support GET STACKED DIAGNOSTICS.
Currently every connection has one diagnostic area for storing completion and exception condition information. According to the SQL standard each connection should instead have a stack of diagnostic areas. The diagnostic area on top of the stack is called the first diagnostic area. The diagnostic area just below is the second diagnostic area. There can be more than just two diagnostic areas, but only the top two are accessible. Two operations are defined for the diagnostics area stack: Push and pop. Pushing a diagnostic area makes it the new first diagnostic area. The previous first diagnostic area becomes the second diagnostic area. The new first diagnostic area initially contains a copy of the contents of the second diagnostic area (i.e. the first diagnostic area before the push). Popping a diagnostic area, destroys it and makes the second diagnostic area the new first diagnostic area. Note that pop does not by definition merge the contents of the first and second diagnostics area - all conditions in the first area are destroyed. Normally all interactions happen with the first diagnostic area. This is where new conditions appear. The contents of the first diagnostic area can be queried with GET [CURRENT] DIAGNOSTICS. This worklog will add support for GET STACKED DIAGNOSTICS which queries the contents of the second diagnostic area in a similar manner. There is no way to access diagnostic areas below the second (this is according to the SQL standard). Pushing and popping of diagnostic areas happens in the following 3 cases: 1) Execution of a stored program -------------------------------- Push before executing a stored program and pop afterwards. Note that there can be more than one diagnostic area to pop if the stored program ends while handlers are executing (due to an exception for which there are no appropriate handlers or due to RETURN in the handler). Any warnings or errors during execution is then added to the current diagnostics area. Exception: For triggers, only errors are added. See BUG#55850 TRIGGER WARNINGS NOT CLEARED 2) Execution of SP handlers --------------------------- Push before executing a SP handler and pop if the handler exits successfully. Handler activation and execution will be like this: - A condition is raised during SP execution. It is entered into the first DA - let's call this the SP DA. - Handler is activated and a new DA is pushed. Let's call this new DA the handler DA. Stack is now handler DA (top) and SP DA (below) and the both contain the same conditions. - Handler executes. Normal clearing rules applies to the first (handler) DA. Also as normal, new conditions that are raised are entered into the first (handler) DA. - If handler ends successfully: Pop DA. This means that SP DA is now again the first DA. Since the handler executed successfully, all conditions that were present when the handler was activated, are cleared. This in effect means emptying the SP DA since it was read-only during the handler. Then we examine the contents of the handler DA as it is at the end of the handler. Any conditions that were added during the handler execution, are copied to the SP DA. So at the end, we have just SP DA, and it contains only the conditions present in the handler DA when the handler ended that were added during handler execution. - If handler ends with exception: Add the exception to the first (handler) DA and activate an appropriate handler (if any). 3) Execution of RESIGNAL ------------------------ Pop at the start of RESIGNAL and push at the end of RESIGNAL. Note that the push is not part of the the SQL standard. However, if the handler continues to execute after RESIGNAL, we will get out of sync with respect to diagnostic areas unless we push at the end. Continuing after RESIGNAL can happen if we RESIGNAL a warning or if we RESIGNAL an error for which there's an outer CONTINUE handler that is appropriate. In other cases (RESIGNAL of error which is not handled and RESIGNAL of error which is handled by EXIT handler), we will pop the DA pushed at the end of RESIGNAL when we exit the handler scope (as normal). By doing pop+push rather than not doing either, we make sure that a condition modified by RESIGNAL (if any) is present in both the first and second diagnostics area when RESIGNAL ends. The diagnostics area pushed will be popped when the handler exits (as normal). We have discussed introducing a limit to the number of diagnostic areas in the diagnostic areas stack. The SQL standard (2008) says this: "The maximum number of diagnostics areas in a diagnostics area stack is implementation-dependent." and regarding pushing diagnostics area: "If the number of diagnostics areas in DAS is equal to the implementation- dependent maximum number of diagnostics areas per diagnostics area stack, then an exception condition is raised: diagnostics exception — maximum number of stacked diagnostics areas exceeded." However, we use push/pop a bit more than what is determined by the SQL standard (e.g. we push before executing a stored program). This means that the number of diagnostic areas (and therefore the meaning of the limit) would not be identical to that described in the SQL standard. It also means that it will be hard to explain properly what such a limit in fact means. The reason for introducing a limit would be to restrict memory usage. However, we already have "max_sp_recursion_depth" and "thread_stack" which addresses this issue. Therefore we don't plan to introduce any limit on number of diagnostics areas in the stack. Functional requirements ----------------------- 1. Syntax for GET DIAGNOSTICS is extended. GET [CURRENT] DIAGNOSTICS becomes GET [CURRENT | STACKED] DIAGNOSTICS. 2. The behavior of GET [CURRENT] DIAGNOSTICS is unchanged. 3. GET STACKED DIAGNOSTICS will retrieve information from the second diagnostic area in the same manner that GET [CURRENT] DIAGNOSTICS currently retrieves information from the first diagnostics area. The same syntax applies. 4. Issuing GET STACKED DIAGNOSTICS outside of a handler, will give the error "GET STACKED DIAGNOSTICS when handler not active" (matching the current "RESIGNAL when handler not active" error). 5. Right after a new diagnostics area has been pushed, the contents of the first and second diagnostics areas should be the same. This means that the result of matching GET [CURRENT] DIAGNOSTICS and GET STACKED DIAGNOSTICS queries should be the same. 6. The contents of the second diagnostics area should not change during SP handler execution. This means that a given GET STACKED DIAGNOSTICS query should give the same result at any point during SP handler execution, regardless of any changes to the first diagnostics area. An exception to this rule is if RESIGNAL is used to modify a condition and the handler continues to execute. This can happen with RESIGNAL of a warning or with RESIGNAL of an error for which there's an outer CONTINUE handler. 7. If a SP handler exits with RESIGNAL, the diagnostic area should at this point contain all the conditions it had when the handler was activated + the condition added by RESIGNAL.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.