debugging postgresql trigger

Question

I have this Trigger in Postgresql that I can't just get to work (does nothing). For understanding, there's how I defined it:

CREATE TABLE documents (
    ...
    modification_time timestamp with time zone DEFAULT now()
);

CREATE FUNCTION documents_update_mod_time() RETURNS trigger
AS $$
    begin
    new.modification_time := now();
    return new;
    end
$$
    LANGUAGE plpgsql;

CREATE TRIGGER documents_modification_time
    BEFORE INSERT OR UPDATE ON documents
    FOR EACH ROW
    EXECUTE PROCEDURE documents_update_mod_time();

Now to make it a bit more interesting.. How do you debug triggers?

Answer 1

1. 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
   

2. 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.

3. 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.

Answer 2

Turns out I was using inheritance in the above problem and forgot to mention it. Now for everybody who might run into this as well, here's some debugging hints:

Use the following code to debug what a trigger is doing:

RAISE NOTICE 'test';       -- 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

Then there's the one thing I didn't know before: triggers only fire when updating the exact table they're defined on. If you use inheritance, you MUST define them on the child tables as well!

Answer 3

You can use 'raise notice' statements inside your trigger function to debug it. To debug the trigger not being called at all is another story.

If you add a 'raise exception' inside your trigger function, can you still do inserts/updates?

Also, if your update test occurs in the same transaction as your insert test, now() will be the same (since it's only calculated once per transaction) and therefore the update won't seem to do anything. If that's the case, either do them in separate transactions, or if this is a unit test and you can't do that, use clock_timestamp().

I have a unit test that depends on some time going by between transactions, so at the beginning of the unit test I have something like:

ALTER TABLE documents
   ALTER COLUMN modification_time SET DEFAULT clock_timestamp();

Then in the trigger, use "set modification_time = default".

So normally it doesn't do the extra calculation, but during a unit test this allows me to do inserts with pg_sleep in between to simulate time passing and actually have that be reflected in the data.