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

Tom Lane <tgl@sss.pgh.pa.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
textspecifies 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
onthis? 

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,
actuallytells me nothing? In other words, should I understand that the _definition_ of “immutable” would allow some
futurePG implementation justifiably still to return “42” after recompiling “my_constant()”. And should I understand
thatI might to continue to see “42” quite literally forever (even after pg-stop and pg-start) —until I actually drop
thefunction “my_constant()”.