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

On Tue, 2022-06-28 at 19:02 -0700, Christophe Pettus wrote:
> > On Jun 28, 2022, at 18:41, Bryn Llewellyn <bryn(at)yugabyte(dot)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