agora inbox for pgsql-committers@postgresql.org
help / color / mirror / Atom feedpgsql: Invalidate RI fast-path metadata on operator family changes
13+ messages / 5 participants
[nested] [flat]
* pgsql: Invalidate RI fast-path metadata on operator family changes
@ 2026-09-19 06:59 Amit Langote <amitlan@postgresql.org>
0 siblings, 0 replies; 13+ messages in thread
From: Amit Langote @ 2026-09-19 06:59 UTC (permalink / raw)
To: pgsql-committers@lists.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
------
REL_19_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/c86738fd4a4c086353138d199190a34e738e6dd3
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(-)
^ permalink raw reply [nested|flat] 13+ messages in thread
* pgsql: Invalidate RI fast-path metadata on operator family changes
@ 2026-09-19 06:59 Amit Langote <amitlan@postgresql.org>
0 siblings, 1 reply; 13+ messages in thread
From: Amit Langote @ 2026-09-19 06:59 UTC (permalink / raw)
To: pgsql-committers@lists.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(-)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pgsql: Invalidate RI fast-path metadata on operator family changes
@ 2026-09-19 08:39 Amit Langote <amitlangote09@gmail.com>
parent: Amit Langote <amitlan@postgresql.org>
0 siblings, 1 reply; 13+ messages in thread
From: Amit Langote @ 2026-09-19 08:39 UTC (permalink / raw)
To: Amit Langote <amitlan@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pgsql: Invalidate RI fast-path metadata on operator family changes
@ 2026-09-19 09:14 Amit Langote <amitlangote09@gmail.com>
parent: Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Amit Langote @ 2026-09-19 09:14 UTC (permalink / raw)
To: Amit Langote <amitlan@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pgsql: Invalidate RI fast-path metadata on operator family changes
@ 2026-09-19 18:00 Alexander Lakhin <exclusion@gmail.com>
parent: Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Alexander Lakhin @ 2026-09-19 18:00 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; Amit Langote <amitlan@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pgsql: Invalidate RI fast-path metadata on operator family changes
@ 2026-09-20 00:43 Richard Guo <guofenglinux@gmail.com>
parent: Alexander Lakhin <exclusion@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Richard Guo @ 2026-09-20 00:43 UTC (permalink / raw)
To: Alexander Lakhin <exclusion@gmail.com>; +Cc: Amit Langote <amitlangote09@gmail.com>; Amit Langote <amitlan@postgresql.org>; pgsql-committers@lists.postgresql.org
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 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pgsql: Invalidate RI fast-path metadata on operator family changes
@ 2026-09-20 03:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2026-09-20 03:20 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Alexander Lakhin <exclusion@gmail.com>; Amit Langote <amitlangote09@gmail.com>; pgsql-committers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pgsql: Invalidate RI fast-path metadata on operator family changes
@ 2026-09-20 03:24 Amit Langote <amitlangote09@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Amit Langote @ 2026-09-20 03:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Guo <guofenglinux@gmail.com>; Alexander Lakhin <exclusion@gmail.com>; pgsql-committers@lists.postgresql.org
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:
[application/octet-stream] v1-0001-Attempt-to-fix-test-interference-from-foreign_key.patch (5.5K, ../../CA+HiwqGVi0yNBMEEd11CqUFE_iSRafOBGNXsEK5guDUivQWyhA@mail.gmail.com/2-v1-0001-Attempt-to-fix-test-interference-from-foreign_key.patch)
download | inline diff:
From 5c7f24a1c7cf95af96741c8df7d5e128d2a08961 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Sun, 20 Sep 2026 12:23:03 +0900
Subject: [PATCH v1] Attempt to fix test interference from foreign_key opfamily
test
The test added by c62b330912e creates a btree opfamily whose members
include built-in integer operators, which get_mergejoin_opfamilies()
finds regardless of schema. So any concurrently running test that
planned an integer join or sort could pick the family up while it
existed. That's the likely cause of the intermittent plan-shape
changes seen in window.sql and equivclass.sql on the buildfarm after
that commit.
Run the test inside a transaction that is rolled back, so the family
is never committed and never visible to other backends.
Per buildfarm members sifaka, prion, turaca, widowbird.
This will be back-patched to REL_19_STABLE after beta4v release
freeze is over.
Discussion: https://postgr.es/m/CA+HiwqGitHCmO7nvV4nXXYWDkNYA+zVcJj=JNmL3C1O9B0Gz0A@mail.gmail.com
---
src/test/regress/expected/foreign_key.out | 19 +++++++++++--------
src/test/regress/sql/foreign_key.sql | 19 +++++++++++--------
2 files changed, 22 insertions(+), 16 deletions(-)
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 861451ef291..40be9c4f75d 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1084,7 +1084,12 @@ DETAIL: Key columns "ptest4" of the referencing table and "ptest1" of the refer
-- Replace the equality operator the FK recorded with an identical
-- implementation, so only opfamily membership changes. The recorded operator
-- is now absent from the family; the fast path must fall back to SPI instead
--- of probing with it.
+-- of probing with it. Run inside a transaction that is rolled back: the
+-- family holds built-in integer operators, and the planner finds btree
+-- opfamilies by content (get_mergejoin_opfamilies), not by schema, so if it
+-- were committed it would be visible to concurrent tests and disturb their
+-- plans.
+begin;
create schema fk_opfamily;
set search_path = fk_opfamily, pg_catalog;
create operator family fam using btree;
@@ -1116,19 +1121,21 @@ insert into p values (1), (2);
insert into warm values (1);
-- Change only pg_amop. warm's cached metadata now names an operator the
-- opfamily no longer contains; cold is still evaluated fresh.
-begin;
alter operator family fam using btree drop operator 3(integer,bigint);
alter operator family fam using btree add operator 3 =#=(integer,bigint);
-commit;
-- A present key must be accepted and a missing one rejected, via SPI.
insert into warm values (2);
+savepoint s;
insert into warm values (99);
ERROR: insert or update on table "warm" violates foreign key constraint "warm_k_fkey"
DETAIL: Key (k)=(99) is not present in table "p".
+rollback to s;
insert into cold values (2);
+savepoint s;
insert into cold values (99);
ERROR: insert or update on table "cold" violates foreign key constraint "cold_k_fkey"
DETAIL: Key (k)=(99) is not present in table "p".
+rollback to s;
select * from warm order by k;
k
---
@@ -1143,11 +1150,7 @@ select * from cold order by k;
(1 row)
reset search_path;
-drop table fk_opfamily.warm, fk_opfamily.cold, fk_opfamily.p;
-drop operator class fk_opfamily.int_ops using btree;
-drop operator family fk_opfamily.fam using btree;
-drop operator fk_opfamily.=#=(integer,bigint);
-drop schema fk_opfamily;
+rollback;
--
-- Now some cases with inheritance
-- Basic 2 table case: 1 column of matching types.
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index b4d53a50b09..89405dff99e 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -750,7 +750,12 @@ ptest3) REFERENCES pktable);
-- Replace the equality operator the FK recorded with an identical
-- implementation, so only opfamily membership changes. The recorded operator
-- is now absent from the family; the fast path must fall back to SPI instead
--- of probing with it.
+-- of probing with it. Run inside a transaction that is rolled back: the
+-- family holds built-in integer operators, and the planner finds btree
+-- opfamilies by content (get_mergejoin_opfamilies), not by schema, so if it
+-- were committed it would be visible to concurrent tests and disturb their
+-- plans.
+begin;
create schema fk_opfamily;
set search_path = fk_opfamily, pg_catalog;
create operator family fam using btree;
@@ -784,24 +789,22 @@ insert into warm values (1);
-- Change only pg_amop. warm's cached metadata now names an operator the
-- opfamily no longer contains; cold is still evaluated fresh.
-begin;
alter operator family fam using btree drop operator 3(integer,bigint);
alter operator family fam using btree add operator 3 =#=(integer,bigint);
-commit;
-- A present key must be accepted and a missing one rejected, via SPI.
insert into warm values (2);
+savepoint s;
insert into warm values (99);
+rollback to s;
insert into cold values (2);
+savepoint s;
insert into cold values (99);
+rollback to s;
select * from warm order by k;
select * from cold order by k;
reset search_path;
-drop table fk_opfamily.warm, fk_opfamily.cold, fk_opfamily.p;
-drop operator class fk_opfamily.int_ops using btree;
-drop operator family fk_opfamily.fam using btree;
-drop operator fk_opfamily.=#=(integer,bigint);
-drop schema fk_opfamily;
+rollback;
--
-- Now some cases with inheritance
--
2.47.3
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pgsql: Invalidate RI fast-path metadata on operator family changes
@ 2026-09-20 03:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2026-09-20 03:34 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; +Cc: Richard Guo <guofenglinux@gmail.com>; Alexander Lakhin <exclusion@gmail.com>; pgsql-committers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pgsql: Invalidate RI fast-path metadata on operator family changes
@ 2026-09-20 04:10 Amit Langote <amitlangote09@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Amit Langote @ 2026-09-20 04:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Guo <guofenglinux@gmail.com>; Alexander Lakhin <exclusion@gmail.com>; pgsql-committers@lists.postgresql.org; pgsql-release@lists.postgresql.org
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:
[application/octet-stream] 0001-Attempt-to-fix-test-interference-from-foreign_key-op.patch (5.4K, ../../CA+HiwqG7dAM2+Jv6RJq6+0L2yS=W2x9A8AV9kNDP2Pa7DCk17w@mail.gmail.com/2-0001-Attempt-to-fix-test-interference-from-foreign_key-op.patch)
download | inline diff:
From 916990539208b2bcea4202ecae30b23dce8d130c Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Sun, 20 Sep 2026 13:06:00 +0900
Subject: [PATCH] Attempt to fix test interference from foreign_key opfamily
test
The test added by c62b330912e creates a btree opfamily whose members
include built-in integer operators, which get_mergejoin_opfamilies()
finds regardless of schema. So any concurrently running test that
planned an integer join or sort could pick the family up while it
existed. That's the likely cause of the intermittent plan-shape
changes seen in window.sql and equivclass.sql on the buildfarm after
that commit.
Run the test inside a transaction that is rolled back, so the family
is never committed and never visible to other backends.
Per buildfarm members sifaka, prion, turaca, widowbird.
Discussion: https://postgr.es/m/CA+HiwqGitHCmO7nvV4nXXYWDkNYA+zVcJj=JNmL3C1O9B0Gz0A@mail.gmail.com
Backpatch-though: 19
---
src/test/regress/expected/foreign_key.out | 19 +++++++++++--------
src/test/regress/sql/foreign_key.sql | 19 +++++++++++--------
2 files changed, 22 insertions(+), 16 deletions(-)
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 8a80d0b5b76..fa579480924 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1084,7 +1084,12 @@ DETAIL: Key columns "ptest4" of the referencing table and "ptest1" of the refer
-- Replace the equality operator the FK recorded with an identical
-- implementation, so only opfamily membership changes. The recorded operator
-- is now absent from the family; the fast path must fall back to SPI instead
--- of probing with it.
+-- of probing with it. Run inside a transaction that is rolled back: the
+-- family holds built-in integer operators, and the planner finds btree
+-- opfamilies by content (get_mergejoin_opfamilies), not by schema, so if it
+-- were committed it would be visible to concurrent tests and disturb their
+-- plans.
+begin;
create schema fk_opfamily;
set search_path = fk_opfamily, pg_catalog;
create operator family fam using btree;
@@ -1116,19 +1121,21 @@ insert into p values (1), (2);
insert into warm values (1);
-- Change only pg_amop. warm's cached metadata now names an operator the
-- opfamily no longer contains; cold is still evaluated fresh.
-begin;
alter operator family fam using btree drop operator 3(integer,bigint);
alter operator family fam using btree add operator 3 =#=(integer,bigint);
-commit;
-- A present key must be accepted and a missing one rejected, via SPI.
insert into warm values (2);
+savepoint s;
insert into warm values (99);
ERROR: insert or update on table "warm" violates foreign key constraint "warm_k_fkey"
DETAIL: Key (k)=(99) is not present in table "p".
+rollback to s;
insert into cold values (2);
+savepoint s;
insert into cold values (99);
ERROR: insert or update on table "cold" violates foreign key constraint "cold_k_fkey"
DETAIL: Key (k)=(99) is not present in table "p".
+rollback to s;
select * from warm order by k;
k
---
@@ -1143,11 +1150,7 @@ select * from cold order by k;
(1 row)
reset search_path;
-drop table fk_opfamily.warm, fk_opfamily.cold, fk_opfamily.p;
-drop operator class fk_opfamily.int_ops using btree;
-drop operator family fk_opfamily.fam using btree;
-drop operator fk_opfamily.=#=(integer,bigint);
-drop schema fk_opfamily;
+rollback;
--
-- Now some cases with inheritance
-- Basic 2 table case: 1 column of matching types.
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 1ae4193843e..aaf1d6d8dab 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -750,7 +750,12 @@ ptest3) REFERENCES pktable);
-- Replace the equality operator the FK recorded with an identical
-- implementation, so only opfamily membership changes. The recorded operator
-- is now absent from the family; the fast path must fall back to SPI instead
--- of probing with it.
+-- of probing with it. Run inside a transaction that is rolled back: the
+-- family holds built-in integer operators, and the planner finds btree
+-- opfamilies by content (get_mergejoin_opfamilies), not by schema, so if it
+-- were committed it would be visible to concurrent tests and disturb their
+-- plans.
+begin;
create schema fk_opfamily;
set search_path = fk_opfamily, pg_catalog;
create operator family fam using btree;
@@ -784,24 +789,22 @@ insert into warm values (1);
-- Change only pg_amop. warm's cached metadata now names an operator the
-- opfamily no longer contains; cold is still evaluated fresh.
-begin;
alter operator family fam using btree drop operator 3(integer,bigint);
alter operator family fam using btree add operator 3 =#=(integer,bigint);
-commit;
-- A present key must be accepted and a missing one rejected, via SPI.
insert into warm values (2);
+savepoint s;
insert into warm values (99);
+rollback to s;
insert into cold values (2);
+savepoint s;
insert into cold values (99);
+rollback to s;
select * from warm order by k;
select * from cold order by k;
reset search_path;
-drop table fk_opfamily.warm, fk_opfamily.cold, fk_opfamily.p;
-drop operator class fk_opfamily.int_ops using btree;
-drop operator family fk_opfamily.fam using btree;
-drop operator fk_opfamily.=#=(integer,bigint);
-drop schema fk_opfamily;
+rollback;
--
-- Now some cases with inheritance
--
2.47.3
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pgsql: Invalidate RI fast-path metadata on operator family changes
@ 2026-09-20 04:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2026-09-20 04:20 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; +Cc: Richard Guo <guofenglinux@gmail.com>; Alexander Lakhin <exclusion@gmail.com>; pgsql-committers@lists.postgresql.org; pgsql-release@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pgsql: Invalidate RI fast-path metadata on operator family changes
@ 2026-09-20 05:20 Amit Langote <amitlangote09@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Amit Langote @ 2026-09-20 05:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Guo <guofenglinux@gmail.com>; Alexander Lakhin <exclusion@gmail.com>; pgsql-committers@lists.postgresql.org; pgsql-release@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pgsql: Invalidate RI fast-path metadata on operator family changes
@ 2026-09-21 01:04 Amit Langote <amitlangote09@gmail.com>
parent: Amit Langote <amitlangote09@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Amit Langote @ 2026-09-21 01:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Guo <guofenglinux@gmail.com>; Alexander Lakhin <exclusion@gmail.com>; pgsql-committers@lists.postgresql.org; pgsql-release@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2026-09-21 01:04 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-19 06:59 pgsql: Invalidate RI fast-path metadata on operator family changes Amit Langote <amitlan@postgresql.org>
2026-09-19 06:59 pgsql: Invalidate RI fast-path metadata on operator family changes Amit Langote <amitlan@postgresql.org>
2026-09-19 08:39 ` Amit Langote <amitlangote09@gmail.com>
2026-09-19 09:14 ` Amit Langote <amitlangote09@gmail.com>
2026-09-19 18:00 ` Alexander Lakhin <exclusion@gmail.com>
2026-09-20 00:43 ` Richard Guo <guofenglinux@gmail.com>
2026-09-20 03:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-20 03:24 ` Amit Langote <amitlangote09@gmail.com>
2026-09-20 03:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-20 04:10 ` Amit Langote <amitlangote09@gmail.com>
2026-09-20 04:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-20 05:20 ` Amit Langote <amitlangote09@gmail.com>
2026-09-21 01:04 ` Amit Langote <amitlangote09@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox