On Tue, 2022-06-28 at 19:02 -0700, Christophe Pettus wrote: > > On Jun 28, 2022, at 18:41, Bryn Llewellyn <b...@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
