Waiting for 9.2 – Trigger Depth

On 25th of January, Alvaro Herrera committed patch:

Add pg_trigger_depth() function
 
This reports the depth level of triggers currently in execution, or zero
if not called from inside a trigger.
 
No catversion bump in this patch, but you have to initdb if you want
access to the new function.
 
Author: Kevin Grittner

Sometimes, when you use triggers, you mind end up in a loop.

Triggered action runs query that triggers the same action.

For example. Let's assume you have a table:

$ \d people
                            TABLE "public.people"
   COLUMN    │  TYPE   │                      Modifiers
─────────────┼─────────┼─────────────────────────────────────────────────────
 id          │ INTEGER │ NOT NULL DEFAULT NEXTVAL('people_id_seq'::regclass)
 username    │ text    │
 best_friend │ INTEGER │
Indexes:
    "people_pkey" PRIMARY KEY, btree (id)
Foreign-KEY constraints:
    "people_best_friend_fkey" FOREIGN KEY (best_friend) REFERENCES people(id)
Referenced BY:
    TABLE "people" CONSTRAINT "people_best_friend_fkey" FOREIGN KEY (best_friend) REFERENCES people(id)

We might want to make sure that “best friend" is symmetric – i.e. when I am your best friend, you are my best friend too.

Which means that whenever we do:

UPDATE people SET best_friend = 123 WHERE id = 234;

we also need to run:

UPDATE people SET best_friend = 234 WHERE id = 123;

This particular example can be solved in other ways too, but it is simple enough to show the problem and solution without too much hassle.

For our update, we could write this trigger:

CREATE OR REPLACE FUNCTION symmetric_best_friend() RETURNS TRIGGER AS
$BODY$
BEGIN
    UPDATE people SET best_friend = NEW.id WHERE id = NEW.best_friend;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER symmetric_best_friend AFTER UPDATE ON people FOR EACH ROW EXECUTE PROCEDURE symmetric_best_friend();

But if I'll run it:

$ UPDATE people SET best_friend = 123 WHERE id = 234;
ERROR:  stack depth LIMIT exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."people" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"
SQL statement "UPDATE people SET best_friend = NEW.id WHERE id = NEW.best_friend"
PL/pgSQL function symmetric_best_friend() line 3 at SQL statement
SQL statement "UPDATE people SET best_friend = NEW.id WHERE id = NEW.best_friend"
PL/pgSQL function symmetric_best_friend() line 3 at SQL statement
SQL statement "UPDATE people SET best_friend = NEW.id WHERE id = NEW.best_friend"
PL/pgSQL function symmetric_best_friend() line 3 at SQL statement
SQL statement "UPDATE people SET best_friend = NEW.id WHERE id = NEW.best_friend"
...

Reason is pretty simple – update from trigger runs trigger again, in other direction.

Before 9.2 we had to either write smarter SQL (which is a good thing always) or employ strange tricks when smarter SQL was not really an option.

Now, I can change the code for function to:

CREATE OR REPLACE FUNCTION symmetric_best_friend() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF pg_trigger_depth() <> 1 THEN
        RETURN NEW;
    END IF;
    UPDATE people SET best_friend = NEW.id WHERE id = NEW.best_friend;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';

and now the update will work just fine:

$ SELECT * FROM people;
 id  │ username │ best_friend
─────┼──────────┼─────────────
 123 │ a        │      [NULL]
 234 │ b        │      [NULL]
(2 ROWS)
 
$ UPDATE people SET best_friend = 123 WHERE id = 234;
UPDATE 1
 
$ SELECT * FROM people;
 id  │ username │ best_friend
─────┼──────────┼─────────────
 234 │ b        │         123
 123 │ a        │         234
(2 ROWS)

Of course if you'd like, for whatever reason, to allow trigger-called-by-trigger, but disallow further recurrence – you can do that too, by changing the IF pg_trigger_depth() condition.

2 thoughts on “Waiting for 9.2 – Trigger Depth”

  1. Handy! My current solution for trigger depth is to use a separate table to store and query the status of an active trigger. This solution is much cleaner, as I won’t need that silly extra table anymore.

Comments are closed.