Cached plan can stay stale indefinitely when an invalidation for a planner-added dependency arrives during planning

Hi,

A cached plan (PREPARE, extended-protocol named statements, PL/pgSQL SPI
plans) can keep returning wrong results indefinitely, without any error,
if an invalidation for an object that appears only in the plan's
PlannedStmt-level dependencies is processed while the plan is being built.
Examples of such objects are a partition of a sub-partitioned table, or an
SQL function that only shows up after inlining. The stale plan survives
across transactions. It is replaced only when some other invalidation
happens to hit one of its plansource-level dependencies.

Reproduced with the attached scripts on:
- REL_17_8 (6af885119b52)
- REL_17_STABLE (12f10c25ce3, 2026-10-14)
- master (3c6665bf026, 2026-10-15)
macOS 26.4 arm64, assert-enabled builds.

This looks related to BUG #19690 [1] (stale rd_partdesc after concurrent
ATTACH PARTITION), but it is a separate problem in plancache.c:

- In the cases below the relcache is correct afterwards: an unprepared
query in the same session returns the right answer. Only the cached plan
is wrong.
- It is not limited to "a query that started just after the DDL committed"
(the window accepted in 898e5e3). The wrong result persists across later
transactions.
- It is not tied to the ATTACH lock-level relaxation.
CREATE OR REPLACE FUNCTION takes no table lock at all and shows the same
behavior.

== Mechanism ==

Line numbers refer to REL_17_8.

1. GetCachedPlan() builds a new generic plan in BuildCachedPlan()
-> pg_plan_queries() (plancache.c:962). The plan is linked into
plansource->gplan only afterwards (plancache.c:1203).

2. Planning acquires new relation locks, and LockRelationOid() then calls
AcceptInvalidationMessages(). Two examples:
- try_table_open() on each partition in expand_partitioned_rtentry()
(inherit.c:393), which runs after the parent's PartitionDesc was read
(inherit.c:332);
- index_open() in get_relation_info().

3. PlanCacheRelCallback() and PlanCacheObjectCallback() decide whether a
plansource is invalid by looking only at plansource->relationOids and
plansource->invalItems. Those come from extract_query_dependencies() on
the rewritten query tree.
PlannedStmt-level dependencies are checked only through
plansource->gplan, which is still NULL at this point. These include:
- child partitions added during expansion (setrefs.c:578);
- functions recorded by record_plan_function_dependency() during
inlining (clauses.c:4879).

4. So if the invalidation concerns an object that exists only in the
PlannedStmt-level dependencies:
- the callback finds nothing to mark, and the message is consumed;
- the new plan, built from catalog state read before the message, is
linked as valid;
- later executions find no pending invalidation and keep reusing it.

When the changed object is in plansource->relationOids or invalItems, the
plansource is marked invalid and the next execution replans, so that case
self-heals. Examples: ATTACH to the top-level parent named in the query, or
replacing a function referenced directly in the query.

== Reproducer 1: ATTACH PARTITION to a sub-partitioned table ==

To make the window deterministic, the attached test-only patch adds two
injection points (it changes no logic):

- expand-subpartdesc-read: in expand_partitioned_rtentry(), right after
PartitionDirectoryLookup(), only for non-top-level parents:

if (parentrel->rd_rel->relispartition)
INJECTION_POINT("expand-subpartdesc-read"); /* master: , NULL */

- get-relation-info-start: at the start of get_relation_info(), after
table_open(); used by variant b below.

Build with --enable-injection-points and install
src/test/modules/injection_points.

Setup (repro1_setup.sql):

