User's responsibility when using a chain of "immutable" functions? - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | User's responsibility when using a chain of "immutable" functions? |
| Date | |
| Msg-id | 38FB5DA3-5940-43A9-8B09-5C856E2B18C4@yugabyte.com Whole thread Raw |
| Responses |
Re: User's responsibility when using a chain of "immutable" functions?
(Christophe Pettus <xof@thebuild.com>)
|
| List | 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();
\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();
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
--------------------------------+-----+-----+-----
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
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
pgsql-general by date: