DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 0Z002 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 0Z002: Stacked Diagnostics Accessed Without Active Handler

PostgreSQL error 0Z002 occurs when GET STACKED DIAGNOSTICS is called outside of an active exception handler (EXCEPTION block) in PL/pgSQL. This statement is exclusively designed to retrieve exception context information—such as error messages, SQL states, and stack traces—and is only valid when an exception is actively being handled. If you call it anywhere else in your function body, PostgreSQL raises this error immediately.

Top 3 Causes

1. Calling GET STACKED DIAGNOSTICS Outside an EXCEPTION Block

This is the most common cause. Developers mistakenly place the statement in the main body of a function instead of inside the EXCEPTION handler.

-- WRONG: causes 0Z002
CREATE OR REPLACE FUNCTION bad_func()
RETURNS void AS $$
DECLARE
    v_message TEXT;
BEGIN
    -- This will raise 0Z002 immediately
    GET STACKED DIAGNOSTICS v_message = MESSAGE_TEXT;
    RAISE NOTICE '%', v_message;
END;
$$ LANGUAGE plpgsql;

-- CORRECT: GET STACKED DIAGNOSTICS inside EXCEPTION block
CREATE OR REPLACE FUNCTION good_func()
RETURNS void AS $$
DECLARE
    v_message TEXT;
    v_context TEXT;
BEGIN
    PERFORM 1 / 0; -- trigger an exception

EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS
            v_message = MESSAGE_TEXT,
            v_context = PG_EXCEPTION_CONTEXT;
        RAISE NOTICE 'Error: %, Context: %', v_message, v_context;
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

2. Incorrect Scope in Nested Blocks

When using nested BEGIN...END blocks, it's easy to lose track of which block's exception handler is active. Calling GET STACKED DIAGNOSTICS in an outer block's normal execution area after handling an inner exception is a common mistake.

-- CORRECT: scoped properly within the inner EXCEPTION block
CREATE OR REPLACE FUNCTION nested_example()
RETURNS void AS $$
DECLARE
    v_message TEXT;
BEGIN
    RAISE NOTICE 'Outer block running';

    BEGIN
        -- inner block triggers exception
        PERFORM 1 / 0;

    EXCEPTION
        WHEN division_by_zero THEN
            -- correct: inside the active handler
            GET STACKED DIAGNOSTICS v_message = MESSAGE_TEXT;
            RAISE NOTICE 'Caught inner error: %', v_message;
    END;

    -- outer block continues normally here
    RAISE NOTICE 'Outer block continues';
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

3. Confusing GET DIAGNOSTICS with GET STACKED DIAGNOSTICS

GET DIAGNOSTICS is for general runtime information (e.g., row counts) and can be used anywhere. GET STACKED DIAGNOSTICS is exclusively for exception context. Mixing them up leads directly to 0Z002.

CREATE OR REPLACE FUNCTION diagnostics_example()
RETURNS void AS $$
DECLARE
    v_rows    BIGINT;
    v_message TEXT;
BEGIN
    UPDATE my_table SET status = 'active' WHERE id = 1;

    -- GET DIAGNOSTICS: valid outside EXCEPTION block
    GET DIAGNOSTICS v_rows = ROW_COUNT;
    RAISE NOTICE 'Rows updated: %', v_rows;

EXCEPTION
    WHEN OTHERS THEN
        -- GET STACKED DIAGNOSTICS: only valid here
        GET STACKED DIAGNOSTICS v_message = MESSAGE_TEXT;
        RAISE WARNING 'Error occurred: %', v_message;
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Always place GET STACKED DIAGNOSTICS inside an EXCEPTION WHEN ... THEN block.
  • Use GET DIAGNOSTICS (without STACKED) for runtime context like ROW_COUNT outside exception handlers.
  • For complex functions, use a dedicated error logging pattern: 
CREATE OR REPLACE FUNCTION safe_operation()
RETURNS void AS $$
DECLARE
    v_state   TEXT;
    v_message TEXT;
    v_context TEXT;
BEGIN
    -- your business logic here
    PERFORM risky_function();

EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS
            v_state   = RETURNED_SQLSTATE,
            v_message = MESSAGE_TEXT,
            v_context = PG_EXCEPTION_CONTEXT;

        INSERT INTO error_log(sql_state, message, context, logged_at)
        VALUES (v_state, v_message, v_context, NOW());

        RAISE; -- re-raise if needed
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Use plpgsql_check: Install the plpgsql_check extension and run SELECT * FROM plpgsql_check_function('your_func()'); as part of your CI/CD pipeline to catch misplaced diagnostics calls before deployment. 
CREATE EXTENSION IF NOT EXISTS plpgsql_check;
SELECT * FROM plpgsql_check_function('safe_operation()');
Enter fullscreen mode Exit fullscreen mode
  • Adopt a standard function template: Establish a team-wide PL/pgSQL template where GET STACKED DIAGNOSTICS always lives inside the EXCEPTION block. This structural habit eliminates the error class entirely and enforces consistent error handling across your codebase.

Related Error Codes

  • 0Z000 — Parent class diagnostics_exception, the broader category this error belongs to.
  • P0000 — General plpgsql_error for PL/pgSQL runtime issues.
  • P0001 — raise_exception, commonly seen alongside stacked diagnostics usage.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)