Could this get a special mention in the documentation for Indexes on
expressions? Most unfamiliar users will look there to figure out the
correct way to create the index and will be at least warned.
On Wed, May 29, 2019 at 7:25 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Dmytry Strikha <dm.strikha@gmail.com> writes:
> > If the index on expression is defined so, that the indexed value
> > depends on a user-defined function, it doesn't get reindexed when the
> > function's definition changes, therefore completely wrong values can
> > be displayed when the query is executed using that index.
>
> If you declare the function immutable, as you must to use it in an
> index, then redefining the function in a way that changes its results
> means *you* broke the contract. I don't think it's unreasonable of
> us to insist that it's on your head to issue any necessary reindexing.
> Certainly, neither of the plausible alternatives are acceptable:
>
> * Reindex any time the function is changed at all. This is putting
> an undue burden on people who did follow the contract.
>
> * Detecting whether the redefinition changed the results.
> See halting problem.
>
> regards, tom lane