Waiting for 9.2 – Stacked Diagnostics in PL/pgSQL

On 18th of July, Tom Lane committed patch:

Add GET STACKED DIAGNOSTICS plpgsql command to retrieve exception info.
 
This is more SQL-spec-compliant, more easily extensible, and better
performing than the old method of inventing special variables.
 
Pavel Stehule, reviewed by Shigeru Hanada and David Wheeler

The problem that it solves is not very common one, but it is a very nifty feature for those of us that write triggers and functions in PostgreSQL.

Let's imagine a simple situation. Two tables:

     Table "public.test1"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null
Indexes:
    "test1_pkey" PRIMARY KEY, btree (id)
 
     Table "public.test2"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null
Indexes:
    "test2_pkey" PRIMARY KEY, btree (id)

But on test2, let's add simple trigger:

CREATE OR REPLACE FUNCTION test2_trg() RETURNS TRIGGER AS
$BODY$
DECLARE
    _id1 INT4;
BEGIN
    _id1 := NEW.id % 100;
    INSERT INTO test1(id) VALUES (_id1);
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER test2_trg AFTER INSERT ON test2 FOR EACH ROW EXECUTE PROCEDURE test2_trg();

So, whenever I'll insert row to test2, it will also insert row to test1, but with value of id being in range <0..99>

Let's see how it works:

$ INSERT INTO test2 (id) VALUES (1), (5), (120);
INSERT 0 3
 
$ SELECT * FROM test2;
 id
-----
   1
   5
 120
(3 ROWS)
 
$ SELECT * FROM test1;
 id
----
  1
  5
 20
(3 ROWS)

Now, let's imagine, that we want to have errors trapped from the inserts, so we write simple function that does the insert and catches error:

CREATE OR REPLACE FUNCTION test(INT4) RETURNS void AS $$
BEGIN
    BEGIN
        INSERT INTO test2 (id) VALUES ($1);
    EXCEPTION WHEN others THEN
        raise notice 'Got exception: state: %, message: %', SQLSTATE, SQLERRM;
    END;
    RETURN;
END;
$$ LANGUAGE plpgsql;

Another sanity check:

$ SELECT test( 10 );
 test
------
 
(1 ROW)
 
$ SELECT * FROM test1;
 id
----
  1
  5
 20
 10
(4 ROWS)
 
$ SELECT * FROM test2;
 id
-----
   1
   5
 120
  10
(4 ROWS)

It's all good. So, what will happen if I'll try to insert row that shouldn't be possible? There are two cases:

  • unique violation in test2 table (which by definition would also cause unique violation in test1, because of trigger, but violation in test2 should prevent the trigger from firing)
  • unique violation only in test1 – for example by inserting value “20" to test2 – which doesn't exist in test2, but it does in test1

Let's see what happens, in first case:

$ SELECT test( 10 );
psql:test.sql:39: NOTICE:  Got exception: state: 23505, message: duplicate KEY VALUE violates UNIQUE CONSTRAINT "test2_pkey"
 test
------
 
(1 ROW)

and in second:

SELECT test( 20 );
psql:test.sql:39: NOTICE:  Got exception: state: 23505, message: duplicate KEY VALUE violates UNIQUE CONSTRAINT "test1_pkey"
 test
------
 
(1 ROW)

Please note that the only thing that differs the error informations is the message. And this is only because my trigger did insert to another table. What would happen if it would insert to the same table? Or there could be multiple inserts to given table, and then the errors are indistinguishable.

But now, with this new patch situation is different.

We can change the test(INT4) function to:

CREATE OR REPLACE FUNCTION test(INT4) RETURNS void AS $$
DECLARE
    v_state   TEXT;
    v_msg     TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
BEGIN
    BEGIN
        INSERT INTO test2 (id) VALUES ($1);
    EXCEPTION WHEN others THEN
        GET STACKED DIAGNOSTICS
            v_state   = RETURNED_SQLSTATE,
            v_msg     = MESSAGE_TEXT,
            v_detail  = PG_EXCEPTION_DETAIL,
            v_hint    = PG_EXCEPTION_HINT,
            v_context = PG_EXCEPTION_CONTEXT;
        raise notice E'Got exception:
            state  : %
            message: %
            detail : %
            hint   : %
            context: %', v_state, v_msg, v_detail, v_hint, v_context;
    END;
    RETURN;
END;
$$ LANGUAGE plpgsql;

The notices will now look now:

SELECT test( 10 );
psql:test.sql:54: NOTICE:  Got exception:
            state  : 23505
            message: duplicate KEY VALUE violates UNIQUE CONSTRAINT "test2_pkey"
            detail : KEY (id)=(10) already EXISTS.
            hint   :
            context: SQL statement "INSERT INTO test2 (id) VALUES ($1)"
PL/pgSQL FUNCTION "test" line 10 at SQL statement
 test
------
 
(1 ROW)

and:

SELECT test( 20 );
psql:test.sql:54: NOTICE:  Got exception:
            state  : 23505
            message: duplicate KEY VALUE violates UNIQUE CONSTRAINT "test1_pkey"
            detail : KEY (id)=(20) already EXISTS.
            hint   :
            context: SQL statement "INSERT INTO test1(id) VALUES (_id1)"
PL/pgSQL FUNCTION "test2_trg" line 6 at SQL statement
SQL statement "INSERT INTO test2 (id) VALUES ($1)"
PL/pgSQL FUNCTION "test" line 10 at SQL statement
 test
------
 
(1 ROW)

Which means we now have full access to details, hints, and (what's most important) full context (a.k.a. stack trace).

Of course you don't have to get all diagnostics – you can choose which ones are important, and why.

This, plus added in 8.4 ability to raise exceptions with defined hint/detail fields, means that we can now easily distinguish between errors happening in different places. Great stuff.

One thought on “Waiting for 9.2 – Stacked Diagnostics in PL/pgSQL”

Comments are closed.