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.
