Re: indexes on functions and create or replace function

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Matthew Dennis" <mdennis(at)merfer(dot)net>
Cc: PGSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: indexes on functions and create or replace function
Date: 2008-08-28 23:22:06
Message-ID: 17171.1219965726@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2008-08-28 23:50:55 Re: ERROR: relation . . . does not exist
Previous Message Joshua Drake 2008-08-28 23:04:12 Re: MySQL LAST_INSERT_ID() to Postgres