Hi,
On Sat, Sep 19, 2026 at 3:59 PM Amit Langote <[email protected]> wrote:
>
> Invalidate RI fast-path metadata on operator family changes
>
> The RI fast path checks a foreign key by probing the referenced
> unique index directly, using the equality operator recorded for
> the constraint. Whether the fast path can be used is decided once
> and cached in RI_ConstraintInfo, and that cache is invalidated on
> pg_constraint changes but not on pg_amop. So after an ALTER
> OPERATOR FAMILY drops the recorded operator and adds another in its
> place, the cached decision is stale, and the next fast-path check
> probes the index with an operator no longer in the opfamily and
> errors out with "operator XXX is not a member of opfamily XXX".
> The SPI path is unaffected, because the planner just stops matching
> the index.
>
> To fix, register an AMOPOPID syscache callback to flush the RI
> cache on pg_amop changes, and have ri_check_fastpath_index()
> recheck that the recorded operator is still the equality member of
> the index's opfamily, falling back to SPI when it is not.
>
> Add regression test coverage.
>
> Reported-by: Nikolay Samokhvalov <[email protected]>
> Author: Nikolay Samokhvalov <[email protected]>
> Discussion:
> https://www.postgr.es/m/CAM527d9PzFzagr67N0%3DEx2ng1p5HzrcAszy3j5OoZKHXQMARXA%40mail.gmail.com
> Backpatch-through: 19
>
> Branch
> ------
> master
>
> Details
> -------
> https://git.postgresql.org/pg/commitdiff/c62b330912e2095dc8dee2f749adf7e5d94ca611
>
> Modified Files
> --------------
> src/backend/utils/adt/ri_triggers.c | 45 ++++++++++++++++++++-
> src/test/regress/expected/foreign_key.out | 67 +++++++++++++++++++++++++++++++
> src/test/regress/sql/foreign_key.sql | 56 ++++++++++++++++++++++++++
> 3 files changed, 166 insertions(+), 2 deletions(-)
I noticed a few BF failures, after pushing the above changes:
sifaka / master:
# diff -U3
/Users/buildfarm/bf-data/HEAD/pgsql.build/src/test/regress/expected/window.out
/Users/buildfarm/bf-data/HEAD/pgsql.build/src/test/regress/results/window.out
# ---
/Users/buildfarm/bf-data/HEAD/pgsql.build/src/test/regress/expected/window.out
2026-09-19 02:53:30
# +++
/Users/buildfarm/bf-data/HEAD/pgsql.build/src/test/regress/results/window.out
2026-09-19 03:22:01
# @@ -5658,16 +5658,19 @@
# SELECT COUNT(*) OVER (ORDER BY t1.unique1)
# FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous
# LIMIT 1;
# - QUERY PLAN
# ---------------------------------------------------------------------------
# + QUERY PLAN
#
+--------------------------------------------------------------------------------------
# Limit
# -> WindowAgg
# Window: w1 AS (ORDER BY t1.unique1)
# - -> Nested Loop
# - -> Index Only Scan using tenk1_unique1 on tenk1 t1
# - -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
# - Index Cond: (tenthous = t1.unique1)
# -(7 rows)
# + -> Sort
# + Sort Key: t1.unique1
# + -> Hash Join
# + Hash Cond: (t1.unique1 = t2.tenthous)
# + -> Index Only Scan using tenk1_unique1 on tenk1 t1
# + -> Hash
# + -> Index Only Scan using
tenk1_thous_tenthous on tenk1 t2
# +(10 rows)
#
# -- Ensure we get a cheap total plan. Lack of ORDER BY in the WindowClause
# -- means that all rows must be read from the join, so a cheap startup plan
sifaka / REL_19_STABLE:
# diff -U3
/Users/buildfarm/bf-data/REL_19_STABLE/pgsql.build/src/test/regress/expected/window.out
/Users/buildfarm/bf-data/REL_19_STABLE/pgsql.build/src/test/modules/test_plan_advice/tmp_check/results/window.out
# ---
/Users/buildfarm/bf-data/REL_19_STABLE/pgsql.build/src/test/regress/expected/window.out
2026-09-19 02:53:07
# +++
/Users/buildfarm/bf-data/REL_19_STABLE/pgsql.build/src/test/modules/test_plan_advice/tmp_check/results/window.out
2026-09-19 03:09:56
# @@ -5658,16 +5658,19 @@
# SELECT COUNT(*) OVER (ORDER BY t1.unique1)
# FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous
# LIMIT 1;
# - QUERY PLAN
# ---------------------------------------------------------------------------
# + QUERY PLAN
#
+--------------------------------------------------------------------------------------
# Limit
# -> WindowAgg
# Window: w1 AS (ORDER BY t1.unique1)
# - -> Nested Loop
# - -> Index Only Scan using tenk1_unique1 on tenk1 t1
# - -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
# - Index Cond: (tenthous = t1.unique1)
# -(7 rows)
# + -> Sort
# + Sort Key: t1.unique1
# + -> Hash Join
# + Hash Cond: (t1.unique1 = t2.tenthous)
# + -> Index Only Scan using tenk1_unique1 on tenk1 t1
# + -> Hash
# + -> Index Only Scan using
tenk1_thous_tenthous on tenk1 t2
# +(10 rows)
#
# -- Ensure we get a cheap total plan. Lack of ORDER BY in the WindowClause
# -- means that all rows must be read from the join, so a cheap startup plan
prion / REL_19_STABLE:
diff -U3
/home/ec2-user/bf/root/REL_19_STABLE/pgsql/src/test/regress/expected/window.out
/home/ec2-user/bf/root/REL_19_STABLE/pgsql.build/src/test/modules/test_plan_advice/tmp_check/results/window.out
---
/home/ec2-user/bf/root/REL_19_STABLE/pgsql/src/test/regress/expected/window.out
2026-09-19 07:23:03.537952198 +0000
+++
/home/ec2-user/bf/root/REL_19_STABLE/pgsql.build/src/test/modules/test_plan_advice/tmp_check/results/window.out
2026-09-19 08:16:48.579365765 +0000
@@ -3634,7 +3634,7 @@
WindowAgg
Window: w1 AS (PARTITION BY f1 ORDER BY f2 RANGE BETWEEN
'1'::bigint PRECEDING AND '1'::bigint FOLLOWING)
-> Sort
- Sort Key: f1
+ Sort Key: f1, f2
-> Seq Scan on t1
Filter: (f1 = f2)
(6 rows)
@@ -3681,7 +3681,7 @@
WindowAgg
Window: w1 AS (PARTITION BY f1 ORDER BY f2 GROUPS BETWEEN
'1'::bigint PRECEDING AND '1'::bigint FOLLOWING)
-> Sort
- Sort Key: f1
+ Sort Key: f1, f2
-> Seq Scan on t1
Filter: (f1 = f2)
(6 rows)
turaca / master:
# # diff -U3
/mnt/data/buildfarm/buildroot/HEAD/pgsql.build/src/test/regress/expected/window.out
/mnt/data/buildfarm/buildroot/HEAD/pgsql.build/src/bin/pg_upgrade/tmp_check/results/window.out
# # ---
/mnt/data/buildfarm/buildroot/HEAD/pgsql.build/src/test/regress/expected/window.out
2026-09-19 07:53:30.000000000 +0100
# # +++
/mnt/data/buildfarm/buildroot/HEAD/pgsql.build/src/bin/pg_upgrade/tmp_check/results/window.out
2026-09-19 09:00:04.647674224 +0100
# # @@ -3634,7 +3634,7 @@
# # WindowAgg
# # Window: w1 AS (PARTITION BY f1 ORDER BY f2 RANGE BETWEEN
'1'::bigint PRECEDING AND '1'::bigint FOLLOWING)
# # -> Sort
# # - Sort Key: f1
# # + Sort Key: f1, f1
# # -> Seq Scan on t1
# # Filter: (f1 = f2)
# # (6 rows)
# # 1 of 239 tests failed.
# # The differences that caused some tests to fail can be viewed in
the file
"/mnt/data/buildfarm/buildroot/HEAD/pgsql.build/src/bin/pg_upgrade/tmp_check/regression.diffs".
# # A copy of the test summary that you see above is saved in the file
"/mnt/data/buildfarm/buildroot/HEAD/pgsql.build/src/bin/pg_upgrade/tmp_check/regression.out".
I don't see any relation between these failures and my commit. window
runs concurrently with foreign_key, but this commit's additions to the
latter are self-contained (a private operator family in its own
schema), so I don't see why window's plans would be affected.
Does anyone see it differently?
--
Thanks, Amit Langote
