Re: Selecting Function Volatility Category

On Thu, 17 Oct 2019 at 10:05, Dinesh Somani <dinesh(at)opsveda(dot)com> wrote:
> That aside, I am still somewhat confused by the following in create function documentation "IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list". Could a function read from database and still be counted as immutable?
>
> For example, something like, FUNCTION get_ID(in name varchar) returns int immutable as $$ select x.id from t_lookup x where x.name = name; $$

No. You quoted the documentation that explicitly mentions that is not
allowed. i.e. "it does not do database lookups".

An immutable function's return value must only depend on constants and
parameters into the function. The return value cannot depend on
anything external to the function, e.g current time, phase of the
moon, etc.

If you want an example of why then try creating a view such as:

CREATE VIEW my_busted_view AS SELECT get_id('somename');

You can then try EXPLAIN VERBOSE SELECT * FROM my_busted_view; and see
that the planner performed constant folding and included the literal
ID that was there when the view was created.

Try performing an UPDATE on t_lookup to change the ID for "somename"
then see if the view returns the new or the old value. (You'll find
the old value is returned). You'll most likely get the same broken
behaviour with PREPAREd statement and also if you use your function
from within a plpgsql function.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services