On Sat, Sep 19, 2026 at 5:39 PM Amit Langote <[email protected]> wrote: > 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?
Forgot to add that the code change is isolated too in that it only changes what ri_triggers.c does on a pg_amop invalidation during an RI fast-path check, not anything the planner consults. It doesn't touch pg_constraint or how FKs feed planning, so I don't see a route from this commit's changes to these window plans. Any effect would be timing at most. I would not claim that I have figured this out. -- Thanks, Amit Langote
