Re: indexes on functions and create or replace function

"Matthew Dennis" <mdennis(at)merfer(dot)net> writes:
> On Thu, Aug 28, 2008 at 9:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> (Changing the behavior of an allegedly IMMUTABLE function has a number
>> of other pitfalls besides that one, btw.)

> I'm interested in knowing what they are - could you point me in the right
> direction (I've read the docs on immutable, etc) or briefly discuss them
> here please? Thanks...

The main one I can think of offhand is that a call of the function might
have been folded to a constant in some cached plan somewhere, and
there's no mechanism to cause that plan to get redone. (This might or
might not get fixed in 8.4 --- since the plan no longer contains any
reference at all to the function, it's not exactly trivial to fix.)

Another thing that's sort of related to the OP's complaint is something
like a table CHECK constraint that calls a user-defined function.
If you alter the function, is the system supposed to run around and
re-verify that constraint on every row? (And if so, what's supposed to
happen on a failure?) We don't enforce any such thing at the moment.

(In fact, putting the two concepts together, it's possible that
redefining a user function that's used in a UNIQUE index might mean that
the UNIQUE condition now fails ... what should happen then?)

regards, tom lane