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:
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

От:
Amit Langote <amitlangote09@gmail.com>
Дата:
Вложения:
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

От:
Amit Langote <amitlangote09@gmail.com>
Дата:
Вложения:
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
FAQ