I have a function which calls itself to resolve a hierarchy. Without any volatility declaration, it gives me the desired result (that means the recursion ends). As a performance optimization, I declared the function as STABLE and executed it again. Now it always aborts with the above error, telling me to increase the max_stack_depth which sounds like endless recursion to me.
What is happening here?
A function may not see the same state of the database depending on whether it's VOLATILE or not, when this state is changing during the execution of the SQL statement it's called from, either because of the statement itself or because of another transaction if the isolation level is
Read Committed.
This is documented in https://www.postgresql.org/docs/current/xfunc-volatility.html
For functions written in SQL or in any of the standard procedural languages, there is a second important property determined by the volatility category, namely the visibility of any data changes that have been made by the SQL command that is calling the function. A VOLATILE function will see such changes, a STABLE or IMMUTABLE function will not. This behavior is implemented using the snapshotting behavior of MVCC (see Chapter 13): STABLE and IMMUTABLE functions use a snapshot established as of the start of the calling query, whereas VOLATILE functions obtain a fresh snapshot at the start of each query they execute.
If your function reads the database and follows different paths depending on what it reads, it's quite possible that it goes into infinite recursion only when it's not VOLATILE. Looking at the code might be revealing.
max_stack_depthparameter? What happened? What is your current value? Have a look at function volatility in the official PostgreSQL documentation.create tableandinsert-statements together with the function itself