Re: Functions used in index definitions shouldn't be changed

Tom Lane wrote:
> Antonin Houska <ah(at)cybertec(dot)at> writes:
>> Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
>>> Currently it is possible to change the behaviour of a function with
>>> CREATE OR REPLACE FUNCTION even if the function is part of an index definition.
>>>
>>> I think that should be forbidden, because it is very likely to corrupt
>>> the index. I expect the objection that this would break valid use cases
>>> where people know exactly what they are doing, but I believe that this
>>> is a footgun for inexperienced users that should be disarmed.
>>>
>>> I'd also opt for forbidding behaviour changing modifications with ALTER FUNCTION
>>> for functions used in index definitions, specifically altering strictness.
>>>
>>> Attached is a patch implementing a fix.

> A comparable issue is that alteration of text search configurations may
> partially invalidate precomputed tsvector columns and indexes based on
> tsvector data. We discussed whether we should prohibit that somehow
> and explicitly decided that it would be a bad idea. In the text search
> case, changes like adding stop words are common and don't meaningfully
> invalidate indexes. In some cases you may decide that you need to
> recompute the tsvector data, but that decision should be left to the
> user.
>
> I think that pretty much the same thing applies to functions used in
> indexes. There are too many use-cases where alterations don't
> meaningfully impact the stored data for us to get away with a blanket
> prohibition. (I'm pretty sure that this has been discussed in the
> past, too. If Albe really wants to push the point he should look
> up the previous discussions and explain why the decision was wrong.)

I don't think that there is a universally compelling right or wrong to
questions like this, it is more a matter of taste. Is it more important to protect
the casual DBA from hurting himself or herself, or is it more important to
provide a well honed scalpel for the experienced surgeon?

It seems that more people lean in the latter direction, so I'll cease and desist.

Yours,
Laurenz Albe