Re: indexes on functions and create or replace function

On Thu, Aug 28, 2008 at 6:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "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?)
>

Well, my expectation is that in the case of the check or unique index, that
PG does try to do a unique reindex and it does go check all the values.
Assuming that it fails one of them, it refuses to replace the function. Of
course, like I suggested with the REINDEX / NOREINDEX options, you could
also have RECHECK / NORECHECK options to say "trust me, I know what I'm
doing" and require them to specify one or the other when replacing a
function that has checks and/or indexes referencing it. I really don't see
much of a difference between I have a table with no unique index / check and
I create one versus I've changed one. If I was to create a new unique index
on an existing table that had duplicate keys, PG would rightfully refuse to
create it of course.

In any case, that's good information to have - thank you.

Another question though. Since I could potentially start transaction, drop
indexes/checks, replace function, create indexes/checks, commit tranasaction
could I deal with the case of the constant folding into the cached plan by
flushing the entire cache in the same transaction? Is cache flushing
transactional? The cases I have for this are infrequent in time and the
overhead of reindexing things, rechecking checks/unique indexes already
dwarf the performance lost to flushing the cache.

On a related note, if I had a maintenence window where I can shutdown all DB
access, make the referenced changes to the functions/indexes/caches/checks
and restart PG - in your opinion, are there other likely problems to
changing an immutable function under those circumstances, or should that be
pretty safe? In other words, I have a function that has indexes on it that
does the wrong thing - what do I do to replace it?