Why trigger recursion happens
Trigger recursion typically starts with a well-intentioned but flawed pattern: a trigger on table T executes an UPDATE on table T, which fires the trigger again. Each recursive invocation pushes another function call onto the stack until PostgreSQL aborts with stack depth limit exceeded. The error context shows the same lines repeating, which is the telltale signature of unbounded recursion.
A classic example is maintaining an updated_at column. A column default sets the value on INSERT, but an UPDATE won't change it. The naive fix is a trigger function that performs its own UPDATE:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN UPDATE data SET updated_at = current_timestamp WHERE data.id = NEW.id; RETURN NEW; END;$$; CREATE TRIGGER set_updated_at AFTER UPDATE ON data FOR EACH ROW EXECUTE FUNCTION set_updated_at(); |
That fires the trigger again on the new update, and the recursion never terminates. Raising stack depth limit only delays the inevitable error.
Avoiding recursion with a BEFORE trigger
The correct approach for this case is a BEFORE trigger that modifies the NEW row in place before it is written. Since the row is not yet in the table, no second update occurs and no recursion starts:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN NEW.updated_at := current_timestamp; RETURN NEW; END;$$; CREATE TRIGGER set_updated_at before UPDATE ON data FOR EACH ROW EXECUTE FUNCTION set_updated_at(); |
The RETURN NEW; is required; without it, PostgreSQL discards the modified row. This pattern also avoids a second dead tuple. Because PostgreSQL uses multi-version concurrency control, every UPDATE creates a dead tuple that VACUUM must later clean up. A trigger-generated second update doubles that overhead.
A realistic recursion scenario
Sometimes recursion is harder to eliminate. Consider a public health database with two tables: one for companies and one for workers. Each worker has a status, including quarantined:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE address ( id bigint PRIMARY KEY, street text, zip text NOT NULL, city text NOT NULL ); CREATE TABLE worker ( id bigint PRIMARY KEY, name text NOT NULL, quarantined boolean NOT NULL, address_id bigint REFERENCES address ); INSERT INTO address VALUES (101, 'Römerstraße 19', '2752', 'Wöllersdorf'), (102, 'Heldenplatz', '1010', 'Wien'); INSERT INTO worker VALUES (1, 'Laurenz Albe', FALSE, 101), (2, 'Hans-Jürgen Schönig', FALSE, 101), (3, 'Alexander Van der Bellen', FALSE, 102); |
Suppose a law requires that if any worker at an address is quarantined, all other workers at that address must be quarantined too. Enforcing this with a trigger keeps data consistent regardless of which application performs the update:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE FUNCTION quarantine_coworkers() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN IF NEW.quarantined IS TRUE THEN UPDATE worker SET quarantined = TRUE WHERE worker.address_id = NEW.address_id AND worker.id <> NEW.id; END IF; RETURN NEW; END;$$; CREATE TRIGGER quarantine_coworkers AFTER UPDATE ON worker FOR EACH ROW EXECUTE FUNCTION quarantine_coworkers(); |
The logic is sound, but as soon as two workers share an address, the trigger fires, updates the other worker, which fires the trigger again, which updates the original worker, and so on endlessly.
Stopping recursion with a WHERE condition
One straightforward fix is to make the trigger's UPDATE idempotent by adding a condition that skips rows already in the desired state:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE OR REPLACE FUNCTION quarantine_coworkers() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN IF NEW.quarantined IS TRUE THEN UPDATE worker SET quarantined = TRUE WHERE worker.address_id = NEW.address_id AND worker.id <> NEW.id AND NOT worker.quarantined; END IF; RETURN NEW; END;$$; |
When worker 1 is updated, the trigger updates worker 2 to quarantined, which fires the trigger again. This time, every worker at that address is already quarantined, so the inner UPDATE affects no rows and recursion stops.
Using pg_trigger_depth() as a safeguard
When adding a WHERE condition is not practical, PostgreSQL offers pg_trigger_depth(). This function returns the current trigger nesting level and can be called from within a trigger function:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE OR REPLACE FUNCTION quarantine_coworkers() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN IF NEW.quarantined IS TRUE AND pg_trigger_depth() < 2 THEN UPDATE worker SET quarantined = TRUE WHERE worker.address_id = NEW.address_id AND worker.id <> NEW.id AND NOT worker.quarantined; END IF; RETURN NEW; END;$$; |
This stops the recursion after the first re-entry. However, the trigger function is still invoked a second time, even though it immediately returns without doing work.
Conditional trigger invocation with WHEN
To avoid paying for a fruitless function call, use the WHEN clause of CREATE TRIGGER. This evaluates before the function runs, so recursion can be halted before the trigger fires again:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DROP TRIGGER quarantine_coworkers ON worker; CREATE OR REPLACE FUNCTION quarantine_coworkers() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN UPDATE worker SET quarantined = TRUE WHERE worker.address_id = NEW.address_id; AND worker.id <> NEW.id AND NOT worker.quarantined; RETURN NEW; END;$$; CREATE TRIGGER quarantine_coworkers AFTER UPDATE ON worker FOR EACH ROW WHEN (NEW.quarantined AND pg_trigger_depth() < 2) EXECUTE FUNCTION quarantine_coworkers(); |
With this definition, a second invocation is never started when all workers at the address are already quarantined, which eliminates the extra function call overhead entirely. Expressing recursion guards in the WHEN clause rather than in the function body is measurably faster.
Key takeaways
The simplest trigger recursion problems are avoided by restructuring the trigger to act on the NEW row in a BEFORE trigger. When recursion cannot be fully avoided, guard it with an idempotent WHERE condition or with pg_trigger_depth(). For the best performance, place the guard in the trigger's WHEN clause so the function is not called at all when recursion would be pointless.
