Skip site navigation (1) Skip section navigation (2)

Re: indexes on functions and create or replace function

From: "Matthew Dennis" <mdennis(at)merfer(dot)net>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PGSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: indexes on functions and create or replace function
Date: 2008-08-29 00:45:00
Message-ID: e94d85500808281745r30b82d7cwb241c1e09563d535@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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?

In response to

Responses

pgsql-general by date

Next:From: Matthew DennisDate: 2008-08-29 00:49:16
Subject: Re: indexes on functions and create or replace function
Previous:From: Adrian KlaverDate: 2008-08-28 23:50:55
Subject: Re: ERROR: relation . . . does not exist

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group