Debugging PL/pgSQL: GET STACKED DIAGNOSTICS

09.2020 / Category: How To / Tags: development | sql help

PL/pgSQL is the preferred way to write stored procedures in PostgreSQL. Of course there are more languages to write code available but most people still use PL/pgSQL to get the job done. However, debugging PL/pgSQL code can be a bit tricky. Tools are around but it is still not a fun experience. One thing to make debugging easier is GET STACKED DIAGNOSTICS which is unfortunately not widely known. This post will show what it does and how you can make use of it.

Table of Contents

Debugging PostgreSQL stored procedures

To show you how GET STACKED DIAGNOSTICS worked I have written some broken code which executes a division by zero which is forbidden in any sane database:

CREATE OR REPLACE FUNCTION broken_function()
	RETURNS void AS $$
BEGIN
	SELECT 1 / 0;
END;
$$ LANGUAGE 'plpgsql';

1

2

3

4

5

6

CREATE OR REPLACE FUNCTION broken_function()

RETURNS void AS $$

BEGIN

SELECT 1 / 0;

END;

$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION simple_function()
	RETURNS numeric AS $$
DECLARE
BEGIN
	RAISE NOTICE 'crazy function called ...';
	PERFORM broken_function();
	RETURN 0;
END;
$$ LANGUAGE 'plpgsql';

1

2

3

4

5

6

7

8

9

CREATE OR REPLACE FUNCTION simple_function()

RETURNS numeric AS $$

DECLARE

BEGIN

RAISE NOTICE 'crazy function called ...';

PERFORM broken_function();

RETURN 0;

END;

$$ LANGUAGE 'plpgsql';

The question now is: How can we get a backtrace and debug the code? One way is to wrap the code into one more function call and see where things fail:

CREATE OR REPLACE FUNCTION get_stack() 
	RETURNS void AS $$
DECLARE
	v_sqlstate text;
	v_message text;
	v_context text;
BEGIN
  	PERFORM simple_function();

EXCEPTION WHEN OTHERS THEN 
	GET STACKED DIAGNOSTICS
		v_sqlstate = returned_sqlstate,
		v_message = message_text,
		v_context = pg_exception_context;
	RAISE NOTICE 'sqlstate: %', v_sqlstate;
	RAISE NOTICE 'message: %', v_message;
	RAISE NOTICE 'context: %', v_context;
END;
$$ LANGUAGE 'plpgsql';

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

CREATE OR REPLACE FUNCTION get_stack()

RETURNS void AS $$

DECLARE

v_sqlstate text;

v_message text;

v_context text;

BEGIN

PERFORM simple_function();

EXCEPTION WHEN OTHERS THEN

GET STACKED DIAGNOSTICS

v_sqlstate = returned_sqlstate,

v_message = message_text,

v_context = pg_exception_context;

RAISE NOTICE 'sqlstate: %', v_sqlstate;

RAISE NOTICE 'message: %', v_message;

RAISE NOTICE 'context: %', v_context;

END;

$$ LANGUAGE 'plpgsql';

My function catches the error causes by simple_function() and calls GET STACKED DIAGNOSTICS to display all the information we can possibly extract from the system. The output looks as follows:

test=# SELECT get_stack();
NOTICE: crazy function called ...
NOTICE: sqlstate: 22012
NOTICE: message: division by zero
NOTICE: context: SQL statement 'SELECT 1 / 0'
PL/pgSQL function broken_function() line 3 at SQL statement
SQL statement 'SELECT broken_function()'
PL/pgSQL function simple_function() line 5 at PERFORM
SQL statement 'SELECT simple_function()'
PL/pgSQL function get_stack() line 7 at PERFORM
get_stack
-----------

(1 row)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

test=# SELECT get_stack();

NOTICE: crazy function called ...

NOTICE: sqlstate: 22012

NOTICE: message: division by zero

NOTICE: context: SQL statement 'SELECT 1 / 0'

PL/pgSQL function broken_function() line 3 at SQL statement

SQL statement 'SELECT broken_function()'

PL/pgSQL function simple_function() line 5 at PERFORM

SQL statement 'SELECT simple_function()'

PL/pgSQL function get_stack() line 7 at PERFORM

get_stack

-----------

(1 row)

As you can see the stack trace is displayed and we can easily figure out where the problem has happened. In this case “broken_function” line 3 is the root cause of all evil.