Hi.We've found one exotic case with SQL function plan invalidation. The case is arguably valid in itself, but I think it shows existing issue in init_execution_state(). To get function result tlist init_execution_state() looks at plansource->query_list. But if plansource was invalidated, query_list is NULL, so we can't look at it.
As for the test case, it uses immutable function to modify table, used in SQL function during execution of query simplification logic.
The possible fix is attached, but I'm not sure that I like it as we always pay for this case by copying query tree to the plan context.
-- Best regards, Alexander Pyhalov, Postgres Professional
From eb1693a9d699544436d0bbe6e56162ece221bfc7 Mon Sep 17 00:00:00 2001 From: Alexander Pyhalov <[email protected]> Date: Tue, 17 Nov 2026 21:02:10 +0300 Subject: [PATCH] init_execution_state() shouldn't suppose that plansource is valid --- src/backend/executor/functions.c | 2 +- src/backend/utils/cache/plancache.c | 3 ++ src/include/utils/plancache.h | 1 + .../regress/expected/create_function_sql.out | 29 ++++++++++++++++++ src/test/regress/sql/create_function_sql.sql | 30 +++++++++++++++++++ 5 files changed, 64 insertions(+), 1 deletion(-) diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c index 8fec79a2f76..2f110902a92 100644 --- a/src/backend/executor/functions.c +++ b/src/backend/executor/functions.c @@ -816,7 +816,7 @@ init_execution_state(SQLFunctionCachePtr fcache) * statement. By this point, we should have thrown an error if there * is not one. */ - resulttlist = get_sql_fn_result_tlist(plansource->query_list); + resulttlist = get_sql_fn_result_tlist(fcache->cplan->query_list); /* * If the result is composite, *and* we are returning the whole tuple diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c index a1b406cee29..f069e713ff3 100644 --- a/src/backend/utils/cache/plancache.c +++ b/src/backend/utils/cache/plancache.c @@ -1123,6 +1123,8 @@ BuildCachedPlan(CachedPlanSource *plansource, List *qlist, MemoryContextSwitchTo(plan_context); plist = copyObject(plist); + + qlist = copyObject(qlist); } else plan_context = CurrentMemoryContext; @@ -1166,6 +1168,7 @@ BuildCachedPlan(CachedPlanSource *plansource, List *qlist, plan->is_oneshot = plansource->is_oneshot; plan->is_saved = false; plan->is_valid = true; + plan->query_list = qlist; /* assign generation number to new plan */ plan->generation = ++(plansource->generation); diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h index a0355e79c28..273cba19e66 100644 --- a/src/include/utils/plancache.h +++ b/src/include/utils/plancache.h @@ -170,6 +170,7 @@ typedef struct CachedPlan int generation; /* parent's generation number for this plan */ int refcount; /* count of live references to this struct */ MemoryContext context; /* context containing this CachedPlan */ + List *query_list; /* list of Query nodes, or NIL if not valid */ } CachedPlan; /* diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out index 57a86927047..be144089bc0 100644 --- a/src/test/regress/expected/create_function_sql.out +++ b/src/test/regress/expected/create_function_sql.out @@ -911,6 +911,35 @@ ERROR: return type mismatch in function declared to return integer[] DETAIL: Function's final statement must be SELECT or INSERT/UPDATE/DELETE/MERGE RETURNING. CONTEXT: SQL function "test1" during startup RESET check_function_bodies; +BEGIN; +CREATE TABLE t(a int); +CREATE FUNCTION f_v() RETURNS void LANGUAGE plpgsql AS $$ +BEGIN + set local client_min_messages to warning; + ALTER TABLE IF EXISTS t RENAME TO t_renamed; +END $$; +-- well, it's not so immutable as it claims +CREATE FUNCTION f_h(int) RETURNS int LANGUAGE plpgsql IMMUTABLE AS $$ +BEGIN + PERFORM f_v(); + BEGIN + PERFORM f_x(1); + EXCEPTION WHEN others THEN + NULL; + END; + RETURN $1; +END $$; +-- This function has to change settings to trigger plan invalidation +CREATE FUNCTION f_x(i int) RETURNS int LANGUAGE sql SET work_mem TO 4096 AS $$ + SELECT f_h(1) + i + (SELECT count(*)::int FROM t) +$$; +SELECT f_x(1); + f_x +----- + 2 +(1 row) + +ROLLBACK; -- Cleanup DROP SCHEMA temp_func_test CASCADE; NOTICE: drop cascades to 45 other objects diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql index 4dd80bb8498..e3aea2aa52d 100644 --- a/src/test/regress/sql/create_function_sql.sql +++ b/src/test/regress/sql/create_function_sql.sql @@ -547,6 +547,36 @@ CREATE FUNCTION test1 (anyelement) RETURNS anyarray LANGUAGE SQL SELECT test1(0); RESET check_function_bodies; +BEGIN; +CREATE TABLE t(a int); + +CREATE FUNCTION f_v() RETURNS void LANGUAGE plpgsql AS $$ +BEGIN + set local client_min_messages to warning; + ALTER TABLE IF EXISTS t RENAME TO t_renamed; +END $$; + +-- well, it's not so immutable as it claims +CREATE FUNCTION f_h(int) RETURNS int LANGUAGE plpgsql IMMUTABLE AS $$ +BEGIN + PERFORM f_v(); + BEGIN + PERFORM f_x(1); + EXCEPTION WHEN others THEN + NULL; + END; + RETURN $1; +END $$; + +-- This function has to change settings to trigger plan invalidation +CREATE FUNCTION f_x(i int) RETURNS int LANGUAGE sql SET work_mem TO 4096 AS $$ + SELECT f_h(1) + i + (SELECT count(*)::int FROM t) +$$; + +SELECT f_x(1); + +ROLLBACK; + -- Cleanup DROP SCHEMA temp_func_test CASCADE; DROP USER regress_unpriv_user; -- 2.43.0
