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
