Can stable (or immutable) functions call volatile functions?

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?

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.

StackExchange Database Administrators Q#43099, answer score: 7