Capturing a context stack
Getting the stack context, which is like a stack trace in other languages, is a powerful way to debug complex and nested functions.
In the code below, we want to capture the stack context and record it in the exception handlers of both nested blocks. Then, we want to review its output in the errors table to help debug what's causing the exception in this function.
This exercise is part of the course
Transactions and Error Handling in PostgreSQL
Exercise instructions
- Declare a text variable,
exc_context
, to hold the stack context. - Store the
PG_EXCEPTION_CONTEXT
in ourexc_context
variable in the first handler. - Store the
PG_EXCEPTION_DETAIL
in ourexc_detail
variable in the second handler. - Record both the error message and the stack context in both blocks.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
DO $$
DECLARE
exc_message text;
exc_details text;
-- Declare a variable, exc_context to hold the exception context
___ ___;
BEGIN
BEGIN
INSERT INTO patients (a1c, glucose, fasting) values (5.6, 93, TRUE),
(6.3, 111, TRUE),(4.7, 65, TRUE);
EXCEPTION
WHEN others THEN
-- Store the exception context in exc_context
GET STACKED DIAGNOSTICS exc_message = MESSAGE_TEXT,
___ = ___;
-- Record both the msg and the context
INSERT INTO errors (msg, context)
VALUES (___, ___);
END;
BEGIN
UPDATE patients set fasting = 'true' where id=1;
EXCEPTION
WHEN others THEN
-- Store the exception detail in exc_details
GET STACKED DIAGNOSTICS exc_message = MESSAGE_TEXT,
___ = ___;
INSERT INTO errors (___, ___)
VALUES (exc_message, exc_context);
END;
END$$;
SELECT * FROM errors;