Thread: User's responsibility when using a chain of "immutable" functions?

> On Jun 28, 2022, at 18:41, Bryn Llewellyn <bryn@yugabyte.com> wrote:
> Should I simply understand that when I have such a dynamic dependency chain of "immutable" functions, and should I
dropand re-create the function at the start of the chain, then all bets are off until I drop and re-create every
functionalong the rest of the chain? 

Yes.

You don't have to drop and recreate the functions, though.  DISCARD PLANS handles it as well:

xof=# create function f1() returns text as $$ begin return 'cat'; end $$ language plpgsql immutable;
CREATE FUNCTION
xof=# create function f2() returns text as $$ begin return f1(); end $$ language plpgsql immutable;
CREATE FUNCTION
xof=# create function f3() returns text as $$ begin return f2(); end $$ language plpgsql immutable;
CREATE FUNCTION
xof=# select f1(), f2(), f3();
 f1  | f2  | f3
-----+-----+-----
 cat | cat | cat
(1 row)

xof=# drop function f1();
DROP FUNCTION
xof=# create function f1() returns text as $$ begin return 'dog'; end $$ language plpgsql immutable;
CREATE FUNCTION
xof=# select f1(), f2(), f3();
 f1  | f2  | f3
-----+-----+-----
 dog | dog | cat
(1 row)

xof=# discard plans;
DISCARD PLANS
xof=# select f1(), f2(), f3();
 f1  | f2  | f3
-----+-----+-----
 dog | dog | dog
(1 row)

xof=#

The contract on an immutable function is that it returns the same return value for particular input values regardless
ofdatabase or system state: that is, it's a pure function.  Changing the definition in such a way breaks the contract,
soI don't think PostgreSQL needs to do heroics to accommodate that situation.  (For example, changing the definition of
animmutable function that's used in an expression index could corrupt the index.)  If one's fixing a bug, then rolling
outthe change in a controlled way is a reasonable requirement. 

david.g.johnston@gmail.com wrote:

xof@thebuild.com wrote:

bryn@yugabyte.com wrote:

Should I simply understand that when I have such a dynamic dependency chain of "immutable" functions, and should I drop and re-create the function at the start of the chain, then all bets are off until I drop and re-create every function along the rest of the chain?

Yes.

You don't have to drop and recreate the functions, though. DISCARD PLANS handles it as well:

Specifically:

select f1(), f2(), f3();
 f1  | f2  | f3  
-----+-----+-----
 cat | cat | cat

The pl/pgsql plan cache now contains the following:

SELECT f1() => 'cat'
SELECT f2() => 'cat'

drop function f1();

Now the cache only contains:

SELECT f2() => 'cat'

The f1 plan has been invalidated due to the drop/replace action on the f1 function

create function f1() returns text as $$ begin return 'dog'; end $$ language plpgsql immutable;
select f1(), f2(), f3();
 f1  | f2  | f3  
-----+-----+-----
 dog | dog | cat

And so f3()'s invocation of "SELECT f2()" yields 'cat' from the cache since that one hasn't been invalidated. While f2() replans its f1() invocation and thus returns 'dog'

The fundamental limitation here is that there really is no attempt being made to deal with inter-functional dependencies. Their bodies are blackboxes (...wonders how this resolves in the new SQL Standard Function Bodies implementation...) and no explicit dependency information is recorded either. So we don't know that the saved plan for f2() depends on a specific version of f1() and thus if f1() is changed plans involving f2() should be invalidated along with plans involving f1(). Nor is there sufficient recognized benefit to doing so.

DISCARD PLANS is unsafe in a multi-user concurrent scenario. The doc says explicitly that its scope is just the single session. And it's easy to show the danger by using my testcase manually, step by appropriate step, with two concurrent sessions.

I've concluded that the only practical practice for "immutable" is to reserve its use for functions that don't mention even a single user-created artifact.

Pavel Stehule <pavel.stehule@gmail.com> writes:
> st 29. 6. 2022 v 6:28 odesílatel Bryn Llewellyn <bryn@yugabyte.com> napsal:
>> Moreover, this "hermetic" property of a to-be-immutable function can be
>> established only by human analysis of the function's source code.

> Our immutable functions are more tolerant than they should be - for real
> immutable functions we should disallow SQL inside functions (and everything
> that is not immutable (plpgsql_check raises warning in this case)), but it
> is allowed.

It's generally believed that Turing's proof of the undecidability of
the halting problem [1] implies that it's impossible to mechanically
prove or refute function properties like immutability.  Admittedly,
Turing was concerned with the most general case --- that is, he showed
that *there exist* cases for which no algorithm can give the right
answer, not that any specific practical case can't be proven.
Still, that result has discouraged most people from spending much
time on mechanically checking such things.  If you declare a function
immutable, Postgres will believe you; the consequences if you lied
are on your own head.

            regards, tom lane

[1] https://en.wikipedia.org/wiki/Halting_problem

Pavel Stehule <pavel.stehule@gmail.com> writes:
> st 29. 6. 2022 v 7:46 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>> ... that result has discouraged most people from spending much
>> time on mechanically checking such things.  If you declare a function
>> immutable, Postgres will believe you; the consequences if you lied
>> are on your own head.

