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
