Thread: Selecting Function Volatility Category
For the first part I will defer to wiser people, but I think you’re stuck with defining it as STABLE as there’s still that 1 transaction a month where it could break between statements and cause havoc.
For the second part I would think the gap would only exist if you did the DROP FUNCTION and the CREATE FUNCTION in separate transactions.
Doing them in the same transaction, or using either of CREATE OR REPLACE FUNCTION, or ALTER FUNCTION should leave no point at all in which the function isn’t available to the rest of the users/transactions... Correct?
Transaction mechanics aside, I think this is the primary line of interest from the docs:
“If you drop and then recreate a function, the new function is not the same entity as the old; you will have to drop existing rules, views, triggers, etc. that refer to the old function. Use CREATE OR REPLACE FUNCTION to change a function definition without breaking objects that refer to the function. Also, ALTER FUNCTION can be used to change most of the auxiliary properties of an existing function.”
From: Dinesh Somani <dinesh@opsveda.com>
Sent: Friday, September 20, 2019 2:13 PM
To: pgsql-novice@lists.postgresql.org
Subject: Selecting Function Volatility Category
I have an id lookup function that translates string names into numeric id. The names are pretty stable over the life of the system but still can mutate once in a while (like, once a month) when someone updates configurations. I can use it like...
WHERE ... a.attribute_id = f('SALES', 'MATERIAL_NUMBER') ...
Currently I am classfying the function as STABLE. I would prefer to use category IMMUTABLE as that might be more performant. (https://www.postgresql.org/docs/current/xfunc-volatility.html) How does one tackle the occasional case when the cached values of the function no longer apply? Is there some way to trigger cache invalidation?
I had thought of deleting and recreating the function. But that leaves a tiny gap during which the function becomes non-existent (leading to a hit on my SLAs), plus may also cause plan invalidations all over the application.
Regards
Dinesh
Dinesh Somani wrote:
> I have an id lookup function that translates string names into
> numeric id. The names are pretty stable over the life of the system
> but still can mutate once in a while (like, once a month) when
> someone updates configurations. I can use it like...
>
> WHERE ... a.attribute_id = f('SALES', 'MATERIAL_NUMBER') ...
>
> Currently I am classfying the function as STABLE. I would prefer to
> use category IMMUTABLE as that might be more performant. (
> https://www.postgresql.org/docs/current/xfunc-volatility.html) How
> does one tackle the occasional case when the cached values of the
> function no longer apply? Is there some way to trigger cache
> invalidation?
>
> I had thought of deleting and recreating the function. But that
> leaves a tiny gap during which the function becomes non-existent
> (leading to a hit on my SLAs), plus may also cause plan invalidations
> all over the application.
As long as you don't use the function in an index, it is not so
dangerous to cheat by marking the function IMMUTABLE.
The other concern may be cached plans that use the function.
One simple was to invalidate them would be to run an ALTER FUNCTION:
ALTER FUNCTION f IMMUTABLE;
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list". Could a function read from database and still be counted as immutable? For the first part I will defer to wiser people, but I think you’re stuck with defining it as STABLE as there’s still that 1 transaction a month where it could break between statements and cause havoc.
For the second part I would think the gap would only exist if you did the DROP FUNCTION and the CREATE FUNCTION in separate transactions.
Doing them in the same transaction, or using either of CREATE OR REPLACE FUNCTION, or ALTER FUNCTION should leave no point at all in which the function isn’t available to the rest of the users/transactions... Correct?
Transaction mechanics aside, I think this is the primary line of interest from the docs:
“If you drop and then recreate a function, the new function is not the same entity as the old; you will have to drop existing rules, views, triggers, etc. that refer to the old function. Use
CREATE OR REPLACE FUNCTIONto change a function definition without breaking objects that refer to the function. Also,ALTER FUNCTIONcan be used to change most of the auxiliary properties of an existing function.”
From: Dinesh Somani <dinesh@opsveda.com>
Sent: Friday, September 20, 2019 2:13 PM
To: pgsql-novice@lists.postgresql.org
Subject: Selecting Function Volatility Category
I have an id lookup function that translates string names into numeric id. The names are pretty stable over the life of the system but still can mutate once in a while (like, once a month) when someone updates configurations. I can use it like...
WHERE ... a.attribute_id = f('SALES', 'MATERIAL_NUMBER') ...
Currently I am classfying the function as STABLE. I would prefer to use category IMMUTABLE as that might be more performant. (https://www.postgresql.org/docs/current/xfunc-volatility.html) How does one tackle the occasional case when the cached values of the function no longer apply? Is there some way to trigger cache invalidation?
I had thought of deleting and recreating the function. But that leaves a tiny gap during which the function becomes non-existent (leading to a hit on my SLAs), plus may also cause plan invalidations all over the application.
Regards
Dinesh
On Thu, 17 Oct 2019 at 10:05, Dinesh Somani <dinesh@opsveda.com> wrote:
> That aside, I am still somewhat confused by the following in create function documentation "IMMUTABLE indicates that
thefunction cannot modify the database and always returns the same result when given the same argument values; that is,
itdoes not do database lookups or otherwise use information not directly present in its argument list". Could a
functionread from database and still be counted as immutable?
>
> For example, something like, FUNCTION get_ID(in name varchar) returns int immutable as $$ select x.id from t_lookup x
wherex.name = name; $$
No. You quoted the documentation that explicitly mentions that is not
allowed. i.e. "it does not do database lookups".
An immutable function's return value must only depend on constants and
parameters into the function. The return value cannot depend on
anything external to the function, e.g current time, phase of the
moon, etc.
If you want an example of why then try creating a view such as:
CREATE VIEW my_busted_view AS SELECT get_id('somename');
You can then try EXPLAIN VERBOSE SELECT * FROM my_busted_view; and see
that the planner performed constant folding and included the literal
ID that was there when the view was created.
Try performing an UPDATE on t_lookup to change the ID for "somename"
then see if the view returns the new or the old value. (You'll find
the old value is returned). You'll most likely get the same broken
behaviour with PREPAREd statement and also if you use your function
from within a plpgsql function.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services