Postgresql – How to get exception context for a manually raised exception in PL/pgSQL

error handlingexceptionplpgsqlpostgresqlpostgresql-9.3

In Postgres, we get the "stack trace" of exceptions using this code:

EXCEPTION WHEN others THEN
    GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT;

This works fine for "natural" exceptions, but if we raise an exception using 

RAISE EXCEPTION 'This is an error!';

…then there is no stack trace. According to a mailing list entry, this might be intentional, although I can't for the life of me figure out why. It makes me want to figure out another way to throw an exception other than using RAISE. Am I just missing something obvious? Does anyone have a trick for this? Is there an exception I can get Postgres to throw that would contain a string of my choosing, so that I would get not only my string in the error message, but the full stack trace as well?

Here's a full example:

CREATE OR REPLACE FUNCTION error_test() RETURNS json AS $$
DECLARE
    v_error_stack text;
BEGIN

    -- Comment this out to see how a "normal" exception will give you the stack trace
    RAISE EXCEPTION 'This exception will not get a stack trace';

    -- This will give a divide by zero error, complete with stack trace
    SELECT 1/0;

-- In case of any exception, wrap it in error object and send it back as json
EXCEPTION WHEN others THEN

    -- If the exception we're catching is one that Postgres threw,
    -- like a divide by zero error, then this will get the full
    -- stack trace of the place where the exception was thrown.
    -- However, since we are catching an exception we raised manually
    -- using RAISE EXCEPTION, there is no context/stack trace!
    GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT;

    RAISE WARNING 'The stack trace of the error is: "%"', v_error_stack;

    return to_json(v_error_stack);
END;
$$ LANGUAGE plpgsql;

Best Answer

This behaviour appears to be by design.

In src/pl/plpgsql/src/pl_exec.c the error context callback explicitly checks to see if it's being called in the context of a PL/PgSQL RAISE statement and, if so, skips emitting the error context:

/*
 * error context callback to let us supply a call-stack traceback
 */
static void
plpgsql_exec_error_callback(void *arg)
{
        PLpgSQL_execstate *estate = (PLpgSQL_execstate *) arg;

        /* if we are doing RAISE, don't report its location */
        if (estate->err_text == raise_skip_msg)
                return;

I can't find any specific reference as to why that is the case.

Internally in the server, the context stack is generated by processing the error_context_stack, which is a chained callback that appends information to a list when called.

When PL/PgSQL enters a function it adds an item to the error context callback stack. When it leaves a function it removes an item from that stack.

If the PostgreSQL server's error reporting functions, like ereport or elog are called, it calls the error context callback. But in PL/PgSQL if it notices that it's being called from a RAISE its callbacks intentionally do nothing.

Given that, I don't see any way to achieve what you want without patching PostgreSQL. I suggest posting mail to pgsql-general asking why RAISE doesn't provide the error context now that PL/PgSQL has GET STACKED DIAGNOSTICS to make use of it.

(BTW, the exception context is not a stack trace as such. It looks a bit like one because PL/PgSQL adds each function call to the stack, but it's also used for other details in the server.)

Related Question