On Thu, Aug 28, 2008 at 6:22 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Matthew Dennis" <[EMAIL PROTECTED]> writes: > > On Thu, Aug 28, 2008 at 9:30 AM, Tom Lane <[EMAIL PROTECTED]> 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?
