Re: Wrong results from function that selects from vier after "created or replace" - Mailing list pgsql-bugs
From | Bryn Llewellyn |
---|---|
Subject | Re: Wrong results from function that selects from vier after "created or replace" |
Date | |
Msg-id | EF124763-B98B-4456-A499-64E0613D3EA6@yugabyte.com Whole thread Raw |
In response to | Re: Wrong results from function that selects from vier after "created or replace" (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Wrong results from function that selects from vier after "created or replace"
Re: Wrong results from function that selects from vier after "created or replace" |
List | pgsql-bugs |
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.
pgsql-bugs by date: