Re: indexes on functions and create or replace function - Mailing list pgsql-general
| From | Matthew Dennis |
|---|---|
| Subject | Re: indexes on functions and create or replace function |
| Date | |
| Msg-id | e94d85500808281745r30b82d7cwb241c1e09563d535@mail.gmail.com Whole thread Raw |
| In response to | Re: indexes on functions and create or replace function (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: indexes on functions and create or replace function
("Matthew Dennis" <mdennis@merfer.net>)
|
| List | pgsql-general |
On Thu, Aug 28, 2008 at 6:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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?
"Matthew Dennis" <mdennis@merfer.net> writes:The main one I can think of offhand is that a call of the function might> On Thu, Aug 28, 2008 at 9:30 AM, Tom Lane <tgl@sss.pgh.pa.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...
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?
pgsql-general by date: