Can stable (or immutable) functions call volatile functions?

Question

The PostgreSQL documentation states:

Any function with side-effects must be labeled VOLATILE...

Consider the following function:

CREATE OR REPLACE FUNCTION count_items()
  RETURNS integer AS
$BODY$
DECLARE
  v_result INTEGER DEFAULT 0;
BEGIN
  SELECT
    count( t.id )
  INTO
    v_result
  FROM
    some_table t;

  RETURN v_result;

EXCEPTION
  WHEN OTHERS THEN
    PERFORM error_log_insert( SQLSTATE, SQLERRM, current_query() );
    RETURN 0;
END;
$BODY$
  LANGUAGE plpgsql STABLE
  COST 10;

Since error_log_insert alters the database (performs an insert upon an exception), does this mean that the count_items function has a side-effect (albeit indirect), and thus cannot be declared STABLE, but must be VOLATILE?

In other words, does the stability or volatility of a function also depend on the functions it calls within its exception block?

If that is the case, then how would you create STABLE functions in PostgreSQL that log all exceptions to a database table?

Answer 1

In the case of purely mathematical circumstances, it is my understanding that the premise of a function f calling a volatile function g would indicate that f is inherently volatile as well. That's because functions in mathematics are equivalencies designed to reduce the work of explaining systems and the one (function or expression) can be substituted for the other, often as syntactic sugar moreso than anything else.

However, my answer isn't specifically from the context of postgres, as I don't normally work in PG. It may be reasonable in PG that the f function would be stable even while the g function were volatile.

I would encourage every developer to examine their code as being basically just mathematics exposed, and so therefore would encourage you to consider the code here as volatile.

Answer 2

Reading the documentation is in order. That comes from someone who just takes interest and never used Postgres and found the answer in the first google link:

http://www.postgresql.org/docs/current/static/xfunc-volatility.html

You may want to put particular emphasis on that part of stable:

This category allows the optimizer to optimize multiple calls of the function to a single call.

So, the problem is - you want the calls to the method logged. But the optimizations of a stable function will lead to the function maybe not executed every time (because the return value for the same parameters is known, so it is reused), so the log will not be complete.

THAT BEING SAID:

As you only want to log exceptions, it may be ok - this also will keep your log smaller as you get only one instance in a query, even when the funciton WOULD be called 100 million times, as long as the optimizer optimizes it out. So, in your case - of exception logging - it may actually be ok. You will have to try out whether postgres developers are putting in protection agaisnt database manipulations, but my bet is no, and I would assume you get away with it.