Postgresql – Can stable (or immutable) functions call volatile functions

exceptionlogspostgresql

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?

Best Answer

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.

Related Question