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

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Bryn Llewellyn <bryn@yugabyte.com> writes:

> The attached self-contained testcase, "pg-bad-invalidation-bug.sql", seems to me definitely to demonstrate a bug.
I'veattached the spooled output as "pg-bad-invalidation-bug.txt" Do you agree that this is a bug? 

No.  You marked the functions as "immutable", and then you broke that
promise by changing what they'd need to output.  The planner had
already inlined the original output value of f1() into the calling
expressions in f2(), on the strength of it being allegedly immutable.
There is no mechanism for undoing that (short of a forced cache flush
or session end), nor should there need to be.

If I mark the functions as either stable or volatile, I see the
desired behavior.

            regards, tom lane


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

How do you regard the fact that, in my “f2()”, the result returned by the dynamically executed “select” _does_ change
whenview “v” suffers create of replace. And, maybe more pertinently, the result from “immutable” function “f1()”
changesimmediately when “v” is redefined. So, also, does this have an immediate effect: 

  create or replace function f2()
    returns text
    immutable
    language plpgsql
  as $body$
  declare
    t1 constant text := f1()||'?';
    t2 constant text := (select f1())||'?';
    t3 text not null := '';
  begin
    execute $$select f1()||'?'$$ into t3;
    return t1||' | '||t2||' | '||t3;
  end;
  $body$;

I couldn’t find an “all bets are off” caveat for the case where either an “immutable” function itself or any of its
dependencyparents is recompiled. Assuming I’m not simply missing it, should this caveat be added? There isn’t a single
hitfor any inflexion of “compile” on the page that defines “immutable”: 

https://www.postgresql.org/docs/12/xfunc-volatility.html

I had read “forever” to mean “forever until the function, or any of its dependency parents, is semantically changed”.
Andthis is the caveated meaning that Oracle database implements for its moral equivalent “deterministic”. Notice that
thisis the meaning that YugabyteDB implements, too, so clearly the problem of invalidation is soluble when that goal is
explicitlyspecified. 

The PG doc goes on to say “For best optimization results, you should label your functions with the strictest volatility
categorythat is valid for them.” So your reply implies that “immutable” must *never* be used in ordinary application
codethat might be patched unless the shop commits to doing every single patch, in a production system, only after
disconnectingall regular client sessions so that, on re-connecting when patching is complete, everything will be
re-compiledab initio. Where is this rule documented? 

I do appreciate that “drop... cascade” will meet the correctness requirement by brute force. But that technique, too,
meansmaking the application unavailable for the duration of the patching exercise.