Re: indexes on functions and create or replace function - Mailing list pgsql-general

From Tom Lane
Subject Re: indexes on functions and create or replace function
Date
Msg-id 9770.1219933854@sss.pgh.pa.us
Whole thread Raw
In response to indexes on functions and create or replace function  ("Matthew Dennis" <mdennis@merfer.net>)
Responses Re: indexes on functions and create or replace function
Re: indexes on functions and create or replace function
Re: indexes on functions and create or replace function
List pgsql-general
"Matthew Dennis" <mdennis@merfer.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

pgsql-general by date:

Previous
From: Julio Leyva
Date:
Subject: Re: [ADMIN] PITR - base backup question
Next
From: Tom Lane
Date:
Subject: Re: Feature Request: additional extension to UPDATE