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

Question

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;

• Craig Ringer: It might be a good idea to show a simple example here.
• Taytay: Good point @CraigRinger. Done!
• Craig Ringer: It's not self-contained. What's error_info? Looks like a custom type.
• Taytay: Sorry - thought you just wanted general context. I've removed the extraneous stuff.

Answer 1

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.)

• Taytay: Thank you very much Craig for the quick and thorough answer. It does seem odd to me, and certainly counter to my expectations. The usefulness of RAISE is diminished by that check. I'll write to them.
• Craig Ringer: @Taytay Please include a link to your question here, but do make sure your mail is complete and can be understood without following the link; many people ignore link-only or link-mostly posts. If you get the chance to pop a link to your post in the comments here, via archives.postgresql.org that'd be really awesome to help other people later.
• Taytay: Thanks Craig. Good advice. I created a thread here: postgresql.org/message-id/… As of now, they're looking for a good solution to the issue.

Answer 2

You can work around this restriction and make plpgsql emit error context as desired by calling another function that raises (warning, notice, ...) the error for you.

I posted a solution for that a couple of years back - in one of my first posts here on dba.SE:

-- helper function to raise an exception with CONTEXT
CREATE OR REPLACE FUNCTION f_raise(_lvl text = 'EXCEPTION'
 ,_msg text = 'Default error msg.')
 RETURNS void AS
$func$
BEGIN
 CASE upper(_lvl)
 WHEN 'EXCEPTION' THEN RAISE EXCEPTION '%', _msg;
 WHEN 'WARNING' THEN RAISE WARNING '%', _msg;
 WHEN 'NOTICE' THEN RAISE NOTICE '%', _msg;
 WHEN 'DEBUG' THEN RAISE DEBUG '%', _msg;
 WHEN 'LOG' THEN RAISE LOG '%', _msg;
 WHEN 'INFO' THEN RAISE INFO '%', _msg;
 ELSE RAISE EXCEPTION 'f_raise(): unexpected raise-level: "%"', _lvl;
 END CASE;
END
$func$ LANGUAGE plpgsql STRICT;

Details:

• Generate an exception with a Context

I expanded your posted test case to demonstrate it works in Postgres 9.3:

SQL Fiddle.

• Taytay: Thank you so much Erwin! Funnily enough, I actually experimented with your solution before posting, but I must have done something wrong and I didn't get the context I expected. Now that I've seen the fiddle (thanks for showing me that too), I'll give it another shot!
• Craig Ringer: Nicely done; it shouldn't be necessary, but looks like it'd do the trick.
• Erwin Brandstetter: @CraigRinger: Since exceptions should be, well, the exception, the minimal performance impact shouldn't matter, either. We have all options this way.
• Craig Ringer: Totally agree, I'd just like to see the need for the workaround go away at some point.
• Erwin Brandstetter: @CraigRinger: True. If that's not going to happen any time soon, we might suggest this workaround in the manual ...