Re: Can this function be declared IMMUTABLE?

In response to beickhof(at)Lexmark(dot)com:

> Hello,
>
> I have a question about whether I can safely declare a function IMMUTABLE.
> Citing the PostgreSQL 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 select 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.

Is this a table that will only change during upgrades/maintenance? If so,
then immutable is probably safe, as the table will change under
controlled circumstances.

The utmost gauge of this is "what happen if the function is immutable and
the data _does_ change?" if the result of such a scenario is acceptable,
then you can probably use immutable.

Another rule to take into account is the Law of Premature Optimization.
The law states that trying to optimize too soon will cause pain. Have
you determined that the extra performance gain that immutable will give
you is even necessary? If not, then start out with a more conservative
approach and approach the immutability problem _if_ you see performance
issues.

--
Bill Moran
http://www.potentialtech.com