I wrote a trigger that update a new row in the same table.

l

When I update the fist row, my trigger tries to update all the relevant rows and I get an error "stack depth limit exceeded"

To increase this mimit is not a solution since my table will be bigger and bigger.

But I never need to update from the first rows the best solution is to limit the maximun of "loops" the trigger can do.

I don't know how to have a "loop counter" and I don't find any way to retrieve the real stack usage that should be another solution.

Any idea regarding this problem?

CodePudding user response：

You can use pg_trigger_depth(), but I strongly suggest not to and review DB design.

For documentation about function see: https://www.postgresql.org/docs/current/functions-info.html

I quote:

" pg_trigger_depth () → integer

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

Why is it bad? Not to repeat what other people wrote, I suggest to read the answer here: https://dba.stackexchange.com/questions/163142/is-pg-trigger-depth-bad-to-use-for-preventing-trigger-cascading-recursion