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

Bryn Llewellyn <bryn(at)yugabyte(dot)com> writes:
> 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.

FWIW, when I said "database state" I meant to include the contents of the
system catalogs, not only user-defined tables. So redefining the view v
as you did counts as a database state change.

> 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?
> ...
> 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()”.

Yeah. Whether you get "42" or "17" in this example is an implementation
artifact that could vary (and has varied, I think) across PG versions,
depending on the exact plan caching behavior being used. It's even
possible that the result would change in an asynchronous way within a
single session, since hard-to-predict cache flush events could cause the
plan in the calling query to be rebuilt.

> 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()”.

In our current implementation, you would not see the effects of the old
function contents persisting into new backend processes, *in an example of
this sort*. The reason why we insist that "immutable" means "no changes
for the foreseeable future" is that immutable functions are allowed in
index definitions, and there is no mechanism for rebuilding an index
after a behavioral change of a function that it depends on. As a simple
example, if you define f(x) = x+1 and then do

create index on mytab (f(mycol));

then a query such as "select * from mytab where f(mycol) = 42" will
return rows where mycol=41. If you then change f() so it returns x+2,
that query will still return rows where mycol=41, because the
corresponding index entries still contain 42. You'd need to manually
reindex in order to bring the index into sync with the new function
definition. From our standpoint, relaxing the definition of immutable
would entail that the database undertakes to make that sort of thing
happen transparently. While it's probably not impossible, it's not
something we care to invest the required amount of effort in.

regards, tom lane