Re: Corruption with IMMUTABLE functions in index expression.

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Monday, October 11, 2021, Prabhat Sahu <prabhat(dot)sahu(at)enterprisedb(dot)com>
> wrote:
>> While using IMMUTABLE functions in index expression, we are getting below
>> corruption on HEAD.

> That function is not actually immutable (the system doesn’t check whether
> your claim of immutability and the function definition match, its up to you
> to know and specify the correct label for what the function does) so not
> our problem. Write a trigger instead.

Yeah. What is happening is that the function's SELECT on the subject
table is trying to examine the not-yet-valid new index. While that could
be argued to be a bug, I share David's lack of interest in fixing it,
because I do not believe that there are any cases where a function that
accesses an index's subject table is really going to be immutable.

To prevent this access, we'd have to set pg_index.indisvalid false
initially and then update it to true after the index is built.
We do do that in CREATE INDEX CONCURRENTLY (so you can make this
example work by specifying CONCURRENTLY), but I'm unexcited about
creating bloat in pg_index for the standard case in order to support
a usage scenario that is going to cause you all sorts of other pain.
To paraphrase Henry Spencer: if you lie to the planner, it will get
its revenge.

regards, tom lane