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

On 2026-10-16, I wrote:
> Reproduced with the attached scripts on:
> - REL_17_8 (6af885119b52)
> - REL_17_STABLE (12f10c25ce3, 2026-10-14)
> - master (3c6665bf026, 2026-10-15)

A short follow-up with a regression test.

1. Still reproduces at the current heads

On macOS 26.4 arm64 (Apple M5 Pro), clang 21, cassert builds:

- REL_17_STABLE 4ec789e1aa50 (2026-10-22)
- master d6cf162f74a8 (2026-10-22)

The attached scripts from my first mail give the same results as
described there for repro1, repro1 with plan_cache_mode = auto, and
variants a, b and c. Nothing has changed in plancache.c, inherit.c,
plancat.c or inval.c on either branch since then. The line numbers in
my first mail are for REL_17_8. At master d6cf162f74a8 the two
plancache.c sites are pg_plan_queries() in BuildCachedPlan() at line
1100 and "plansource->gplan = plan" at line 1342.

2. TAP test

The attached patches turn the reproducers into a TAP test,
src/test/modules/test_misc/t/016_plancache_inval.pl, next to
007_catcache_inval.pl, which tests a similar case for the catalog
cache. Each patch is a single commit that also contains the two
test-only injection points from my first mail, so the test can be
applied and run on its own.

Session A sets plan_cache_mode, prepares the statement, calls
injection_points_set_local() and attaches the point in 'wait' mode.
It then starts the execution with query_until(), and wait_for_event()
confirms that it is waiting. Another session runs the DDL, then
detaches the point and wakes A up.

The check is differential, not a fixed expected value. Three times, in
one REPEATABLE READ transaction of session A, the cached plan's result
is compared with the result of the same query run unprepared.

Cases:

- ATTACH PARTITION to a sub-partitioned table, generic plan;
- the same with plan_cache_mode = auto. The test also checks that
pg_prepared_statements shows 5 custom plans before the pause, and
1 generic and 5 custom plans after it;
- DETACH PARTITION CONCURRENTLY on the sub-partitioned table, with the
second transaction cancelled by lock_timeout (variant a). This one
is deterministic, since the second transaction always waits for
session A;
- nested inlined SQL functions (variant b);
- the ATTACH case through a PL/pgSQL function (variant c).

Each case also has a control: the same DDL between two executions,
with no pause.

On the current code, the 15 differential checks of the five paused
cases fail, and the other 27 checks pass, including all the controls.
I ran the test 20 times on each branch, and every check gave the same
result in all 40 runs. The rest of test_misc still passes.

The REL_17 version differs only in using a pg_available_extensions
query instead of check_extension(), which does not exist in 17 (the
same as REL_17's copy of 007_catcache_inval.pl). The Perl code is
formatted with pgperltidy and the C code passes pgindent.
To satisfy pgindent, I shortened the comment above
get-relation-info-start compared to the patch in my first mail. The
code is the same.

Attached:
- v1-0001-Add-tests-for-stale-cached-plans-after-concurrent-invalidation.patch
(master, base d6cf162f74a8)
- REL_17_STABLE-v1-0001-Add-tests-for-stale-cached-plans-after-concurrent-invalidation.patch
(REL_17_STABLE, base 4ec789e1aa50)

Best Regards!
Xinlei Zhang

On Fri, Oct 16, 2026 at 4:43 PM Clemenza Zhang <zxlmgsps2(at)gmail(dot)com> wrote:
>
> 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