CREATE EXTENSION injection_points;
CREATE TABLE root (grp int, k int, v int) PARTITION BY LIST (grp);
CREATE TABLE p_a PARTITION OF root FOR VALUES IN (1);
CREATE TABLE mid PARTITION OF root FOR VALUES IN (2) PARTITION BY RANGE (k);
CREATE TABLE mid_1 PARTITION OF mid FOR VALUES FROM (0) TO (1000);
CREATE TABLE float_sub (grp int, k int, v int);
INSERT INTO p_a SELECT 1, i, i FROM generate_series(1, 100) i;
INSERT INTO mid_1 SELECT 2, i, i FROM generate_series(1, 100) i;
INSERT INTO float_sub SELECT 2, 1000 + i, i FROM generate_series(1, 100) i;

Session A (repro1_session_a.sql):

SET plan_cache_mode = force_generic_plan;
PREPARE q(int) AS SELECT count(*) FROM root WHERE v >= $1;
SELECT injection_points_set_local();
SELECT injection_points_attach('expand-subpartdesc-read', 'wait');
EXECUTE q(0); -- waits after reading mid's PartitionDesc

Session B (repro1_session_b.sql), while A waits. It does not block,
because A holds only AccessShareLock on root, p_a and mid:

ALTER TABLE mid ATTACH PARTITION float_sub FOR VALUES FROM (1000) TO (2000);
SELECT injection_points_wakeup('expand-subpartdesc-read');

Session A, continued:

-- the paused EXECUTE returns 200; on its own that would be the
-- accepted window
SELECT injection_points_detach('expand-subpartdesc-read');
EXECUTE q(0); -- 200 (expected 300)
SELECT count(*) FROM root WHERE v >= 0; -- 300
EXPLAIN (COSTS OFF) EXECUTE q(0); -- Append over p_a and mid_1 only
EXECUTE q(0); -- still 200
EXECUTE q(0); -- still 200

In psql's autocommit mode each EXECUTE runs in its own transaction. The
result is the same with plan_cache_mode = auto when q is executed five
times (custom plans) before the injection point is attached
(pg_prepared_statements then shows generic_plans = 6, custom_plans = 5).

run_psql.sh runs a whole scenario with two psql sessions, for example
"./run_psql.sh repro1". It waits until session A shows the injection point
as its wait_event before starting session B.

How long it lasts: on an otherwise idle instance with autovacuum off, it
was still wrong after 10 minutes.

What cleared it:
- an ANALYZE of root or mid that actually changed reltuples
(vac_update_relstats() only updates pg_class, and so only sends an
invalidation, when a value changes);
- the session's first CREATE TEMP TABLE (presumably the namespace
invalidation -> ResetPlanCache).

What did not clear it:
- ANALYZE of p_a or mid_1 with unchanged statistics;
- ordinary statements in session A;
- waiting.

== Reproduction without instrumentation ==

plancache_stress.py needs only psycopg 3 and an unmodified server.
Connection parameters come from --dsn or the PG* environment variables.
On an unmodified REL_17_8 build:
- mid has 800 partitions of 1 row each, to widen the planning window;
- N sessions loop "DISCARD PLANS; EXECUTE q(0)";
- another session repeatedly ATTACHes and DETACHes float_sub on mid;
- after each ATTACH commits, the loops are paused, and each session runs
EXECUTE q(0) three times in fresh transactions and compares the result
with the unprepared query;
- a round counts as a hit only if all three results differ.

sessions duration rounds hit rounds first hit
1 300 s 2131 493 (23.1%) 0.1 s
8 300 s 441 115 (26.1%) 0.8 s
16 301 s 231 106 (45.9%) 1.6 s

Each session locks all partitions of mid in one transaction, so the shared
lock table must hold roughly sessions * 800 entries. The script checks
max_locks_per_transaction and max_connections before starting; the runs
above used max_locks_per_transaction = 2048. The script creates its
objects in its own schema and drops them at the end.

== Other variants ==

All three reproduce on 17.8, REL_17_STABLE and master with the attached
scripts.

a) DETACH PARTITION CONCURRENTLY on the sub-partitioned table, with the
second transaction cancelled (variant_a_*.sql). float_sub starts
attached. While session A waits at expand-subpartdesc-read, session B
runs:

