pgsql: Invalidate RI fast-path metadata on operator family changes

Started by Amit Langote2 months ago12 messagescommitters
Jump to latest
#1Amit Langote
amitlan@postgresql.org

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(-)

#2Amit Langote
amitlangote09@gmail.com
In reply to: Amit Langote (#1)
Re: pgsql: Invalidate RI fast-path metadata on operator family changes

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

#3Amit Langote
amitlangote09@gmail.com
In reply to: Amit Langote (#2)
Re: pgsql: Invalidate RI fast-path metadata on operator family changes

On Sat, Sep 19, 2026 at 5:39 PM Amit Langote <amitlangote09@gmail.com> wrote:

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?

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

#4Alexander Lakhin
exclusion@gmail.com
In reply to: Amit Langote (#3)
Re: pgsql: Invalidate RI fast-path metadata on operator family changes

Hello Amit,

19.09.2026 12:14, Amit Langote wrote:

I noticed a few BF failures, after pushing the above changes:

...

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.

I've reproduced such failures locally with:
test: test_setup
test: create_index
test: foreign_key window
x100

I think the window failures are caused by these additions:
+create operator family fam using btree;
+create operator class int_ops for type integer using btree family fam as
+  operator 1 <(integer,integer), operator 2 <=(integer,integer),
+  operator 3 =(integer,integer), operator 4 >=(integer,integer),
+  operator 5 >(integer,integer), function 1 btint4cmp(integer,integer);

Best regards,
Alexander

#5Richard Guo
guofenglinux@gmail.com
In reply to: Alexander Lakhin (#4)
Re: pgsql: Invalidate RI fast-path metadata on operator family changes

On Sun, Sep 20, 2026 at 3:00 AM Alexander Lakhin <exclusion@gmail.com> wrote:

I think the window failures are caused by these additions:
+create operator family fam using btree;
+create operator class int_ops for type integer using btree family fam as
+  operator 1 <(integer,integer), operator 2 <=(integer,integer),
+  operator 3 =(integer,integer), operator 4 >=(integer,integer),
+  operator 5 >(integer,integer), function 1 btint4cmp(integer,integer);

FWIW, this seems to also cause the equivclass failure on widowbird [1]https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=widowbird&dt=2026-09-19%2019%3A50%3A41.

It seems to me what happens is that the new family adds operator 3
=(bigint,bigint), i.e. int8eq. So in

select * from ec0 a, ec1 b
where a.ff = b.ff and a.ff = 43::bigint::int8alias1;

get_mergejoin_opfamilies() gives a.ff = b.ff {integer_ops, fam} while
a.ff = 43::int8alias1 stays {integer_ops}, and process_equivalence()
requires those lists to be equal(). The two clauses land in separate
ECs, the constant never propagates to b.ff, and we get

  -         Index Cond: (ff = '43'::int8alias1)
  +         Index Cond: (ff = a.ff)

[1]: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=widowbird&dt=2026-09-19%2019%3A50%3A41

- Richard

#6Tom Lane
tgl@sss.pgh.pa.us
In reply to: Richard Guo (#5)
Re: pgsql: Invalidate RI fast-path metadata on operator family changes

Richard Guo <guofenglinux@gmail.com> writes:

On Sun, Sep 20, 2026 at 3:00 AM Alexander Lakhin <exclusion@gmail.com> wrote:

I think the window failures are caused by these additions:
+create operator family fam using btree;
+create operator class int_ops for type integer using btree family fam as
+  operator 1 <(integer,integer), operator 2 <=(integer,integer),
+  operator 3 =(integer,integer), operator 4 >=(integer,integer),
+  operator 5 >(integer,integer), function 1 btint4cmp(integer,integer);

FWIW, this seems to also cause the equivclass failure on widowbird [1].

It's easy to show that this is indeed what is breaking the window.sql
test cases:

regression=# create temp table t1 (f1 int, f2 int8);
insert into t1 values (1,1),(1,2),(2,2);
CREATE TABLE
INSERT 0 3
regression=# explain (costs off)
select f1, sum(f1) over (partition by f1 order by f2
range between 1 preceding and 1 following)
from t1 where f1 = f2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
WindowAgg
Window: w1 AS (PARTITION BY f1 ORDER BY f2 RANGE BETWEEN '1'::bigint PRECEDING AND '1'::bigint FOLLOWING)
-> Sort
Sort Key: f1
-> Seq Scan on t1
Filter: (f1 = f2)
(6 rows)

regression=# create operator family fam using btree;
CREATE OPERATOR FAMILY
regression=# create operator class int_ops for type integer using btree family fam as
regression-# operator 1 <(integer,integer), operator 2 <=(integer,integer),
regression-# operator 3 =(integer,integer), operator 4 >=(integer,integer),
regression-# operator 5 >(integer,integer), function 1 btint4cmp(integer,integer);
CREATE OPERATOR CLASS
regression=# explain (costs off)
select f1, sum(f1) over (partition by f1 order by f2
range between 1 preceding and 1 following)
from t1 where f1 = f2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
WindowAgg
Window: w1 AS (PARTITION BY f1 ORDER BY f2 RANGE BETWEEN '1'::bigint PRECEDING AND '1'::bigint FOLLOWING)
-> Sort
Sort Key: f1, f1
-> Seq Scan on t1
Filter: (f1 = f2)
(6 rows)

Since t1 is a temp table, the common instability explanations like
autovacuum don't hold water.

I didn't look closely at why this FK test needs to have a broken
operator class, but if it does, maybe you could put that whole test
into a transaction that rolls back, so other sessions never see it.

regards, tom lane

#7Amit Langote
amitlangote09@gmail.com
In reply to: Tom Lane (#6)
Re: pgsql: Invalidate RI fast-path metadata on operator family changes

Hi,

On Sun, Sep 20, 2026 at 12:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Richard Guo <guofenglinux@gmail.com> writes:

On Sun, Sep 20, 2026 at 3:00 AM Alexander Lakhin <exclusion@gmail.com> wrote:

I think the window failures are caused by these additions:
+create operator family fam using btree;
+create operator class int_ops for type integer using btree family fam as
+  operator 1 <(integer,integer), operator 2 <=(integer,integer),
+  operator 3 =(integer,integer), operator 4 >=(integer,integer),
+  operator 5 >(integer,integer), function 1 btint4cmp(integer,integer);

FWIW, this seems to also cause the equivclass failure on widowbird [1].

It's easy to show that this is indeed what is breaking the window.sql
test cases:

regression=# create temp table t1 (f1 int, f2 int8);
insert into t1 values (1,1),(1,2),(2,2);
CREATE TABLE
INSERT 0 3
regression=# explain (costs off)
select f1, sum(f1) over (partition by f1 order by f2
range between 1 preceding and 1 following)
from t1 where f1 = f2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
WindowAgg
Window: w1 AS (PARTITION BY f1 ORDER BY f2 RANGE BETWEEN '1'::bigint PRECEDING AND '1'::bigint FOLLOWING)
-> Sort
Sort Key: f1
-> Seq Scan on t1
Filter: (f1 = f2)
(6 rows)

regression=# create operator family fam using btree;
CREATE OPERATOR FAMILY
regression=# create operator class int_ops for type integer using btree family fam as
regression-# operator 1 <(integer,integer), operator 2 <=(integer,integer),
regression-# operator 3 =(integer,integer), operator 4 >=(integer,integer),
regression-# operator 5 >(integer,integer), function 1 btint4cmp(integer,integer);
CREATE OPERATOR CLASS
regression=# explain (costs off)
select f1, sum(f1) over (partition by f1 order by f2
range between 1 preceding and 1 following)
from t1 where f1 = f2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
WindowAgg
Window: w1 AS (PARTITION BY f1 ORDER BY f2 RANGE BETWEEN '1'::bigint PRECEDING AND '1'::bigint FOLLOWING)
-> Sort
Sort Key: f1, f1
-> Seq Scan on t1
Filter: (f1 = f2)
(6 rows)

Since t1 is a temp table, the common instability explanations like
autovacuum don't hold water.

I didn't look closely at why this FK test needs to have a broken
operator class, but if it does, maybe you could put that whole test
into a transaction that rolls back, so other sessions never see it.

Was just about to send a patch to do that. Attached here.

I'm thinking of pushing this to master first and see if it helps. I
note in my proposed commit message that back-patching to 19 is
deferred until beta4 freeze is over, but maybe I should not wait until
then?

Thank you all for chiming in.

--
Thanks, Amit Langote

Attachments:

v1-0001-Attempt-to-fix-test-interference-from-foreign_key.patchapplication/octet-stream; name=v1-0001-Attempt-to-fix-test-interference-from-foreign_key.patchDownload+22-15
#8Tom Lane
tgl@sss.pgh.pa.us
In reply to: Amit Langote (#7)
Re: pgsql: Invalidate RI fast-path metadata on operator family changes

Amit Langote <amitlangote09@gmail.com> writes:

I'm thinking of pushing this to master first and see if it helps. I
note in my proposed commit message that back-patching to 19 is
deferred until beta4 freeze is over, but maybe I should not wait until
then?

Formally, we're in release freeze on v19, so you should get the
concurrence of pgsql-release@ before pushing something into the
v19 branch this weekend. It's going to be hard to get meaningful
input though (seeing that it's late Saturday night USA time, and
very early Sunday morning European time). Moreover, the longer
you wait, the fewer buildfarm runs will happen before release wrap;
and it being a weekend there's not going to be a lot of commit
activity to help runs happen. So there's no way around some risk
here.

Having said that, the failure rate seems high enough to be
annoying (I see six BF failures across master and v19 in the
24-ish hours since this went in), and the patch looks pretty
safe. So I think my vote is to push. I'd counsel asking
pgsql-release@ as a matter of formality, and pushing if no
objections arrive within a couple of hours.

regards, tom lane

#9Amit Langote
amitlangote09@gmail.com
In reply to: Tom Lane (#8)
Re: pgsql: Invalidate RI fast-path metadata on operator family changes

On Sun, Sep 20, 2026 at 12:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Amit Langote <amitlangote09@gmail.com> writes:

I'm thinking of pushing this to master first and see if it helps. I
note in my proposed commit message that back-patching to 19 is
deferred until beta4 freeze is over, but maybe I should not wait until
then?

Formally, we're in release freeze on v19, so you should get the
concurrence of pgsql-release@ before pushing something into the
v19 branch this weekend. It's going to be hard to get meaningful
input though (seeing that it's late Saturday night USA time, and
very early Sunday morning European time). Moreover, the longer
you wait, the fewer buildfarm runs will happen before release wrap;
and it being a weekend there's not going to be a lot of commit
activity to help runs happen. So there's no way around some risk
here.

Having said that, the failure rate seems high enough to be
annoying (I see six BF failures across master and v19 in the
24-ish hours since this went in), and the patch looks pretty
safe. So I think my vote is to push. I'd counsel asking
pgsql-release@ as a matter of formality, and pushing if no
objections arrive within a couple of hours.

Thanks Tom.

Added pgsql-release. For context: the change is regression-test-only.
It wraps the operator-family test added by c62b330912e in a
rolled-back transaction so the family is never visible to concurrently
running tests, which has been causing intermittent plan-shape failures
in window.sql and equivclass.sql on several buildfarm animals (six in
the last ~24h as Tom noted).

Patch I would like to push is attached. I'd like concurrence to push
it to REL_19_STABLE during the freeze.

I'll push to both branches around 15:30 JST (06:30 UTC) unless I hear
objections.

Sorry about the weekend rush.

--
Thanks, Amit Langote

Attachments:

0001-Attempt-to-fix-test-interference-from-foreign_key-op.patchapplication/octet-stream; name=0001-Attempt-to-fix-test-interference-from-foreign_key-op.patchDownload+22-15
#10Tom Lane
tgl@sss.pgh.pa.us
In reply to: Amit Langote (#9)
Re: pgsql: Invalidate RI fast-path metadata on operator family changes

Amit Langote <amitlangote09@gmail.com> writes:

Added pgsql-release. For context: the change is regression-test-only.
It wraps the operator-family test added by c62b330912e in a
rolled-back transaction so the family is never visible to concurrently
running tests, which has been causing intermittent plan-shape failures
in window.sql and equivclass.sql on several buildfarm animals (six in
the last ~24h as Tom noted).
Patch I would like to push is attached. I'd like concurrence to push
it to REL_19_STABLE during the freeze.

To add to that: we've got about 40 hours before the 19beta4 wrap.
Given that these buildfarm failures are intermittent, that's already
not enough time to be 100% sure that the patch fixes them --- I think
it will, but perhaps not. However, the worst-case outcome is that
this makes things visibly worse, in which case we could revert it
sometime Monday and be no worse off than we are now. So my vote is
to go ahead, and not be too slow about that.

regards, tom lane

#11Amit Langote
amitlangote09@gmail.com
In reply to: Tom Lane (#10)
Re: pgsql: Invalidate RI fast-path metadata on operator family changes

On Sun, Sep 20, 2026 at 1:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Amit Langote <amitlangote09@gmail.com> writes:

Added pgsql-release. For context: the change is regression-test-only.
It wraps the operator-family test added by c62b330912e in a
rolled-back transaction so the family is never visible to concurrently
running tests, which has been causing intermittent plan-shape failures
in window.sql and equivclass.sql on several buildfarm animals (six in
the last ~24h as Tom noted).
Patch I would like to push is attached. I'd like concurrence to push
it to REL_19_STABLE during the freeze.

To add to that: we've got about 40 hours before the 19beta4 wrap.
Given that these buildfarm failures are intermittent, that's already
not enough time to be 100% sure that the patch fixes them --- I think
it will, but perhaps not. However, the worst-case outcome is that
this makes things visibly worse, in which case we could revert it
sometime Monday and be no worse off than we are now. So my vote is
to go ahead, and not be too slow about that.

OK, I've pushed to both master and 19.

Thanks a lot for being available late to advise.

--
Thanks, Amit Langote

#12Amit Langote
amitlangote09@gmail.com
In reply to: Amit Langote (#11)
Re: pgsql: Invalidate RI fast-path metadata on operator family changes

On Sun, Sep 20, 2026 at 14:20 Amit Langote <amitlangote09@gmail.com> wrote:

On Sun, Sep 20, 2026 at 1:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Amit Langote <amitlangote09@gmail.com> writes:

Added pgsql-release. For context: the change is regression-test-only.
It wraps the operator-family test added by c62b330912e in a
rolled-back transaction so the family is never visible to concurrently
running tests, which has been causing intermittent plan-shape failures
in window.sql and equivclass.sql on several buildfarm animals (six in
the last ~24h as Tom noted).
Patch I would like to push is attached. I'd like concurrence to push
it to REL_19_STABLE during the freeze.

To add to that: we've got about 40 hours before the 19beta4 wrap.
Given that these buildfarm failures are intermittent, that's already
not enough time to be 100% sure that the patch fixes them --- I think
it will, but perhaps not. However, the worst-case outcome is that
this makes things visibly worse, in which case we could revert it
sometime Monday and be no worse off than we are now. So my vote is
to go ahead, and not be too slow about that.

OK, I've pushed to both master and 19.

16 hours in and no new reds so far, so the fix seems to have worked.

- Amit

Show quoted text