> We cannot ensure that the function is immutable, but we can detect that the
> function is not very probably immutable (in execution time).

Sure, there are a lot of easy cases where we could say "that's
obviously not immutable".  But is it worth spending engineering
effort and runtime on that?  I suspect the cases that people
might actually mess up are less obvious, so that we might
accomplish little more than offering a false sense of security.

            regards, tom lane

On Tue, 2022-06-28 at 19:02 -0700, Christophe Pettus wrote:
> > On Jun 28, 2022, at 18:41, Bryn Llewellyn <bryn@yugabyte.com> wrote:
> > Should I simply understand that when I have such a dynamic dependency
> > chain of "immutable" functions, and should I drop and re-create the
> > function at the start of the chain, then all bets are off until I drop
> > and re-create every function along the rest of the chain?
> 
> Yes.

That is not enough in the general case.  You are not allowed to redefine
an IMMUTABLE function in a way that changes its behavior:

CREATE FUNCTION const(integer) RETURNS integer LANGUAGE plpgsql IMMUTABLE AS 'BEGIN RETURN $1; END;';

CREATE TABLE t (x integer);
INSERT INTO t VALUES (1);

CREATE INDEX ON t (const(x));

SET enable_seqscan = off;

SELECT * FROM t WHERE const(x) = 1; -- returns a correct result
 x 
═══
 1
(1 row)

CREATE OR REPLACE FUNCTION const(integer) RETURNS integer LANGUAGE plpgsql IMMUTABLE AS 'BEGIN RETURN $1 + 1; END;';

SELECT * FROM t WHERE const(x) = 1; -- returns a bad result
 x 
═══
 1
(1 row)

Of course, you are allowed to cheat if you know what you are doing.
But any problem you encounter that way is your own problem entirely.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

> On Jun 28, 2022, at 23:42, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> That is not enough in the general case.  You are not allowed to redefine
> an IMMUTABLE function in a way that changes its behavior [...]

I think "not allowed" is putting it too strongly.  It would be a bit much to ask that every single user-written
immutablefunction be 100% perfect when it is rolled out, and never have to fix any bugs in them.  However, you
definitely*do* have to understand that there are administrative consequences for doing so, like rebuilding indexes and
invalidatingsession caches.  I think that the OP's statement that you can't ever use user-defined functions from an
immutablefunction is too strong, too; you need to be aware of the consequences if you change an immutable function in a
waythat alters the return result for a previously-valid set of arguments. 

xof@thebuild.com wrote:

I think "not allowed" is putting it too strongly. It would be a bit much to ask that every single user-written immutable function be 100% perfect when it is rolled out, and never have to fix any bugs in them. However, you definitely *do* have to understand that there are administrative consequences for doing so, like rebuilding indexes and invalidating session caches. I think that the OP's statement that you can't ever use user-defined functions from an immutable function is too strong, too; you need to be aware of the consequences if you change an immutable function in a way that alters the return result for a previously-valid set of arguments.

When I drop the first link, f1() in the chain of "immutable" functions, I see that I cannot invoke f(2) because it now complains that f1() doesn't exist. This surprises me because, after all, the result of f2() is now cached (at least as I suppose) and its body isn't executed to produce the result. This outcome almost suggests that there is, after all, a dependency tracking scheme at work.

Yet I can still invoke the third link, f(3), and it still does produce the value that it had cached!

david.g.johnston@gmail.com wrote:

bryn@yugabyte.com wrote:

When I drop the first link, f1() in the chain of "immutable" functions, I see that I cannot invoke f(2) because it now complains that f1() doesn't exist. This surprises me because, after all, the result of f2() is now cached (at least as I suppose) and its body isn't executed to produce the result. This outcome almost suggests that there is, after all, a dependency tracking scheme at work.

Yet I can still invoke the third link, f(3), and it still does produce the value that it had cached!

The “cache” is just a prepared statement plan. You didn’t create any of those yourself at the top SQL context so you don’t see caching effects in the stuff you execute in SQL directly.

PL/pgSQL, however, creates prepared statement plans for any internal SQL it executes (i.e., it compiles the function). That is the caching artefact you are seeing and why I mentioned pl/pgsql in my reply where I described why you saw the results you did.

IOW, when you write : "select f2();" in SQL, f2() is always called, it is never directly replaced with a cached value. The documentation does say this though I lack the relevant paragraph reference at the moment.

david.g.johnston@gmail.com wrote:

bryn@yugabyte.com wrote:

Meanwhile. I'll appeal for some pointers to what I should read...

I tend not to search...or at least that isn't my first (or at least only) recourse. The pg/pgsql chapter has a subchapter named "Plan Caching":

https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

You really need to read the "see related" reference there to get the level of detail that you want:

https://www.postgresql.org/docs/current/xfunc-volatility.html

"This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments."

The implication is that this operation is not session-scoped but query-scoped. Other parts of the page reinforce this.  Not saying it is perfect wording but I came by my understanding pretty much exclusively from this documentation.