1. Hints to help handle nested exceptions
Previously, we discussed using nested blocks to emulate savepoints and handle exceptions; however, we didn't consider how to pinpoint which block caused the exception. For small code blocks, a custom exception can work great and be all one needs. Nonetheless, for large code blocks, adding the PG_EXCEPTION_CONTEXT to the exception handler to get the stack can be a massive plus to both debugging and pinpointing an exception.
2. Emulating savepoints with nested blocks
Let's take a look back at a previous example we built. Here we have two nested blocks setting the prior for different cookies in our inventory table. Block 1 worked as expected.
3. Emulating savepoint Block 2
However, block 2 shown here throws an exception because we set the Macaron's cost to 35, which is higher than the max allowed of 10. Let's rework this using stacked diagnostics.
4. Nested blocks with stacked diagnostics
Here is a full view of using nested blocks with stacked diagnostics, you'll want to memorize the whole thing. Just kidding, let's break this structure into the individual blocks that make it up.
5. Nested blocks with stacked diagnostics
Remember that we start by declaring text variables to hold the parts of the diagnostic information we want. Here, in addition to declaring variables for the message and detail, we also add one for the context, which is a stack trace of the error.
6. Nested blocks with stacked diagnostics: block 2
We then move down to the exception handler of block one and make the call to get stacked diagnostics to access the MESSAGE_TEXT, PG_EXCEPTION_DETAIL, and PG_EXCEPTION_CONTEXT. We adjust our error recording and logging statements to use the exception context as well.
7. Nested blocks with stacked diagnostics: block 2
In block 2 of the function, we do not need to declare any variables since we added the declare block to the outer function's scope. We do need to repeat the call to get stack diagnostics and adjustments to the record and logging statements. Let's take a look at the results of our work.
8. Results
When we run our code, we get a RAISE INFO message just as we expect. If we look closer at our error message, we can see in the context the exact SQL statement and line number that caused the exception. This combination will help you pinpoint exactly where the exception occurred.
9. Custom exception handling vs stacked diagnostics
Remember, exception handling adds computational overhead to our SQL code and adding stacked diagnostics onto that adds even more. That makes knowing when to use stacked diagnostics instead of simple exception handling more valuable. In general, if you can get a clear understanding of what happened from an exception handler without stacked diagnostics, you shouldn't use them. Likewise, if it's an expected error condition, stacked diagnostics won't help with any new understanding. However, another good reason to use custom exception handling is to add your exception messages that are clearer and contain more domain-specific information. Such as some of the exception messages you created for A1C checks. Nonetheless, stacked diagnostics are compelling tools when debugging and generalizing exception handling.
10. Let's practice!
Time to get some hands on practice with context.