Re: indexes on functions and create or replace function

On Thu, Aug 28, 2008 at 9:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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.
>

I have no doubt that someone would complain about it, but I think it's
better than the alternative. I received no errors, no warnings and no
indication whatsoever that my queries were now returning incorrect results.
If people are worried about the case of changing the text/implementation of
the function but not the behavior and needing to avoid the reindexing
overhead, it should prevent you from doing it unless you explicitly say no
reindexing is required. How about having NOREINDEX and/or REINDEX options
in the create or replace function syntax? If no indexes depend on the
function, no option is required. If there are indexes on the function,
require one or the other (thus handling all cases). I have two main issues
I'm trying to address:

1) Future queries not returning matching rows.
2) Having to manually go find all the indexes that use that function, drop
them, replace the function, recreate them. This just seems like tedioum
that really should live interal to PG.