Re: Can this function be declared IMMUTABLE?

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.

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.

Thanks very much,
Bobby

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

beickhof(at)Lexmark(dot)com writes:
> 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?

What you'd want to do is restart all existing sessions so that any plans
made using precomputed function values are discarded. The trigger you
suggest is fairly pointless because it will not cause regeneration of
plans.

I concur with Bill's remark that you should first determine if there's
a really substantial benefit to marking the function immutable rather
than merely stable.

regards, tom lane

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> beickhof(at)Lexmark(dot)com writes:
>> 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?
>
> What you'd want to do is restart all existing sessions so that any plans
> made using precomputed function values are discarded. The trigger you
> suggest is fairly pointless because it will not cause regeneration of
> plans.

The trigger would alert him if there were any indexes built using the
function...

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com