Re: indexes on functions and create or replace function

"Matthew Dennis" <mdennis(at)merfer(dot)net> writes:
> Given table T(c1 int) and function F(arg int) create an index on T using
> F(c1). It appears that if you execute "create or replace function F" and
> provide a different implementation that the index still contains the results
> from the original implementation, thus if you execute something like "select
> * from T where F(c1)" after replacing the function that it now misses rows
> that should be returned. In other words, the index isn't aware the function
> is now returning different values. That's not the correct/expected behavior
> is it? I would have expected that replacing the function would have caused
> any indexes that depend on that function to be reindexed/recreated with the
> new function implementation.

If it did that, you (or someone) would complain about the enormous
overhead imposed on trivial updates of the function. Since determining
whether the function actually did change behavior is Turing-complete,
we can't realistically try to determine that in software. So we leave
it up to the user to reindex if he makes a behavioral change in an
indexed function.

(Changing the behavior of an allegedly IMMUTABLE function has a number
of other pitfalls besides that one, btw.)

regards, tom lane