> On Jun 28, 2022, at 18:41, Bryn Llewellyn <b...@yugabyte.com> wrote:
> Should I simply understand that when I have such a dynamic dependency chain
> of "immutable" functions, and should I drop and re-create the function at the
> start of the chain, then all bets are off until I drop and re-create every
> function along 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 of database or system state: that is,
it's a pure function. Changing the definition in such a way breaks the
contract, so I don't think PostgreSQL needs to do heroics to accommodate that
situation. (For example, changing the definition of an immutable function
that's used in an expression index could corrupt the index.) If one's fixing a
bug, then rolling out the change in a controlled way is a reasonable
requirement.
