Prevent recursive trigger in PostgreSQL
Solution 1
In pg, it's up to you to track trigger recursion.
If a trigger function executes SQL commands then these commands might fire triggers again. This is known as cascading triggers. There is no direct limitation on the number of cascade levels. It is possible for cascades to cause a recursive invocation of the same trigger; for example, an INSERT trigger might execute a command that inserts an additional row into the same table, causing the INSERT trigger to be fired again. It is the trigger programmer's responsibility to avoid infinite recursion in such scenarios.
https://www.postgresql.org/docs/13/trigger-definition.html
Solution 2
This is what I do in PostgreSQL 9.2, although I must admit I did not find this approach documented. There is a function pg_trigger_depth()
documented here, which I use to differentiate between original and nested calls in the trigger.
CREATE TRIGGER trg_taxonomic_positions
AFTER INSERT OR UPDATE OF taxonomic_position
ON taxon_concepts
FOR EACH ROW
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE trg_taxonomic_positions()
Solution 3
At the beggining of the definition of the trigger you can disable triggers on that particular table, and reenable them at the end (and make sure an exception doesn't terminate the execution before expected!). This has many deep holes, but may work for some light implementations. Notice that for this implementation, you will also need priviliges to disable triggers.
Solution 4
To avoid unbounded recursion, see my answer here. As others have commented, if your data structure is a true tree (the root(s) will have no parent(s)) and the recursion will always stop at the root(s). For nodes with only one parent pointer, the only way for unbounded recursion would be if there were loops present. (the method in my link will visit any node at most once)
Hao
Updated on June 16, 2022Comments
-
Hao almost 2 years
How to prevent recursive execution of trigger? Let's say I want to construct a "tree-able" description on chart of account. So what I do is when a new record is inserted/updated, I update the the parent record's
down_qty
, so this would trigger the update trigger recursively.Right now, my code is ok - I put this on
UPDATE
trigger's first line:-- prevents recursive trigger if new.track_recursive_trigger <> old.track_recursive_trigger then return new; end if;
And this is the sample code from my trigger when I need to update the parent record's qty:
update account_category set track_recursive_trigger = track_recursive_trigger + 1, -- i put this line to prevent recursive trigger down_qty = down_qty - (old.down_qty + 1) where account_category_id = m_parent_account;
I'm thinking if there's a way in PostgreSQL to detect recursive trigger without introducing a new field, something analogous to MSSQL's
trigger_nestlevel
.[EDIT]
I loop inside the tree, I need to bubble up the
down_qty
of eachaccount_category
back to its root. For example, I insert a new account category, it needs to increment thedown_qty
of its parentaccount_category
, likewise when I change the account category's parentaccount_category
, I need to decrement thedown_qty
ofaccount_category
's previous parentaccount_category
. Though I think it can, I'm not letting PostgreSQL do the recursive trigger. I used MSSQL before where the trigger recursive depth level is limited only up to16
levels.