Is pg_trigger_depth() bad to use for preventing trigger cascading (recursion)? Why is pg_trigger_depth() = 0 bad to use (for anything other than debugging)  when preventing trigger cascading (recursion)?

Can someone provide code to demonstrate why it is bad?

I am guessing because if multiple triggers are working on the same data at the same time a condition that stops a trigger using pg_trigger_depth() = 0 will stop any trigger that is second in line to do work.

I thought it would be a good solution to this (my) question here, but I am told otherwise:


Within a trigger is there a way to tell if an update or insert came from a trigger or not?


Thought it would make a good question.

It is offered here as a solution:


Prevent recursive trigger in PostgreSQL


Postgres 9.3 documentation:

https://www.postgresql.org/docs/9.3/static/functions-info.html
It is not bad to use per se (IMHO). You just need to be aware of implications.

I'd rather make it pg_trigger_depth() &lt; 1 instead of pg_trigger_depth() = 0, on principal, but that's not important here. We are talking about triggers like:

CREATE TRIGGER my_trigger
AFTER INSERT ON my_tbl
FOR EACH ROW
WHEN (pg_trigger_depth() &lt; 1)
EXECUTE PROCEDURE my_trigger_func();


If you later add triggers that would in turn fire your trigger, nothing will happen instead. Your trigger only reacts to non-trigger events. Depending on your setup this may be exactly what you want - or kind of a trap, if you forget about this and later add more triggers that should in turn (also) fire this trigger. You are changing default behavior. Be sure to document this clearly.

Be aware that pg_trigger_depth() counts any trigger, not only the same trigger to prevent recursion, so the condition prevents the trigger from firing if called from any other trigger, too.
The manual:


  current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger)


Related:


How to prevent a PostgreSQL trigger from being fired by another trigger?