> On Jun 28, 2022, at 18:41, Bryn Llewellyn <bryn@yugabyte.com> wrote:
> Should I simply understand that when I have such a dynamic dependency chain of "immutable" functions, and should I
dropand re-create the function at the start of the chain, then all bets are off until I drop and re-create every
functionalong the rest of the chain?
Yes.
You don't have to drop and recreate the functions, though. DISCARD PLANS handles it as well:
xof=# create function f1() returns text as $$ begin return 'cat'; end $$ language plpgsql immutable;
CREATE FUNCTION
xof=# create function f2() returns text as $$ begin return f1(); end $$ language plpgsql immutable;
CREATE FUNCTION
xof=# create function f3() returns text as $$ begin return f2(); end $$ language plpgsql immutable;
CREATE FUNCTION
xof=# select f1(), f2(), f3();
f1 | f2 | f3
-----+-----+-----
cat | cat | cat
(1 row)
xof=# drop function f1();
DROP FUNCTION
xof=# create function f1() returns text as $$ begin return 'dog'; end $$ language plpgsql immutable;
CREATE FUNCTION
xof=# select f1(), f2(), f3();
f1 | f2 | f3
-----+-----+-----
dog | dog | cat
(1 row)
xof=# discard plans;
DISCARD PLANS
xof=# select f1(), f2(), f3();
f1 | f2 | f3
-----+-----+-----
dog | dog | dog
(1 row)
xof=#
The contract on an immutable function is that it returns the same return value for particular input values regardless
ofdatabase or system state: that is, it's a pure function. Changing the definition in such a way breaks the contract,
soI don't think PostgreSQL needs to do heroics to accommodate that situation. (For example, changing the definition of
animmutable function that's used in an expression index could corrupt the index.) If one's fixing a bug, then rolling
outthe change in a controlled way is a reasonable requirement.
