Re: Wrong results from function that selects from vier after "created or replace"

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

…We do not attempt to document what might go wrong when you lie
about the volatility classification of a function. There are
a lot of possibilities, many of them worse than what you have
here, and there's no reason to think that the implications will
be stable enough to be worth documenting.

> I had read “forever” to mean “forever until the function, or any of
> its dependency parents, is semantically changed”.

That's a curious reading of "forever”.

regards, tom lane

Thanks again, Tom. And thanks, too, to Christophe Pettus and to David Johnston.

I see the now that my analysis was faulty. And that, moreover, I described Oracle Database’s meaning of “deterministic” wrongly. Yes indeed, both Postgres’s “immutable” and Oracle’s “deterministic” rule out sensitivity to database state. And “select” from table or a view must be defined to be reading database state—even when I happen to know that the view text specifies a result that does not depend on database state.

I’m going you risk overtaxing your patience with a follow-up question to test what you’ve just said. What’s your stance on this?

drop function if exists my_dependant() cascade;
drop function if exists my_constant() cascade;

create function my_constant()
returns int
immutable
language plpgsql
as $body$
begin
return 42;
end;
$body$;

create function my_dependant()
returns int
immutable
language plpgsql
as $body$
declare
v constant int not null := my_constant();
begin
return v;
end;
$body$;

select 'my_dependant(): '||my_dependant()::text;

create or replace function my_constant()
returns int
immutable
language plpgsql
as $body$
begin
return 17;
end;
$body$;

select 'my_dependant(): '||my_dependant()::text;

When I run this, I see “42” at the start and “17” after recompiling the function “my_constant()”.

Should I understand that this outcome, while it might have wrongly seemed to me to support my bad mental model, actually tells me nothing? In other words, should I understand that the _definition_ of “immutable” would allow some future PG implementation justifiably still to return “42” after recompiling “my_constant()”. And should I understand that I might to continue to see “42” quite literally forever (even after pg-stop and pg-start) —until I actually drop the function “my_constant()”.