Re: Wrong results from function that selects from vier after "created or replace"

On Mon, Oct 12, 2020 at 6:15 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> 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.