Thank you for the very quick response. This is what the Version 12 doc says on “immutable”:
> An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever.
This is the guarantee that you, the function's author, makes, not the database. The database is free to cache or not cache results as it desires. It is not obligated to not re-evaluate an immutable function and re-use a previous evaluation's result.
I had read “forever” to mean “forever until the function, or any of its dependency parents, is semantically changed”.
On the create function page that sentence you quote is followed-on by:
"that is, it does not do database lookups or otherwise use information not directly present in its argument list."
I suppose having that in the other page would have saved a bit of confusion.
And this is the caveated meaning that Oracle database implements for its moral equivalent “deterministic”. Notice that this is the meaning that YugabyteDB implements, too, so clearly the problem of invalidation is soluble when that goal is explicitly specified.
PostgreSQL has defined its meaning of immutable. Your example demonstrates what can happen if you promise your function is immutable and is it not.
The PG doc goes on to say “For best optimization results, you should label your functions with the strictest volatility category that is valid for them.”
So your reply implies that “immutable” must *never* be used in ordinary application code that might be patched unless the shop commits to doing every single patch, in a production system, only after disconnecting all regular client sessions so that, on re-connecting when patching is complete, everything will be re-compiled ab initio. Where is this rule documented?
This seems more philosophical than practical. Yes, truly, and even practically, immutable functions are rare but they are possible. Stable is usually what ends up being promised. Your toy example has no purpose being immutable anyway.
select add_two(d1 int, d2 int) returns select d1+d2;
Philosophically speaking the operator plus could change, but it won't and so the function is practically immutable. The same goes for the concatenation operator. Or even the meaning of symbol 2 if the inner query was select 2+2.
As your quoted page says, immutable functions can rely on other immutable functions, which is why d1+d2 works.
David J.