From: | Dinesh Somani <dinesh(at)opsveda(dot)com> |
---|---|
To: | pgsql-novice(at)lists(dot)postgresql(dot)org |
Subject: | Selecting Function Volatility Category |
Date: | 2019-09-20 18:13:03 |
Message-ID: | CAGcTZwU8J2Te9dnhrWu926FDHOd20LgbxnCz+oqP7=ArJY=P_Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Raymond | 2019-09-20 19:25:41 | RE: Selecting Function Volatility Category |
Previous Message | Steve Midgley | 2019-09-19 08:16:21 | Re: any alternative to pg-safeupdate for windows 10? |