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

At Tue, 11 Jul 2023 10:14:29 +0900 (JST), Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> wrote in
> 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.

Even after doing that, any functions used indeirectly are still
allowed to be or to become non-immutable. Therefore, we might want to
reject this because we can't execut it perfectly.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center