Convincing STABLE functions to run once

From: Dan Wells <dbw2(at)calvin(dot)edu>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Convincing STABLE functions to run once
Date: 2014-09-09 15:23:59
Message-ID: ed527d86dc3c4e7aa35e29159afaf00e@CO2PR06MB588.namprd06.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2014-09-09 15:36:36 Re: Convincing STABLE functions to run once
Previous Message Adrian Klaver 2014-09-09 14:29:40 Re: psql and tab-delimited output