1. Enhancing exception handling with stacked diagnostics
So far, we've been defining our exception messages and details; however, PostgreSQL offers a way to capture a considerable amount of diagnostic information when an exception occurs.
2. Capturing more error information
To do this, we used a particular command in our exception handling block called GET STACKED DIAGNOSTICS. Stacked diagnostics immediately gives us access to the error stack as it was when the exception occurred. In a prior video, we used this exact code to captures an error that occurs when trying to update a cost beyond the check constraint of a maximum of 10. Let's transform this into using stacked diagnostics. To do this, we need to use a new concept in our DO functions.
3. Using stacked diagnostics
The first thing we need to do when building a function that uses stacked diagnostics is to set up some variables to store the part of the diagnostics we want to use. We use a DECLARE block to define our variable and types before the BEGIN block. Here we are defining two text variables exc_message and exc_detail, which are of the type text. All the stacked diagnostic variables are of the type text as of PostgreSQL 12.0.
With our variables declared, we turn our attention down to the exception handler, where right after we define what we are capturing, we make the call to GET STACKED DIAGNOSTICS and ask it to store the MESSAGE_TEXT in our exc_message variable and the PG_EXCEPTION_DETAIL in the exc_detail variable. Then we use those variables in our errors insert statement. We also use those variables in our RAISE INFO statement, which, if you look closely, you will now see we used percent signs as placeholders where our variables will end up in the message. We also added the variables to the RAISE line separated from the message and each other by commas. Now let's see what happens when we execute this.
4. Example diagnostic output
We get an INFO notice that the updated row for the inventory table violates our cost_check constraint.
Let's check our errors table to see that are diagnostics were also inserted in there; however, since these are long strings, I'm using the \x on command to enable expanded display, which gives us a friendly vertical record orientation.
Now we can see the same message and detail stored in the errors table. Fantastic, this is much better automatically generated detail about what is happening.
If you're like me, you got two questions. Why didn't we start with this, and what all data can I get from stacked diagnostics? We didn't start with this, because often a more expressive user created error message can call out specific known issues in a codebase better than the generic PostgreSQL messaging, and mixing both custom messages with data from the diagnostics stack is the best solution. Now about that second question...
5. So what all can you get?
You can capture a significant amount of data from diagnostics, here is the first half of the list list from the PostgreSQL 12 documentation. I tend to encourage the use of RETURNED_SQLSTATE, which is the PostgreSQL internal error code, MESSAGE_TEXT, and PG_EXCEPTION_DETAIL. Those three components are excellent for googling what PostgreSQL means by the error and what data or statement clause caused the exception.
6. More diagnostic datapoints
I also encourage the use of PG_EXCEPTION_CONTEXT when you have nested blocks or functions, as it can help you understand where the error occurred in the nesting.
7. Let's practice!
Alright, let's go get to work on some examples.