© Laurenz Albe 2024
Table of Contents
Many a beginner falls into the trap of trigger recursion at some point. Usually, the solution is to avoid recursion at all. But for some use cases, you may have to handle trigger recursion. This article tells you what you need to know about the topic. If you were ever troubled by the error message “stack depth limit exceeded”, here is the solution.
Triggers are the only good way to change data automatically. Constraints are the “policemen” that make sure rules are not voilated, but triggers are the workers that make the data stay in line. A beginner who has understood that may (quite correctly) wish to use a trigger to set the updated_at column in the following table:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE data ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, value text NOT NULL, updated_at timestamp with time zone DEFAULT current_timestamp NOT NULL ); |
The column default will set updated_at when the row is inserted, but won't change the value when you update the row. For that, our beginner writes a trigger:
|
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(); |
But that won't work as intended:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
INSERT INTO data (value) VALUES ('initial') RETURNING id; id ════ 1 (1 row) UPDATE data SET value = 'changed' WHERE id = 1; 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 "UPDATE data SET updated_at = current_timestamp WHERE data.id = NEW.id" PL/pgSQL function set_updated_at() line 2 at SQL statement SQL statement "UPDATE data SET updated_at = current_timestamp WHERE data.id = NEW.id" PL/pgSQL function set_updated_at() line 2 at SQL statement ... |
The last four lines of the error context keep repeating and indicate a recursion problem.
BEFORE triggerThe problem with the trigger is that it updates the same table whose update invoked the trigger in the first place. That triggers the same trigger again, and so on until there are so many recursive function calls on the stack that they exceed the limit. Different from most other cases, PostgreSQL's hint is not helpful here. Since the recursion is infinite, increasing the stack depth limit will only increase the time to the error message and the length on the error context.
Even if it didn't lead to infinite recursion, the above trigger would not be ideal. Owing to PostgreSQL's multi-version implementation, every update produces a “dead tuple”, which VACUUM has to clean up later. If the trigger performs a second update on the table row you just updated, that will generate a second dead tuple. That is inefficient, and you may need to tune autovacuum to cope with the additional workload.
The correct solution in PostgreSQL to avoid the second update and the infinite recursion is a BEFORE trigger that modifies the new row before it gets added to the table:
|
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 essential; read my article if you need an explanation.
The beginner's mistake above was easy to fix, and in most cases, it is easy to avoid such recursion with a little thought. But sometimes there are use cases for triggers where recursion is difficult to avoid. Imagine a public health database of working places and workers:
|
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); |
Each worker has a status “quarantined” (no, this article was not written during the pandemic).
Imagine law decrees that if one worker is quarantined, all people working at the same address be quarantined as well. It is best to implement data integrity rules like that with a trigger. Otherwise, data modifications performed outside the application could break the integrity of the data. Here is how such a trigger might look:
|
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(); |
That looks fundamentally correct, but you get trigger recursion as soon as there are more workers at a single address. The first trigger invocation will update the other workers at the same address, which will again invoke the trigger, updating the original worker a second time, and so forth ad infinitum:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
UPDATE worker SET quarantined = TRUE WHERE id = 1; 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 "laurenz"."address" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x" SQL statement "UPDATE worker SET quarantined = TRUE WHERE worker.address_id = NEW.address_id AND worker.id <> NEW.id" PL/pgSQL function quarantine_coworkers() line 3 at SQL statement SQL statement "UPDATE worker SET quarantined = TRUE WHERE worker.address_id = NEW.address_id AND worker.id <> NEW.id" PL/pgSQL function quarantine_coworkers() line 3 at SQL statement ... |
WHERE conditionIn the above case, you could fix the infinite recursion by adding another WHERE condition that avoids updating a row a second time:
|
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;$$; |
Now if I update the worker with id = 1, the trigger will update the worker with id = 2. This will call the trigger a second time, but all workers at that address are already quarantined, so the trigger won't update any row and recursion stops.
pg_trigger_depth()In our example, it was not difficult to avoid endless recursion with a WHERE condition. It may not always be that easy. There is another way to stop recursion: the function pg_trigger_depth(). This function is for use in trigger functions and returns the recursion level. We can use it as a safeguard to stop the recursion after the first level:
|
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;$$; |
WHEN clause for better performanceWith the above code, the trigger will still be called twice. The second time, the trigger funtion will return without doing anything, but we still have to pay the price of a second function call. The little-known WHEN clause in CREATE TRIGGER can make trigger invocation conditional and avoid that overhead:
|
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, recursion is stopped before the trigger function is called a second time, which will improve performance significantly.
We have seen how to avoid a beginner's error that causes infinite trigger recursion by avoiding recursion at all. In cases where we cannot avoid trigger recursion, we have seen how to use pg_trigger_depth() or a carefully designed additional condition can stop recursion at the right moment. We have also seen the WHEN clause of CREATE TRIGGER that can simplify the code and boost performance.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply