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
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:
|
1 2 3 4 5 6 |
CREATE OR REPLACE FUNCTION broken_function() RETURNS void AS $ BEGIN SELECT 1 / 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:
|
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:
|
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.
If you want to learn more about PostgreSQL consider checking out one of my posts dealing with database performance.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply