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

On Mon, Oct 12, 2020 at 7:57 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> create function my_constant()
> immutable
> return 42;
>
> create function my_dependant()
> immutable
> language plpgsql
> v constant int not null := my_constant();
> return v;
>

> select 'my_dependant(): '||my_dependant()::text;
>
> create or replace function my_constant()
> immutable
> language plpgsql
> return 17;
>

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

In terms of running ad-hoc SQL SELECT queries interactively, no. The
function my_dependant will eventually be re-planned and executed and when
that happens the new definition will be seen. In the extreme case plans do
not survive server restarts. Indeed, plans are session-local so at
worse the next time you connect you will see the 17. As seen, you may see
the 17 appear even sooner - within the my_constant function modification
session - but at that point you are observing an implementation detail that
you should not rely upon.

It's when you start doing stuff like: CHECK WHERE (age < my_constant());
and then you insert a bunch of records to that table. Now change the
function to return 17. Dump the table and restore it - every record with
age between 17 and 41 now fails the check constraint even though none of
the data in the table changed. A similar thing happens for functional
indexes - where the inputs and the function result are cached in an on-disk
index for quick future lookup. Both these features require immutable
functions so persisted data that uses those function results continue to
produce the same outcome "forever" (you can at least REINDEX in the later
case).

David J.