Re: Wrong results from function that selects from vier after "created or replace" - Mailing list pgsql-bugs

From Christophe Pettus
Subject Re: Wrong results from function that selects from vier after "created or replace"
Date
Msg-id D39D6264-C6E5-44F7-92D7-8646BBE19764@thebuild.com
Whole thread Raw
In response to Re: Wrong results from function that selects from vier after "created or replace"  (Bryn Llewellyn <bryn@yugabyte.com>)
List pgsql-bugs

> On Oct 12, 2020, at 18:26, Bryn Llewellyn <bryn@yugabyte.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
sameinput 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,
becauseonce 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
preparedstatements 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@thebuild.com




pgsql-bugs by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Wrong results from function that selects from vier after "created or replace"
Next
From: Tom Lane
Date:
Subject: Re: Wrong results from function that selects from vier after "created or replace"