Re: pgsql: Invalidate RI fast-path metadata on operator family changes - Mailing list pgsql-committers

Hi,

On Sat, Sep 19, 2026 at 3:59 PM Amit Langote <amitlan@postgresql.org> 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 <nik@postgres.ai>
> Author: Nikolay Samokhvalov <nik@postgres.ai>
> 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