SET lock_timeout = '1s';
ALTER TABLE mid DETACH PARTITION float_sub CONCURRENTLY;
-- ERROR: canceling statement due to lock timeout

- the first transaction commits, and float_sub stays detach-pending
(pg_inherits.inhdetachpending = true);
- its invalidation was consumed as described above;
- the second transaction was waiting for session A and never
completes, so no further invalidation arrives.

Afterwards EXECUTE q(0) keeps returning 300 and still scans float_sub,
while the unprepared query returns 200.
As far as I understand the design, the second phase's wait is what
guarantees that older plans go away; when that phase is cancelled, the
backstop is gone.

b) Nested inlined SQL functions (variant_b_*.sql):

CREATE FUNCTION inner_f(int) RETURNS int LANGUAGE sql IMMUTABLE
AS 'SELECT $1 + 1';
CREATE FUNCTION outer_f(int) RETURNS int LANGUAGE sql IMMUTABLE
AS 'SELECT inner_f($1)';
CREATE TABLE t (v int);
INSERT INTO t SELECT generate_series(1, 100);
CREATE INDEX ON t (v);

Session A:

SET plan_cache_mode = force_generic_plan;
PREPARE q2(int) AS SELECT sum(outer_f(v)) FROM t WHERE v >= $1;
SELECT injection_points_set_local();
SELECT injection_points_attach('get-relation-info-start', 'wait');
EXECUTE q2(0); -- waits after inlining, before index_open()

Session B:

CREATE OR REPLACE FUNCTION inner_f(int) RETURNS int LANGUAGE sql IMMUTABLE
AS 'SELECT $1 + 1000';
SELECT injection_points_wakeup('get-relation-info-start');

Afterwards EXECUTE q2(0) keeps returning 5150, while the unprepared
query returns 105050. EXPLAIN VERBOSE shows sum((v + 1)).
Replacing outer_f instead self-heals, because outer_f is in
plansource->invalItems.
plan_cache_mode = force_generic_plan matters here. Under the default
auto setting the paused execution builds a custom plan, which is not
cached, so nothing stale remains.

c) Reproducer 1's query inside a PL/pgSQL function, i.e. an SPI saved plan
(variant_c_*.sql):

SELECT count(*) INTO n FROM root WHERE v >= p;

With the same pause and ATTACH, f_count(0) keeps returning 200 while
the unprepared query returns 300.

Controls: the same DDL executed between two EXECUTEs, i.e. without a pause
during planning, never produced a wrong result.

== Possible directions ==

I have not written a fix. Two thoughts:

- BuildCachedPlan()/GetCachedPlan() could record SharedInvalidMessageCounter
before planning. If it has changed by the time the plan is linked, the new
plan should not be trusted: either replan, or link it marked invalid so
the next use replans. This is similar to the retry loop in
RangeVarGetRelidExtended(). Some bound would be needed to avoid
replanning forever under continuous DDL.

- RevalidateCachedQuery() has the same shape:
- it sets is_valid = false before re-analysis, so the callbacks skip the
entry;
- afterwards it sets is_valid = true unconditionally (plancache.c:661 and
:806).
I could not find a user-visible case in stock PostgreSQL, because what
analysis captures is protected by locks that conflict with the relevant
DDL. The pattern still looks just as fragile.

Attached:
- 0001-test-only-injection-points-REL_17.patch: for REL_17_8 and
REL_17_STABLE;
- 0001-test-only-injection-points-master.patch;
- repro1_{setup,session_a,session_b}.sql;
- variant_{a,b,c}_{setup,session_a,session_b}.sql;
- run_psql.sh: runs one scenario with two psql sessions;
- plancache_stress.py: the stress reproducer for unmodified builds.

[1] https://www.postgresql.org/message-id/19690-5619e7d182d18d5d@postgresql.org
Best Regards!
Xinlei Zhang