User's responsibility when using a chain of "immutable" functions?

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: User's responsibility when using a chain of "immutable" functions?
Date: 2022-06-29 01:41:58
Message-ID: 38FB5DA3-5940-43A9-8B09-5C856E2B18C4@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I’ve copied my self-contained testcase at the end.

I create three functions, marking each of them "immutable". "f1()" simply returns the manifest constant 'dog'. So it seems perfectly honest to mark it as I did. "f2()" simply returns "f1()"—so, here too, it seems that I'm being honest. But I do see that I'm using human reasoning, and that Postgres cannot check that I'm right. In the same way, and with the same reasoning for my marking, "f3()" returns "f2()".

Then I do this:

select rpad('at start', 30) as history, f1(), f2(), f3();

Then I drop, and then re-create "f(1)", now returning 'cat', and do this:

select rpad('after drop, re-create f1()', 30) as history, f1(), f2(), f3();

Finally, I create-and-replace "f3()", using the identical source text, and do this:

select rpad('after create-and-replace f3()', 30) as history, f1(), f2(), f3();

Here's what I see when I run my .sql script:

history | f1 | f2 | f3
--------------------------------+-----+-----+-----
at start | dog | dog | dog
after drop, re-create f1() | cat | cat | dog
after create-and-replace f3() | cat | cat | cat

I understand that the possible session-duration caching that I allow with "immutable" doesn't guarantee that I'll get any caching. But I had expected a cascade purge on anything that was cashed on delete of "f1()".

Something seems odd to me: if I do my "select f1(), f2(), f3()" after dropping "f1()" (i.e. before re-creating it) then I get an ordinary error saying that "f1()" doesn't exist. So it seems that Postgres does understand the dynamic dependency chain—even when the result from "f3()" is cached. If I then recreate "f1()" to return 'cat', I get no error—but, same as in my straight-through test, "f3()" continues to return its cached (and now "wrong") result.

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?

--------------------------------------------------------------------------------
-- testcase.sql

\t off

drop function if exists f3() cascade;
drop function if exists f2() cascade;
drop function if exists f1() cascade;

create function f1()
returns text
immutable
language plpgsql
as $body$
begin
return 'dog';
end;
$body$;

create function f2()
returns text
immutable
language plpgsql
as $body$
begin
return f1();
end;
$body$;

create function f3()
returns text
immutable
language plpgsql
as $body$
begin
return f2();
end;
$body$;

select rpad('at start', 30) as history, f1(), f2(), f3();

\t on

drop function f1() cascade;
create function f1()
returns text
immutable
language plpgsql
as $body$
begin
return 'cat';
end;
$body$;

select rpad('after drop, re-create f1()', 30) as history, f1(), f2(), f3();

create or replace function f3()
returns text
immutable
language plpgsql
as $body$
declare
t1 constant text := f2();
begin
return t1;
end;
$body$;

select rpad('after create-and-replace f3()', 30) as history, f1(), f2(), f3();

\t off

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christophe Pettus 2022-06-29 02:02:34 Re: User's responsibility when using a chain of "immutable" functions?
Previous Message David Rowley 2022-06-28 18:24:56 Re: Unique index prohibits partial aggregates