Re: Problematic enforcement of "ERROR: functions in index predicate must be marked IMMUTABLE"

At Sun, 9 Jul 2023 14:22:37 +0000, Avi Weinberg <AviW(at)gilat(dot)com> wrote in
> Hi,
>
> If you attempt to create an index based on function that is not IMMUTABLE you will get an exception "ERROR: functions in index predicate must be marked IMMUTABLE". However, if you created the index when the function was IMMUTABLE, but later on you updated the function and mistakenly removed the IMMUTABLE key, you will not get any error to alert you that there is an index based on this function and it should remain IMMUTABLE.
>
> I suggest triggering error message also when updating a function that is used by index if it is no longer IMMUTABLE

There's no way to truly verify a function is really immutable or
not. So, as mentioned in the documentation, the function volatility
categories are essentially a promise to the optimizer regarding the
function's behavior.

Even given this, premising users keeping the volatility marks in line
with the actual behavior of their corresponding functions, it might be
benetifical to prohibit changes to the volatility category while it's
being used for indices. In the first place, that protecting indices
from entering an inconsistent state, at least on the surface.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center