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

> On Oct 12, 2020, at 18:26, Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:
>
> Why are my functions not immutable? They merely select, and have no side-effects.

For a function to be immutable, it needs to not depend on the state of the database, and return the same value for the same input parameters. A SELECT that accesses a view definition depends on the state of the database, because (as you discovered) that view could change under the function. This is particularly important inside of PL/pgSQL functions, because once planned the plans for those functions are cached, and thus the plan could be cached and reused incorrectly. The documentation states this:

> Labeling a function IMMUTABLE when it really isn't might allow it to be prematurely folded to a constant during planning, resulting in a stale value being re-used during subsequent uses of the plan. This is a hazard when using prepared statements or when using function languages that cache plans (such as PL/pgSQL).

It's not a bug, but if the documentation could be improved, suggestions are certainly welcome.

--
-- Christophe Pettus
xof(at)thebuild(dot)com