Re: indexes on functions and create or replace function

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Matthew Dennis" <mdennis(at)merfer(dot)net>
Cc: PGSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: indexes on functions and create or replace function
Date: 2008-08-28 14:30:54
Message-ID: 9770.1219933854@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-08-28 14:34:32 Re: Feature Request: additional extension to UPDATE
Previous Message Julio Leyva 2008-08-28 14:02:03 Re: [GENERAL] PITR - base backup question