On Mon, Oct 12, 2020 at 7:57 PM Bryn Llewellyn <bryn@yugabyte.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).
