Convincing STABLE functions to run once

[Dan Wells]

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

[Tom Lane]

Post by Dan Wells
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.

STABLE tells the system it's *okay* to run the function fewer times than
naive SQL semantics might suggest. There's no *guarantee* that any such
optimization will happen (and in fact, about the only special thing that
currently happens for STABLE functions is that they're considered okay
to use in indexscan qualifications).

What I'd suggest is sticking the expensive function call into a CTE
(a WITH clause). We do guarantee only-once eval for CTEs.

regards, tom lane

[Merlin Moncure]

Post by Dan Wells
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
--------------------------------------------------------------------------
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$
;

This is kinda off topic but I'd like to point out your 'passthru'
function is a wonderful debugging trick. I write it like this:

CREATE OR REPLACE FUNCTION Notice(anyelement) RETURNS anyelement AS
$$
BEGIN
RAISE NOTICE '%', $1;
RETURN $1;
END;
$$ LANGUAGE PLPGSQL;

The reason why that's so useful is that when you have complicated
functions that depend on each other it can be kind of a pain to adjust
complicated SQL so that it 'raise notices' values you'd want to see --
the passthrough function makes it a snap without adjusting query
behavior.

merlin

[Alban Hertroys]

Post by Dan Wells
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.

Assuming that the part that makes these functions expensive is not the reading of the configuration information, perhaps you can split your functions such that the expensive part goes into an IMMUTABLE function that takes those (STABLE) configuration values as input?

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.