debugging postgresql trigger

Use the following code within a trigger function, then watch the ‘messages’ tab in pgAdmin3 or the output in psql:

RAISE NOTICE 'myplpgsqlval is currently %', myplpgsqlval;       -- either this
RAISE EXCEPTION 'failed';  -- or that

To see which triggers actually get called, how many times etc, the following statement is the life-saver of choice:

EXPLAIN ANALYZE UPDATE table SET foo='bar'; -- shows the called triggers

Note that if your trigger is not getting called and you use inheritance, it may be that you’ve only defined a trigger on the parent table, whereas triggers are not inherited by child tables automatically.

To step through the function, you can use the debugger built into pgAdmin3, which on Windows is enabled by default; all you have to do is execute the code found in …\8.3\share\contrib\pldbgapi.sql against the database you’re debugging, restart pgAdmin3, right-click your trigger function, hit ‘Set Breakpoint’, and then execute a statement that would cause the trigger to fire, such as the UPDATE statement above.