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