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

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

Bryn Llewellyn <bryn(at)yugabyte(dot)com> writes:

> The attached self-contained testcase, "pg-bad-invalidation-bug.sql", seems to me definitely to demonstrate a bug. I've attached 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 arguments forever.

How do you regard the fact that, in my “f2()”, the result returned by the dynamically executed “select” _does_ change when view “v” suffers create of replace. And, maybe more pertinently, the result from “immutable” function “f1()” changes immediately 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 dependency parents is recompiled. Assuming I’m not simply missing it, should this caveat be added? There isn’t a single hit for 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”. 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.

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?

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