pgsql: Invalidate RI fast-path metadata on operator family changes
pgsql: Invalidate RI fast-path metadata on operator family changes
От:
Amit 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 Author: Nikolay Samokhvalov 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(-)
Re: pgsql: Invalidate RI fast-path metadata on operator family changes
От:
Amit Langote <amitlangote09@gmail.com>
Дата:
Hi,
On Sat, Sep 19, 2026 at 3:59 PM Amit Langote 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
> Author: Nikolay Samokhvalov
> 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
Re: pgsql: Invalidate RI fast-path metadata on operator family changes
От:
Amit Langote <amitlangote09@gmail.com>
Дата:
On Sat, Sep 19, 2026 at 5:39 PM Amit Langote wrote: > On Sat, Sep 19, 2026 at 3:59 PM Amit Langote 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 > > Author: Nikolay Samokhvalov > > 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
Re: pgsql: Invalidate RI fast-path metadata on operator family changes
От:
Alexander Lakhin <exclusion@gmail.com>
Дата:
Hello Amit,
19.09.2026 12:14, Amit Langote wrote:
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
Re: pgsql: Invalidate RI fast-path metadata on operator family changes
От:
Richard Guo <guofenglinux@gmail.com>
Дата:
On Sun, Sep 20, 2026 at 3:00 AM Alexander Lakhin 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 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
Re: pgsql: Invalidate RI fast-path metadata on operator family changes
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Richard Guo writes:
> On Sun, Sep 20, 2026 at 3:00 AM Alexander Lakhin 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
Re: pgsql: Invalidate RI fast-path metadata on operator family changes
Hi, On Sun, Sep 20, 2026 at 12:20 PM Tom Lane wrote: > Richard Guo writes: > > On Sun, Sep 20, 2026 at 3:00 AM Alexander Lakhin 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
Re: pgsql: Invalidate RI fast-path metadata on operator family changes
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Amit Langote 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
Re: pgsql: Invalidate RI fast-path metadata on operator family changes
On Sun, Sep 20, 2026 at 12:34 PM Tom Lane wrote: > Amit Langote 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
Re: pgsql: Invalidate RI fast-path metadata on operator family changes
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Amit Langote 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
Re: pgsql: Invalidate RI fast-path metadata on operator family changes
От:
Amit Langote <amitlangote09@gmail.com>
Дата:
On Sun, Sep 20, 2026 at 1:20 PM Tom Lane wrote: > Amit Langote 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
Re: pgsql: Invalidate RI fast-path metadata on operator family changes
От:
Amit Langote <amitlangote09@gmail.com>
Дата:
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