Re: Can this function be declared IMMUTABLE?

On 8/27/07, beickhof(at)lexmark(dot)com <beickhof(at)lexmark(dot)com> wrote:
> Hello,
>
> I have a question about whether I can safely declare a function IMMUTABLE.
> Citing the documentation under "Function Volatility Categories" in the
> section on "Extending SQL":
> ----------------------------------------
> It is generally unwise to select from database tables within an IMMUTABLE
> function at all, since the immutability will be broken if the table
> contents ever change.
> ----------------------------------------
>
> Well, I am considering a function that does read from a table, but the
> table contents change extremely infrequently (the table is practically a
> list of constants). Would it be safe to declare the function IMMUTABLE
> provided that the table itself is endowed with a trigger that will drop
> and recreate the function any time the table contents are modified? In
> this way, it seems that the database would gain the performance benefit of
> an immutable function for the long stretches of time in between changes to
> the table.
>
> I apologize that I don't have any details -- it is still very early in the
> development of the database design, and I was just hoping to get a better
> understanding of whether an immutable function would safely offer any
> benefit in this scenario.
>

Lemme see if I can embarrass myself trying to answer something like
this. It seems like your function really ought to be declared STABLE,
because during a single transaction MVCC will make sure your function
sees the same values in its references table each time you call it,
but between transactions the reference table might change, changing
the result of the function. The benefits of an IMMUTABLE function over
a STABLE one, as far as I know, are these:

1) The planner can take advantage of the fact that this function is
IMMUTABLE to evaluate it only once if its arguments are constant, and
keep this result throughout the life of the query plan. If you don't
cache the query plan (e.g. with PREPARE) this is identical to STABLE,
but if you PREPARE a query, for instance, involving a call to an
IMMUTABLE function with constant arguments, the system can evaluate
the function only once during planning, and never again, whereas for
STABLE you'd have to execute the function each time it was called. For
this to be a big win over STABLE, you have to both call your function
with constant arguments and cache the query plan somehow, such as by
having the query inside another pl/pgsql function or by using PREPARE.

2) You can use IMMUTABLE functions, but not STABLE ones, in
expression-based indexes. If you declared your function IMMUTABLE, you
could build an index on a bunch of data using an index expression
involving your function, have your referenced table change somehow,
and end up not being able to correctly use that index anymore.

There may well be other advantages of IMMUTABLE over STABLE that I
don't know about. Were we talking about data I was supposed to care
for, I'd make the function STABLE, not IMMUTABLE, because that's the
most appropriate for the function.

-Josh