Hello all,
I’ve run into this issue in several contexts recently, and wonder if folks here can help clear up my understanding of function volatility. I often have functions which are not truly immutable (they do something minor, like read in configuration information),
but the functions themselves are fairly expensive, so I want them to run just once per query. At face value, I feel like STABLE should do what I want, but often it does not. Here is a simple example of what I am talking about (tested on 9.1.9):
--------------------------------------------------------------------------
CREATE TABLE t1(id INT PRIMARY KEY, val TEXT);
-- Using numbers as "text" for convenience
INSERT INTO t1 SELECT generate_series(1,1000), random() * 1000;
-- The real function reads configuration from the DB, and so
-- cannot be truthfully IMMUTABLE
--
-- This function returns 'text' to better match my real case,
-- but is otherwise just for demonstration
--
CREATE OR REPLACE FUNCTION passthru(myval text)
RETURNS text
LANGUAGE plpgsql
STABLE STRICT
AS $function$
DECLARE
BEGIN
RAISE NOTICE 'test';
RETURN myval;
END;
$function$
;
EXPLAIN ANALYZE select * from t1 where val like passthru('55') || '%';
ALTER FUNCTION passthru(text) IMMUTABLE;
EXPLAIN ANALYZE select * from t1 where val like passthru('55') || '%';
-------------------------------------------------------------------------------------
If you run this, you should see two things:
1) When STABLE, the function still runs many, many times (see notices), despite having a fixed input.
2) When switching to IMMUTABLE, the function runs just once (as expected) and the query is orders of magnitude faster.
Is STABLE working as it should in this example? I’ve searched around, and in some threads I see explanations that STABLE only /allows/ the planner to run the function once, but the planner is free to run it as many times as it sees fit. If this is the
case, is there a way to alter the function to tell the planner, “trust me, you only want to run this once per query”? In effect, it seems I want something between IMMUTABLE and the current interpretation of STABLE, maybe a SUPERSTABLE designation or something.
I have also seen that wrapping the function in a subselect is a workaround, but it seems unusual to require such a workaround for what seems like a common need.
Thanks for any insight you might have!
Sincerely,
Dan
