DEBUGGER: Debug PL/pgSQL Stored Functions & Triggers

Overview

The DEBUGGER Extension is available as an optional component in our Linux, OSX & Windows distributions. Install it from BigSQL Manager UI or from the command line with PGC. You can step thru your PL/pgSQL code, set breakpoints, and assign variables. For understanding the performance of your PL/pgSQL code, take at look at using the profiler.

Initial Setup

First you must edit the postgresql.conf file and then re-start postgresql.

shared_preload_libraries = '$libdir/plugin_debugger'

Then you create the PLDEBUGGER extension


postgres=# CREATE EXTENSION pldbgapi;
CREATE EXTENSION

Sample Function

Consider the following PL/pgSQL function

CREATE FUNCTION pgmax (a INTEGER, b INTEGER)
  RETURNS INTEGER
AS $$
BEGIN
  IF  a > b THEN
    RETURN a;
  END IF;
  RETURN b;
END;
$$ LANGUAGE plpgsql;

Set Breakpoint

Use pgAdmin to Set a Breakpoint in the function:

Run Code

Run the function you want profiled from psql (or your application of course)


postgres=# SELECT pgmax(1,2);

Step Thru with Debugger

You're code will appear in pgAdmin Global Debugging window You can set breakpoints, step thru code, and assign variables.
 

Wow!! That was cool and simple. You may also set breakpoints and debug Triggers. Enjoy!