pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedRouted ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
16+ messages / 7 participants
[nested] [flat]
* Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
@ 2026-08-28 20:19 Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 08:07 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Michael Paquier <michael@paquier.xyz>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
0 siblings, 2 replies; 16+ messages in thread
From: Zsolt Parragi @ 2026-08-28 20:19 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hello,
While testing ON CONFLICT on partitioned tables on master, I found
that a deferrable unique constraint on a leaf partition breaks every
routed insert that takes the no-conflict path. This is a regression
from commit 90eae926abbb (Fix ON CONFLICT with REINDEX CONCURRENTLY
and partitions[1]), so it affects master and the 19 betas, but not 18.
This is one of the issues I found with cross-checking feature
interactions with Claude[2], and I thoght I'll submit this first since
this is a PG19 regression.
Reproducer:
CREATE TABLE d (a int, b text, PRIMARY KEY (a)) PARTITION BY RANGE (a);
CREATE TABLE d1 PARTITION OF d FOR VALUES FROM (0) TO (100);
ALTER TABLE d1 ADD CONSTRAINT d1_a_def UNIQUE (a) DEFERRABLE;
INSERT INTO d VALUES (1, 'one');
-- works: conflict found on d1_pkey
INSERT INTO d VALUES (1, 'ONE') ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b;
-- ERROR: ON CONFLICT does not support deferrable unique
-- constraints/exclusion constraints as arbiters
INSERT INTO d VALUES (2, 'two') ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b;
INSERT INTO d VALUES (3, 'three') ON CONFLICT (a) DO NOTHING;
All four inserts succeed on 18.
Since 90eae926abbb, ExecInitPartitionInfo matches every leaf index
that has no parent against the arbiters mapped from the root.
IsIndexCompatibleAsArbiter compares several properties, but not
indimmediate.
The loop returns early, so statements might work or fail based on how
the table/index was created, which suggests an unintended oversight,
not an intentional change.
For example if I just slightly modify the above repro, the previously
successful insert also fails:
CREATE TABLE d (a int, b text, PRIMARY KEY (a)) PARTITION BY RANGE (a);
CREATE TABLE d1 (a int NOT NULL, b text);
ALTER TABLE d1 ADD CONSTRAINT d1_a_def UNIQUE (a) DEFERRABLE;
ALTER TABLE d ATTACH PARTITION d1 FOR VALUES FROM (0) TO (100);
INSERT INTO d VALUES (1, 'one');
-- ERROR (worked in the first setup)
INSERT INTO d VALUES (1, 'ONE') ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b;
If instead I modify the first snippet to make the index also NULLS NOT
DISTINCT, all 4 inserts succeed.
The attached patch adds the missing indimmediate comparison and
restores the PG18 and earlier behavior.
[1]: https://postgr.es/m/CANtu0ojXmqjmEzp-=aJSxjsdE76iAsRgHBoK0QtYHimb_mEfsg@mail.gmail.com
[2]: https://postgr.es/m/CAN4CZFPBcRObk2sHJKidnuN7hJ_fG7QCdim%3DYnrN1sjSLFN68A%40mail.gmail.com
Attachments:
[application/octet-stream] v1-0001-Don-t-use-deferrable-indexes-as-additional-ON-CON.patch (5.0K, ../../CAN4CZFPEYXeYFTxHpoPujfVFb+1Tx1jnXVboDMBg-ZhpgpQ-_g@mail.gmail.com/2-v1-0001-Don-t-use-deferrable-indexes-as-additional-ON-CON.patch)
download | inline diff:
From 6460709341919bc50e5d701a13be3dafd71d2a0c Mon Sep 17 00:00:00 2001
From: Zsolt Parragi <zsolt.parragi@percona.com>
Date: Fri, 28 Aug 2026 13:07:34 +0000
Subject: [PATCH v1] Don't use deferrable indexes as additional ON CONFLICT
arbiters
Commit 90eae926abbb made tuple routing match unparented partition
indexes against the mapped arbiters, so that indexes created by a
concurrent REINDEX are used as arbiters too. The compatibility check
did not compare enforcement timing, so a partition-local deferrable
unique constraint on the arbiter columns was also chosen, and the
executor then rejected it with "ON CONFLICT does not support deferrable
unique constraints/exclusion constraints as arbiters". Whether a
given insert failed depended on the data and on index OID order, since
the pre-check only reaches the deferrable index when no earlier arbiter
reports a conflict.
To fix, require indimmediate to match as well. Indexes differing in
enforcement timing cannot be REINDEX CONCURRENTLY twins, so the case
the matching was added for is unaffected.
---
src/backend/executor/execPartition.c | 9 +++++++++
src/test/regress/expected/insert_conflict.out | 20 +++++++++++++++++++
src/test/regress/sql/insert_conflict.sql | 14 +++++++++++++
3 files changed, 43 insertions(+)
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
index 0e938cfea1f..c9630a3ad87 100644
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -512,6 +512,15 @@ IsIndexCompatibleAsArbiter(Relation arbiterIndexRelation,
if (arbiterIndexInfo->ii_Unique != indexInfo->ii_Unique)
return false;
+ /*
+ * Constraint enforcement timing must match, or we could end up choosing
+ * a deferrable index as arbiter, which ExecCheckIndexConstraints
+ * rejects.
+ */
+ if (arbiterIndexRelation->rd_index->indimmediate !=
+ indexRelation->rd_index->indimmediate)
+ return false;
+
/* No support currently for comparing exclusion indexes. */
if (arbiterIndexInfo->ii_ExclusionOps != NULL ||
indexInfo->ii_ExclusionOps != NULL)
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 34e2e7ee355..9d226042a69 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -1063,6 +1063,26 @@ insert into parted_conflict values (40, 'forty');
insert into parted_conflict_1 values (40, 'cuarenta')
on conflict (a) do update set b = excluded.b;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
+drop table parted_conflict;
+-- a partition-local deferrable unique constraint on the arbiter columns
+-- must not be picked up as an additional arbiter for routed inserts
+create table parted_conflict (a int, b text, primary key (a)) partition by range (a);
+create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000);
+alter table parted_conflict_1 add constraint parted_conflict_1_a_def unique (a) deferrable;
+insert into parted_conflict values (40, 'forty');
+insert into parted_conflict values (40, 'cuarenta')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (41, 'forty-one')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (42, 'forty-two') on conflict (a) do nothing;
+select * from parted_conflict order by a;
+ a | b
+----+-----------
+ 40 | cuarenta
+ 41 | forty-one
+ 42 | forty-two
+(3 rows)
+
drop table parted_conflict;
-- test whole-row Vars in ON CONFLICT expressions
create table parted_conflict (a int, b text, c int) partition by range (a);
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index a5a84d1d4b8..76d794fa3e4 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -619,6 +619,20 @@ insert into parted_conflict_1 values (40, 'cuarenta')
on conflict (a) do update set b = excluded.b;
drop table parted_conflict;
+-- a partition-local deferrable unique constraint on the arbiter columns
+-- must not be picked up as an additional arbiter for routed inserts
+create table parted_conflict (a int, b text, primary key (a)) partition by range (a);
+create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000);
+alter table parted_conflict_1 add constraint parted_conflict_1_a_def unique (a) deferrable;
+insert into parted_conflict values (40, 'forty');
+insert into parted_conflict values (40, 'cuarenta')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (41, 'forty-one')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (42, 'forty-two') on conflict (a) do nothing;
+select * from parted_conflict order by a;
+drop table parted_conflict;
+
-- test whole-row Vars in ON CONFLICT expressions
create table parted_conflict (a int, b text, c int) partition by range (a);
create table parted_conflict_1 (drp text, c int, a int, b text);
--
2.55.0
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
@ 2026-08-31 08:07 ` Michael Paquier <michael@paquier.xyz>
1 sibling, 0 replies; 16+ messages in thread
From: Michael Paquier @ 2026-08-31 08:07 UTC (permalink / raw)
To: Zsolt Parragi <zsolt.parragi@percona.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Fri, Aug 28, 2026 at 09:19:52PM +0100, Zsolt Parragi wrote:
> While testing ON CONFLICT on partitioned tables on master, I found
> that a deferrable unique constraint on a leaf partition breaks every
> routed insert that takes the no-conflict path. This is a regression
> from commit 90eae926abbb (Fix ON CONFLICT with REINDEX CONCURRENTLY
> and partitions[1]), so it affects master and the 19 betas, but not 18.
Thanks for the report.
> Since 90eae926abbb, ExecInitPartitionInfo matches every leaf index
> that has no parent against the arbiters mapped from the root.
> IsIndexCompatibleAsArbiter compares several properties, but not
> indimmediate.
This is new as of v19, but I also feel responsible for missing the
fact that indisimmediate was incorrect until 74276e685dd0, causing
random constraint errors during a concurrent build. So we could say
that 90eae926abbb got inspiration from the pre-74276e685dd0 code in
terms of the index copies created.
> This is one of the issues I found with cross-checking feature
> interactions with Claude[2], and I thoght I'll submit this first since
> this is a PG19 regression.
>
> Reproducer:
> [...]
> -- ERROR: ON CONFLICT does not support deferrable unique
> -- constraints/exclusion constraints as arbiters
> INSERT INTO d VALUES (2, 'two') ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b;
> INSERT INTO d VALUES (3, 'three') ON CONFLICT (a) DO NOTHING;
That's nice.
I'll look at all that tomorrow in depth. Just too tired today to do
so now. :p
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../apU2JNlhLS79uhOZ@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
@ 2026-08-31 10:00 ` Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Mihail Nikalayeu @ 2026-08-31 10:00 UTC (permalink / raw)
To: Zsolt Parragi <zsolt.parragi@percona.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Álvaro Herrera <alvherre@kurilemu.de>
Hello!
Thanks for the report!
Looks like my fault, I'll also recheck everything a little bit later.
Best regards,
Mikhail.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
@ 2026-08-31 22:21 ` Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 23:47 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
0 siblings, 1 reply; 16+ messages in thread
From: Mihail Nikalayeu @ 2026-08-31 22:21 UTC (permalink / raw)
To: Zsolt Parragi <zsolt.parragi@percona.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Álvaro Herrera <alvherre@kurilemu.de>
Hello!
What I understood - the issue is a little bit wider, it also may
appear on a named constraint - there it is not checked for both
`deferrable` and `nulls not distinct` to be matching original index
itself.
Going to do something with it in near future.
Mikhail.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
@ 2026-08-31 23:47 ` Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-01 06:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 16+ messages in thread
From: Zsolt Parragi @ 2026-08-31 23:47 UTC (permalink / raw)
To: Mihail Nikalayeu <mihailnikalayeu@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
On Mon, 31 Aug 2026, Mihail Nikalayeu <mihailnikalayeu@gmail.com> wrote:
> What I understood - the issue is a little bit wider, it also may
> appear on a named constraint - there it is not checked for both
> `deferrable` and `nulls not distinct` to be matching original index
> itself.
I didn't add tests about this in the patch, but it should also handle
these partition-local cases, e.g.
CREATE TABLE d (a int, b text, CONSTRAINT d_pk PRIMARY KEY (a))
PARTITION BY RANGE (a);
CREATE TABLE d1 PARTITION OF d FOR VALUES FROM (0) TO (100);
ALTER TABLE d1 ADD CONSTRAINT d1_a_def UNIQUE (a) DEFERRABLE;
INSERT INTO d VALUES (1, 'one');
INSERT INTO d VALUES (2, 'two') ON CONFLICT ON CONSTRAINT d_pk DO
UPDATE SET b = EXCLUDED.b;
-- master: ERROR; patched: INSERT 0 1
Also, now that I took another look into this, a somewhat similar PG19
regression exists outside partitions tables, caused by a different
commit (2bc7e886fc1):
CREATE TABLE t (a int, b text, CONSTRAINT t_u UNIQUE (a));
ALTER TABLE t ADD CONSTRAINT t_nnd UNIQUE NULLS NOT DISTINCT (a);
INSERT INTO t VALUES (NULL, 'one');
INSERT INTO t VALUES (NULL, 'two') ON CONFLICT ON CONSTRAINT t_u DO
UPDATE SET b = 'upd';
-- 18: ERROR: duplicate key value violates unique constraint "t_nnd"
-- (named arbiter t_u sees no conflict on NULLs, insert proceeds,
t_nnd rejects)
-- 19: INSERT 0 1 -> table now (NULL, 'upd')
-- (t_nnd silently used as arbiter user never named; error
swallowed, row updated)
But that seems like a different issue requiring a different fix.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 23:47 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
@ 2026-09-01 06:00 ` Michael Paquier <michael@paquier.xyz>
2026-09-01 09:15 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Michael Paquier @ 2026-09-01 06:00 UTC (permalink / raw)
To: Zsolt Parragi <zsolt.parragi@percona.com>; +Cc: Mihail Nikalayeu <mihailnikalayeu@gmail.com>; pgsql-hackers@lists.postgresql.org
On Mon, Aug 31, 2026 at 07:47:29PM -0400, Zsolt Parragi wrote:
> I didn't add tests about this in the patch, but it should also handle
> these partition-local cases, e.g.
>
> CREATE TABLE d (a int, b text, CONSTRAINT d_pk PRIMARY KEY (a))
> PARTITION BY RANGE (a);
> CREATE TABLE d1 PARTITION OF d FOR VALUES FROM (0) TO (100);
> ALTER TABLE d1 ADD CONSTRAINT d1_a_def UNIQUE (a) DEFERRABLE;
> INSERT INTO d VALUES (1, 'one');
> INSERT INTO d VALUES (2, 'two') ON CONFLICT ON CONSTRAINT d_pk DO
> UPDATE SET b = EXCLUDED.b;
> -- master: ERROR; patched: INSERT 0 1
Nice. Perhaps the tests should be expanded for this pattern? Named
constraints seems also worth caring about as ON CONFLICT allows this
pattern..
> Also, now that I took another look into this, a somewhat similar PG19
> regression exists outside partitions tables, caused by a different
> commit (2bc7e886fc1):
>
> CREATE TABLE t (a int, b text, CONSTRAINT t_u UNIQUE (a));
> ALTER TABLE t ADD CONSTRAINT t_nnd UNIQUE NULLS NOT DISTINCT (a);
> INSERT INTO t VALUES (NULL, 'one');
>
> INSERT INTO t VALUES (NULL, 'two') ON CONFLICT ON CONSTRAINT t_u DO
> UPDATE SET b = 'upd';
> -- 18: ERROR: duplicate key value violates unique constraint "t_nnd"
> -- (named arbiter t_u sees no conflict on NULLs, insert proceeds,
> t_nnd rejects)
> -- 19: INSERT 0 1 -> table now (NULL, 'upd')
> -- (t_nnd silently used as arbiter user never named; error
> swallowed, row updated)
>
> But that seems like a different issue requiring a different fix.
Ugh. This family of failures is super annoying because they do no
require a rebuilt index at all.
2bc7e886fc1 and the commit that has triggered this thread both refer
to the same set of improvements regarding the interactions of rebuilt
indexes and ON CONFLICT, so my take is that if we finish by updating
the same area of the code we could just group both changes together,
but I agree that my line of thoughts may be thin. It seems to me that
we'd better look first at this secondary issue before drawing a
conclusion regarding if both issues should be handled together or
separately.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../apZqARb5v6mTXoas@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 23:47 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-01 06:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Michael Paquier <michael@paquier.xyz>
@ 2026-09-01 09:15 ` Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-09-01 22:05 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-16 20:56 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Dmitry Dolgov <9erthalion6@gmail.com>
0 siblings, 2 replies; 16+ messages in thread
From: Mihail Nikalayeu @ 2026-09-01 09:15 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Zsolt Parragi <zsolt.parragi@percona.com>; pgsql-hackers@lists.postgresql.org
Hello!
> 2bc7e886fc1 and the commit that has triggered this thread both refer
> to the same set of improvements regarding the interactions of rebuilt
> indexes and ON CONFLICT, so my take is that if we finish by updating
> the same area of the code we could just group both changes together,
> but I agree that my line of thoughts may be thin. It seems to me that
> we'd better look first at this secondary issue before drawing a
> conclusion regarding if both issues should be handled together or
> separately.
This is a "grouped" version. Also, it handles possible collation
issues + provides a set of tests to pin the correct behaviour.
I haven't reviewed the tests too deeply (AI-generated) but they pass
on 18 (expect DO SELECT cases) and on the fixed version, failing on
the pre-fix.
Super-short version:
* move IsIndexCompatibleAsArbiter to index.c with a slight signature change
* handle `indimmediate` in it
* use `equal` for lists instead of list_difference
* use IsIndexCompatibleAsArbiter for both - partitioned and named
constraint cases
* simply infer_arbiter_indexes a bit (reverting part of 2bc7e886fc1b)
Best regards,
Mikhail.
Attachments:
[application/octet-stream] v2-0001-Require-exact-equivalence-for-additional-ON-CONFL.patch (28.4K, ../../CADzfLwVjwqca1Ot2saJ0XmcUQpCCiq4cLDnrtWmK0BHqOd_Mjw@mail.gmail.com/2-v2-0001-Require-exact-equivalence-for-additional-ON-CONFL.patch)
download | inline diff:
From 88ab1409bba307c03ff7f12eecc47848dc10e460 Mon Sep 17 00:00:00 2001
From: Mikhail Nikalayeu <mihailnikalayeu@gmail.com>
Date: Tue, 1 Sep 2026 10:45:45 +0200
Subject: [PATCH v2] Require exact equivalence for additional ON CONFLICT
arbiter indexes
Commits 2bc7e886fc1b and 90eae926abbb taught ON CONFLICT to include indexes
matching an already selected arbiter, so that an index left behind by
REINDEX CONCURRENTLY continues to arbitrate together with its
replacement. Both checks were too permissive.
infer_arbiter_indexes() compared a candidate with a named constraint's
index using only attributes, expressions and predicate. As a result, an
index with a different collation or NULLS NOT DISTINCT setting could be
accepted even though it did not identify the same conflicts. A matching
deferrable index could also cause ON CONFLICT to fail with "ON CONFLICT
does not support deferrable unique constraints/exclusion constraints as
arbiters".
Likewise, IsIndexCompatibleAsArbiter() compared all relevant properties
except indimmediate, so a partition-local deferrable unique constraint on
the arbiter columns could break routed inserts with the same error.
Use a single comparison in both places that requires equality of all
properties affecting which tuples an index considers duplicates.
Also handle the named-constraint case in infer_arbiter_indexes()
separately instead of passing the constraint index through the regular
clause-inference matching.
Add tests for ON CONFLICT ON CONSTRAINT with deferrable,
NULLS NOT DISTINCT, and different-collation sibling indexes, preserving
the behavior of released pre-19 versions. Also test routed inserts with
a partition-local deferrable unique constraint.
Author: Zsolt Parragi <zsolt.parragi@percona.com>
Author: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Reported-by: Zsolt Parragi <zsolt.parragi@percona.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/CAN4CZFPEYXeYFTxHpoPujfVFb+1Tx1jnXVboDMBg-ZhpgpQ-_g@mail.gmail.com
---
src/backend/catalog/index.c | 76 +++++++++++++
src/backend/executor/execPartition.c | 65 +----------
src/backend/optimizer/util/plancat.c | 102 +++++++-----------
src/include/catalog/index.h | 2 +
.../regress/expected/collate.icu.utf8.out | 28 +++++
src/test/regress/expected/insert_conflict.out | 96 +++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 18 ++++
src/test/regress/sql/insert_conflict.sql | 59 ++++++++++
8 files changed, 319 insertions(+), 127 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ec21b83b6b8..8b0eb261dda 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2556,6 +2556,82 @@ BuildDummyIndexInfo(Relation index)
return ii;
}
+/*
+ * IsIndexCompatibleAsArbiter
+ * Return true if two indexes of the same table are interchangeable as
+ * speculative insertion arbiters for INSERT ON CONFLICT.
+ *
+ * To be interchangeable, the two indexes must agree on which tuples conflict,
+ * so every property bearing on that must be identical: uniqueness, immediacy
+ * of enforcement, treatment of nulls, key columns with their collations and
+ * operator families, index expressions and predicate. Indexes that merely
+ * index the same columns can each have their own notion of what a duplicate
+ * is, and treating one as arbiter in place of the other would resolve
+ * conflicts the other does not have.
+ *
+ * The point of recognizing such twin indexes is REINDEX CONCURRENTLY: while
+ * it processes an arbiter index, an exact copy of it built by
+ * index_create_copy() exists alongside, and both copies must arbitrate
+ * together for all concurrent sessions to agree on the set of arbiters.
+ *
+ * Properties that do not affect which tuples conflict, such as validity and
+ * readiness of the indexes, are deliberately not examined here; callers
+ * check them as needed.
+ */
+bool
+IsIndexCompatibleAsArbiter(Relation indexRel1, Relation indexRel2)
+{
+ Form_pg_index indexForm1 = indexRel1->rd_index;
+ Form_pg_index indexForm2 = indexRel2->rd_index;
+
+ /* Only indexes of the same relation can be compared. */
+ Assert(indexForm1->indrelid == indexForm2->indrelid);
+
+ /* must match whether they're unique */
+ if (indexForm1->indisunique != indexForm2->indisunique)
+ return false;
+
+ /* No support currently for comparing exclusion indexes. */
+ if (indexForm1->indisexclusion || indexForm2->indisexclusion)
+ return false;
+
+ /* a deferrable index detects conflicts at a different time */
+ if (indexForm1->indimmediate != indexForm2->indimmediate)
+ return false;
+
+ /* the "nulls not distinct" criterion must match */
+ if (indexForm1->indnullsnotdistinct != indexForm2->indnullsnotdistinct)
+ return false;
+
+ /* number of key attributes must match */
+ if (indexForm1->indnkeyatts != indexForm2->indnkeyatts)
+ return false;
+
+ /* key columns, and their collations and opfamilies, must match */
+ for (int i = 0; i < indexForm1->indnkeyatts; i++)
+ {
+ if (indexForm1->indkey.values[i] != indexForm2->indkey.values[i])
+ return false;
+
+ if (indexRel1->rd_indcollation[i] != indexRel2->rd_indcollation[i])
+ return false;
+
+ if (indexRel1->rd_opfamily[i] != indexRel2->rd_opfamily[i])
+ return false;
+ }
+
+ /* index expressions and predicate must match */
+ if (!equal(RelationGetIndexExpressions(indexRel1),
+ RelationGetIndexExpressions(indexRel2)))
+ return false;
+
+ if (!equal(RelationGetIndexPredicate(indexRel1),
+ RelationGetIndexPredicate(indexRel2)))
+ return false;
+
+ return true;
+}
+
/*
* CompareIndexInfo
* Return whether the properties of two indexes (in different tables)
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
index 0e938cfea1f..23f5ccad0dc 100644
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -493,65 +493,6 @@ ExecFindPartition(ModifyTableState *mtstate,
return rri;
}
-/*
- * IsIndexCompatibleAsArbiter
- * Return true if two indexes are identical for INSERT ON CONFLICT
- * purposes.
- *
- * Only indexes of the same relation are supported.
- */
-static bool
-IsIndexCompatibleAsArbiter(Relation arbiterIndexRelation,
- IndexInfo *arbiterIndexInfo,
- Relation indexRelation,
- IndexInfo *indexInfo)
-{
- Assert(arbiterIndexRelation->rd_index->indrelid == indexRelation->rd_index->indrelid);
-
- /* must match whether they're unique */
- if (arbiterIndexInfo->ii_Unique != indexInfo->ii_Unique)
- return false;
-
- /* No support currently for comparing exclusion indexes. */
- if (arbiterIndexInfo->ii_ExclusionOps != NULL ||
- indexInfo->ii_ExclusionOps != NULL)
- return false;
-
- /* the "nulls not distinct" criterion must match */
- if (arbiterIndexInfo->ii_NullsNotDistinct !=
- indexInfo->ii_NullsNotDistinct)
- return false;
-
- /* number of key attributes must match */
- if (arbiterIndexInfo->ii_NumIndexKeyAttrs !=
- indexInfo->ii_NumIndexKeyAttrs)
- return false;
-
- for (int i = 0; i < arbiterIndexInfo->ii_NumIndexKeyAttrs; i++)
- {
- if (arbiterIndexRelation->rd_indcollation[i] !=
- indexRelation->rd_indcollation[i])
- return false;
-
- if (arbiterIndexRelation->rd_opfamily[i] !=
- indexRelation->rd_opfamily[i])
- return false;
-
- if (arbiterIndexRelation->rd_index->indkey.values[i] !=
- indexRelation->rd_index->indkey.values[i])
- return false;
- }
-
- if (list_difference(RelationGetIndexExpressions(arbiterIndexRelation),
- RelationGetIndexExpressions(indexRelation)) != NIL)
- return false;
-
- if (list_difference(RelationGetIndexPredicate(arbiterIndexRelation),
- RelationGetIndexPredicate(indexRelation)) != NIL)
- return false;
- return true;
-}
-
/*
* ExecInitPartitionInfo
* Lock the partition and initialize ResultRelInfo. Also setup other
@@ -846,19 +787,15 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
foreach_int(arbiter_i, arbiters_listidxs)
{
Relation arbiter_rel;
- IndexInfo *arbiter_ii;
arbiter_rel = leaf_part_rri->ri_IndexRelationDescs[arbiter_i];
- arbiter_ii = leaf_part_rri->ri_IndexRelationInfo[arbiter_i];
/*
* If the non-ancestor index is compatible with the
* arbiter, use the non-ancestor as arbiter too.
*/
if (IsIndexCompatibleAsArbiter(arbiter_rel,
- arbiter_ii,
- unparented_rel,
- unparented_ii))
+ unparented_rel))
{
arbiterIndexes = lappend_oid(arbiterIndexes,
unparented_rel->rd_index->indexrelid);
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 4c9c5e9fc33..5231d0a26a3 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -27,6 +27,7 @@
#include "access/xlog.h"
#include "catalog/catalog.h"
#include "catalog/heap.h"
+#include "catalog/index.h"
#include "catalog/pg_am.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_statistic_ext.h"
@@ -795,6 +796,13 @@ find_relation_notnullatts(PlannerInfo *root, Oid relid)
* the purposes of inference. If no opclass (or collation) is specified, then
* all matching indexes (that may or may not match the default in terms of
* each attribute opclass/collation) are used for inference.
+ *
+ * If a named constraint was specified, none of that matching happens: the
+ * constraint's index is used, along with any index that is an exact
+ * structural equivalent of it. Such equivalents exist transiently while
+ * REINDEX CONCURRENTLY processes the constraint's index, and they must all
+ * arbitrate together so that every concurrent session resolves conflicts
+ * against the same set of indexes.
*/
List *
infer_arbiter_indexes(PlannerInfo *root)
@@ -804,18 +812,13 @@ infer_arbiter_indexes(PlannerInfo *root)
/* Iteration state */
Index varno;
RangeTblEntry *rte;
- Relation relation;
+ Relation relation,
+ indexRelFromConstraint = NULL;
Oid indexOidFromConstraint = InvalidOid;
List *indexList;
List *indexRelList = NIL;
- /*
- * Required attributes and expressions used to match indexes to the clause
- * given by the user. In the ON CONFLICT ON CONSTRAINT case, we compute
- * these from that constraint's index to match all other indexes, to
- * account for the case where that index is being concurrently reindexed.
- */
- List *inferIndexExprs = (List *) onconflict->arbiterWhere;
+ /* Normalized inference attributes and inference expressions: */
Bitmapset *inferAttrs = NULL;
List *inferElems = NIL;
@@ -911,39 +914,22 @@ infer_arbiter_indexes(PlannerInfo *root)
errmsg("constraint in ON CONFLICT clause has no associated index")));
/*
- * Find the named constraint index to extract its attributes and
- * predicates.
+ * Find that index in the list, so that candidate indexes can be
+ * compared against it below. The constraint belongs to the target
+ * relation, so its index must be here.
*/
foreach_ptr(RelationData, idxRel, indexRelList)
{
- Form_pg_index idxForm = idxRel->rd_index;
-
- if (indexOidFromConstraint == idxForm->indexrelid)
+ if (indexOidFromConstraint == RelationGetRelid(idxRel))
{
- /* Found it. */
- Assert(idxForm->indisready);
-
- /*
- * Set up inferElems and inferIndexExprs to match the
- * constraint index, so that we can match them in the loop
- * below.
- */
- for (int natt = 0; natt < idxForm->indnkeyatts; natt++)
- {
- int attno;
-
- attno = idxRel->rd_index->indkey.values[natt];
- if (attno != InvalidAttrNumber)
- inferAttrs =
- bms_add_member(inferAttrs,
- attno - FirstLowInvalidHeapAttributeNumber);
- }
-
- inferElems = RelationGetIndexExpressions(idxRel);
- inferIndexExprs = RelationGetIndexPredicate(idxRel);
+ Assert(idxRel->rd_index->indisready);
+ indexRelFromConstraint = idxRel;
break;
}
}
+ if (indexRelFromConstraint == NULL)
+ elog(ERROR, "could not find index %u of ON CONFLICT constraint",
+ indexOidFromConstraint);
}
/*
@@ -1028,13 +1014,16 @@ infer_arbiter_indexes(PlannerInfo *root)
else if (indexOidFromConstraint != InvalidOid)
{
/*
- * In the case of "ON constraint_name DO SELECT/UPDATE" we need to
- * skip non-unique candidates.
+ * When a constraint is named, the only other indexes that may
+ * arbitrate are exact structural equivalents of its index, which
+ * exist while REINDEX CONCURRENTLY is processing it.
*/
- if (!idxForm->indisunique &&
- (onconflict->action == ONCONFLICT_UPDATE ||
- onconflict->action == ONCONFLICT_SELECT))
- continue;
+ if (IsIndexCompatibleAsArbiter(indexRelFromConstraint, idxRel))
+ {
+ results = lappend_oid(results, idxForm->indexrelid);
+ foundValid |= idxForm->indisvalid;
+ }
+ continue;
}
else
{
@@ -1079,10 +1068,7 @@ infer_arbiter_indexes(PlannerInfo *root)
idxExprs = (List *) eval_const_expressions(root, (Node *) idxExprs);
}
- /*
- * If arbiterElems are present, check them. (Note that if a
- * constraint name was given in the command line, this list is NIL.)
- */
+ /* Check the arbiterElems against this index. */
match = true;
foreach_ptr(InferenceElem, elem, onconflict->arbiterElems)
{
@@ -1125,14 +1111,11 @@ infer_arbiter_indexes(PlannerInfo *root)
continue;
/*
- * In case of inference from an attribute list, ensure that the
+ * Now that all inference elements were matched, ensure that the
* expression elements from inference clause are not missing any
* cataloged expressions. This does the right thing when unique
* indexes redundantly repeat the same attribute, or if attributes
* redundantly appear multiple times within an inference clause.
- *
- * In case a constraint was named, ensure the candidate has an equal
- * set of expressions as the named constraint's index.
*/
if (list_difference(idxExprs, inferElems) != NIL)
continue;
@@ -1150,21 +1133,12 @@ infer_arbiter_indexes(PlannerInfo *root)
}
/*
- * Partial indexes affect each form of ON CONFLICT differently: if a
- * constraint was named, then the predicates must be identical. In
- * conventional inference, the index's predicate must be implied by
- * the WHERE clause.
+ * If it's a partial index, its predicate must be implied by the ON
+ * CONFLICT's WHERE clause.
*/
- if (OidIsValid(indexOidFromConstraint))
- {
- if (list_difference(predExprs, inferIndexExprs) != NIL)
- continue;
- }
- else
- {
- if (!predicate_implied_by(predExprs, inferIndexExprs, false))
- continue;
- }
+ if (!predicate_implied_by(predExprs,
+ (List *) onconflict->arbiterWhere, false))
+ continue;
/* All good -- consider this index a match */
results = lappend_oid(results, idxForm->indexrelid);
@@ -1202,7 +1176,9 @@ infer_arbiter_indexes(PlannerInfo *root)
*
* At least historically, Postgres has not offered collations or opclasses
* with alternative-to-default notions of equality, so these additional
- * criteria should only be required infrequently.
+ * criteria should only be required infrequently. XXX That is no longer
+ * true: nondeterministic collations, supported since PostgreSQL 12, do
+ * equate values that the default notion of equality keeps distinct.
*
* Don't give up immediately when an inference element matches some attribute
* cataloged as indexed but not matching additional opclass/collation
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index b952ad071d3..fcc1b3b770c 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -133,6 +133,8 @@ extern IndexInfo *BuildIndexInfo(Relation index);
extern IndexInfo *BuildDummyIndexInfo(Relation index);
+extern bool IsIndexCompatibleAsArbiter(Relation indexRel1,
+ Relation indexRel2);
extern bool CompareIndexInfo(const IndexInfo *info1, const IndexInfo *info2,
const Oid *collations1, const Oid *collations2,
const Oid *opfamilies1, const Oid *opfamilies2,
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index fcfcc658bea..25cae40f22f 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2102,6 +2102,34 @@ DETAIL: Key (x)=(ABC) already exists.
CREATE UNIQUE INDEX ON test3ci (x); -- error
ERROR: could not create unique index "test3ci_x_idx"
DETAIL: Key (x)=(abc) is duplicated.
+-- ON CONFLICT ON CONSTRAINT must not use an index that differs from the
+-- named constraint's index in collation
+CREATE TABLE test_arbiter_ci (x text, y text);
+ALTER TABLE test_arbiter_ci ADD CONSTRAINT test_arbiter_ci_x_key UNIQUE (x);
+CREATE UNIQUE INDEX test_arbiter_ci_x_ci
+ ON test_arbiter_ci (x COLLATE case_insensitive);
+INSERT INTO test_arbiter_ci VALUES ('abc', 'first');
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'second')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key
+ DO UPDATE SET y = excluded.y; -- error
+ERROR: duplicate key value violates unique constraint "test_arbiter_ci_x_ci"
+DETAIL: Key (x)=(ABC) already exists.
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'third')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key DO NOTHING; -- error
+ERROR: duplicate key value violates unique constraint "test_arbiter_ci_x_ci"
+DETAIL: Key (x)=(ABC) already exists.
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'fourth')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key
+ DO SELECT RETURNING *; -- error
+ERROR: duplicate key value violates unique constraint "test_arbiter_ci_x_ci"
+DETAIL: Key (x)=(ABC) already exists.
+SELECT x, y FROM test_arbiter_ci;
+ x | y
+-----+-------
+ abc | first
+(1 row)
+
+DROP TABLE test_arbiter_ci;
SELECT string_to_array('ABC,DEF,GHI' COLLATE case_insensitive, ',', 'abc');
string_to_array
-----------------
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 34e2e7ee355..b4c9bdb0e55 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -1064,6 +1064,102 @@ insert into parted_conflict_1 values (40, 'cuarenta')
on conflict (a) do update set b = excluded.b;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
drop table parted_conflict;
+-- a partition-local deferrable unique constraint on the arbiter columns
+-- must not be picked up as an additional arbiter for routed inserts
+create table parted_conflict (a int, b text, primary key (a)) partition by range (a);
+create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000);
+alter table parted_conflict_1 add constraint parted_conflict_1_a_def unique (a) deferrable;
+insert into parted_conflict values (40, 'forty');
+insert into parted_conflict values (40, 'cuarenta')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (41, 'forty-one')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (42, 'forty-two') on conflict (a) do nothing;
+select * from parted_conflict order by a;
+ a | b
+----+-----------
+ 40 | cuarenta
+ 41 | forty-one
+ 42 | forty-two
+(3 rows)
+
+drop table parted_conflict;
+-- ON CONFLICT ON CONSTRAINT has the same hazard: an index that merely covers
+-- the same columns as the named constraint's index, but is deferrable or
+-- disagrees with it about duplicates, must not join the arbiter set
+create table namedconstraint (a int, b text, c text);
+alter table namedconstraint add constraint namedconstraint_a_key unique (a);
+alter table namedconstraint add constraint namedconstraint_a_def unique (a) deferrable;
+create unique index namedconstraint_b_nnd on namedconstraint (b) nulls not distinct;
+alter table namedconstraint add constraint namedconstraint_b_key unique (b);
+insert into namedconstraint values (1, 'x', 'first');
+insert into namedconstraint values (2, null, 'first');
+insert into namedconstraint values (1, 'y', 'second')
+ on conflict on constraint namedconstraint_a_key do update set c = excluded.c;
+insert into namedconstraint values (3, null, 'second')
+ on conflict on constraint namedconstraint_b_key do update set c = excluded.c; -- error
+ERROR: duplicate key value violates unique constraint "namedconstraint_b_nnd"
+DETAIL: Key (b)=(null) already exists.
+insert into namedconstraint values (4, 'z', 'third')
+ on conflict on constraint namedconstraint_a_key do nothing;
+insert into namedconstraint values (5, null, 'third')
+ on conflict on constraint namedconstraint_b_key do nothing; -- error
+ERROR: duplicate key value violates unique constraint "namedconstraint_b_nnd"
+DETAIL: Key (b)=(null) already exists.
+select * from namedconstraint order by a;
+ a | b | c
+---+---+--------
+ 1 | x | second
+ 2 | | first
+ 4 | z | third
+(3 rows)
+
+drop table namedconstraint;
+-- same, with the indexes created in the opposite order
+create table namedconstraint2 (a int, b text, c text);
+alter table namedconstraint2 add constraint namedconstraint2_a_def unique (a) deferrable;
+alter table namedconstraint2 add constraint namedconstraint2_a_key unique (a);
+alter table namedconstraint2 add constraint namedconstraint2_b_key unique (b);
+create unique index namedconstraint2_b_nnd on namedconstraint2 (b) nulls not distinct;
+insert into namedconstraint2 values (1, 'x', 'first');
+insert into namedconstraint2 values (2, null, 'first');
+insert into namedconstraint2 values (1, 'y', 'second')
+ on conflict on constraint namedconstraint2_a_key do update set c = excluded.c;
+insert into namedconstraint2 values (3, 'z', 'second')
+ on conflict on constraint namedconstraint2_a_key do nothing;
+insert into namedconstraint2 values (4, null, 'second')
+ on conflict on constraint namedconstraint2_b_key do nothing; -- error
+ERROR: duplicate key value violates unique constraint "namedconstraint2_b_nnd"
+DETAIL: Key (b)=(null) already exists.
+-- ON CONFLICT DO SELECT reaches the same code
+insert into namedconstraint2 values (1, 'w', 'third')
+ on conflict on constraint namedconstraint2_a_key do select returning *;
+ a | b | c
+---+---+--------
+ 1 | x | second
+(1 row)
+
+insert into namedconstraint2 values (5, 'v', 'third')
+ on conflict on constraint namedconstraint2_a_key do select returning *;
+ a | b | c
+---+---+-------
+ 5 | v | third
+(1 row)
+
+insert into namedconstraint2 values (6, null, 'fourth')
+ on conflict on constraint namedconstraint2_b_key do select returning *; -- error
+ERROR: duplicate key value violates unique constraint "namedconstraint2_b_nnd"
+DETAIL: Key (b)=(null) already exists.
+select * from namedconstraint2 order by a;
+ a | b | c
+---+---+--------
+ 1 | x | second
+ 2 | | first
+ 3 | z | second
+ 5 | v | third
+(4 rows)
+
+drop table namedconstraint2;
-- test whole-row Vars in ON CONFLICT expressions
create table parted_conflict (a int, b text, c int) partition by range (a);
create table parted_conflict_1 (drp text, c int, a int, b text);
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index ce4e2bb3ffd..ecef8682c66 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -748,6 +748,24 @@ SELECT x, row_number() OVER (ORDER BY x), rank() OVER (ORDER BY x) FROM test3ci
CREATE UNIQUE INDEX ON test1ci (x); -- ok
INSERT INTO test1ci VALUES ('ABC'); -- error
CREATE UNIQUE INDEX ON test3ci (x); -- error
+
+-- ON CONFLICT ON CONSTRAINT must not use an index that differs from the
+-- named constraint's index in collation
+CREATE TABLE test_arbiter_ci (x text, y text);
+ALTER TABLE test_arbiter_ci ADD CONSTRAINT test_arbiter_ci_x_key UNIQUE (x);
+CREATE UNIQUE INDEX test_arbiter_ci_x_ci
+ ON test_arbiter_ci (x COLLATE case_insensitive);
+INSERT INTO test_arbiter_ci VALUES ('abc', 'first');
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'second')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key
+ DO UPDATE SET y = excluded.y; -- error
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'third')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key DO NOTHING; -- error
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'fourth')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key
+ DO SELECT RETURNING *; -- error
+SELECT x, y FROM test_arbiter_ci;
+DROP TABLE test_arbiter_ci;
SELECT string_to_array('ABC,DEF,GHI' COLLATE case_insensitive, ',', 'abc');
SELECT string_to_array('ABCDEFGHI' COLLATE case_insensitive, NULL, 'b');
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index a5a84d1d4b8..d119158549f 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -619,6 +619,65 @@ insert into parted_conflict_1 values (40, 'cuarenta')
on conflict (a) do update set b = excluded.b;
drop table parted_conflict;
+-- a partition-local deferrable unique constraint on the arbiter columns
+-- must not be picked up as an additional arbiter for routed inserts
+create table parted_conflict (a int, b text, primary key (a)) partition by range (a);
+create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000);
+alter table parted_conflict_1 add constraint parted_conflict_1_a_def unique (a) deferrable;
+insert into parted_conflict values (40, 'forty');
+insert into parted_conflict values (40, 'cuarenta')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (41, 'forty-one')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (42, 'forty-two') on conflict (a) do nothing;
+select * from parted_conflict order by a;
+drop table parted_conflict;
+
+-- ON CONFLICT ON CONSTRAINT has the same hazard: an index that merely covers
+-- the same columns as the named constraint's index, but is deferrable or
+-- disagrees with it about duplicates, must not join the arbiter set
+create table namedconstraint (a int, b text, c text);
+alter table namedconstraint add constraint namedconstraint_a_key unique (a);
+alter table namedconstraint add constraint namedconstraint_a_def unique (a) deferrable;
+create unique index namedconstraint_b_nnd on namedconstraint (b) nulls not distinct;
+alter table namedconstraint add constraint namedconstraint_b_key unique (b);
+insert into namedconstraint values (1, 'x', 'first');
+insert into namedconstraint values (2, null, 'first');
+insert into namedconstraint values (1, 'y', 'second')
+ on conflict on constraint namedconstraint_a_key do update set c = excluded.c;
+insert into namedconstraint values (3, null, 'second')
+ on conflict on constraint namedconstraint_b_key do update set c = excluded.c; -- error
+insert into namedconstraint values (4, 'z', 'third')
+ on conflict on constraint namedconstraint_a_key do nothing;
+insert into namedconstraint values (5, null, 'third')
+ on conflict on constraint namedconstraint_b_key do nothing; -- error
+select * from namedconstraint order by a;
+drop table namedconstraint;
+
+-- same, with the indexes created in the opposite order
+create table namedconstraint2 (a int, b text, c text);
+alter table namedconstraint2 add constraint namedconstraint2_a_def unique (a) deferrable;
+alter table namedconstraint2 add constraint namedconstraint2_a_key unique (a);
+alter table namedconstraint2 add constraint namedconstraint2_b_key unique (b);
+create unique index namedconstraint2_b_nnd on namedconstraint2 (b) nulls not distinct;
+insert into namedconstraint2 values (1, 'x', 'first');
+insert into namedconstraint2 values (2, null, 'first');
+insert into namedconstraint2 values (1, 'y', 'second')
+ on conflict on constraint namedconstraint2_a_key do update set c = excluded.c;
+insert into namedconstraint2 values (3, 'z', 'second')
+ on conflict on constraint namedconstraint2_a_key do nothing;
+insert into namedconstraint2 values (4, null, 'second')
+ on conflict on constraint namedconstraint2_b_key do nothing; -- error
+-- ON CONFLICT DO SELECT reaches the same code
+insert into namedconstraint2 values (1, 'w', 'third')
+ on conflict on constraint namedconstraint2_a_key do select returning *;
+insert into namedconstraint2 values (5, 'v', 'third')
+ on conflict on constraint namedconstraint2_a_key do select returning *;
+insert into namedconstraint2 values (6, null, 'fourth')
+ on conflict on constraint namedconstraint2_b_key do select returning *; -- error
+select * from namedconstraint2 order by a;
+drop table namedconstraint2;
+
-- test whole-row Vars in ON CONFLICT expressions
create table parted_conflict (a int, b text, c int) partition by range (a);
create table parted_conflict_1 (drp text, c int, a int, b text);
--
2.43.0
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 23:47 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-01 06:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Michael Paquier <michael@paquier.xyz>
2026-09-01 09:15 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
@ 2026-09-01 22:05 ` Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-16 15:42 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Nathan Bossart <nathandbossart@gmail.com>
2026-09-17 21:08 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Álvaro Herrera <alvherre@kurilemu.de>
1 sibling, 2 replies; 16+ messages in thread
From: Zsolt Parragi @ 2026-09-01 22:05 UTC (permalink / raw)
To: Mihail Nikalayeu <mihailnikalayeu@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org, Michael Paquier <michael@paquier.xyz>
On Tue, 01 Sep 2026, Mihail Nikalayeu <mihailnikalayeu@gmail.com> wrote:
> This is a "grouped" version. Also, it handles possible collation
> issues + provides a set of tests to pin the correct behaviour.
Thanks, this looks better what I had in mind, I would have missed a
few corner cases this patch covers.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 23:47 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-01 06:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Michael Paquier <michael@paquier.xyz>
2026-09-01 09:15 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-09-01 22:05 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
@ 2026-09-16 15:42 ` Nathan Bossart <nathandbossart@gmail.com>
2026-09-16 17:39 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Alvaro Herrera <alvherre@kurilemu.de>
1 sibling, 1 reply; 16+ messages in thread
From: Nathan Bossart @ 2026-09-16 15:42 UTC (permalink / raw)
To: Zsolt Parragi <zsolt.parragi@percona.com>; +Cc: Mihail Nikalayeu <mihailnikalayeu@gmail.com>; pgsql-hackers@lists.postgresql.org, Michael Paquier <michael@paquier.xyz>; alvherre@kurilemu.de
[RMT hat]
This one is listed as an open item, but there haven't been any updates to
the thread in a couple of weeks. What is the current status, and can it be
fixed before code freeze for 19beta4 begins on Saturday at noon UTC?
--
nathan
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 23:47 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-01 06:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Michael Paquier <michael@paquier.xyz>
2026-09-01 09:15 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-09-01 22:05 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-16 15:42 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Nathan Bossart <nathandbossart@gmail.com>
@ 2026-09-16 17:39 ` Alvaro Herrera <alvherre@kurilemu.de>
0 siblings, 0 replies; 16+ messages in thread
From: Alvaro Herrera @ 2026-09-16 17:39 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Zsolt Parragi <zsolt.parragi@percona.com>; Mihail Nikalayeu <mihailnikalayeu@gmail.com>; pgsql-hackers@lists.postgresql.org, Michael Paquier <michael@paquier.xyz>
On 2026-Sep-16, Nathan Bossart wrote:
> This one is listed as an open item, but there haven't been any updates to
> the thread in a couple of weeks. What is the current status, and can it be
> fixed before code freeze for 19beta4 begins on Saturday at noon UTC?
I haven't reviewed the proposed fix yet, but I'll strive to get the fix
in before that deadline.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 23:47 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-01 06:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Michael Paquier <michael@paquier.xyz>
2026-09-01 09:15 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-09-01 22:05 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
@ 2026-09-17 21:08 ` Álvaro Herrera <alvherre@kurilemu.de>
2026-09-18 11:42 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Álvaro Herrera <alvherre@kurilemu.de>
1 sibling, 1 reply; 16+ messages in thread
From: Álvaro Herrera @ 2026-09-17 21:08 UTC (permalink / raw)
To: Zsolt Parragi <zsolt.parragi@percona.com>; +Cc: Mihail Nikalayeu <mihailnikalayeu@gmail.com>; pgsql-hackers@lists.postgresql.org, Michael Paquier <michael@paquier.xyz>
On 2026-Sep-01, Zsolt Parragi wrote:
> On Tue, 01 Sep 2026, Mihail Nikalayeu <mihailnikalayeu@gmail.com> wrote:
> > This is a "grouped" version. Also, it handles possible collation
> > issues + provides a set of tests to pin the correct behaviour.
>
> Thanks, this looks better what I had in mind, I would have missed a
> few corner cases this patch covers.
I spent some time with this and ended up with the attached. I don't I
found anything to change, apart from minor edits to the commit message.
I'll probably edit it some more before push, to mention the change of
list_difference() to equal().
The non-deterministic collation aspect mentioned in an XXX comment added
by the patch was a bug in 18 and back, and continues to be a bug after
this patch. That's shown with the following test case:
CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
-- First part of test case: ON CONFLICT listing a column works fine.
CREATE TABLE t (x text, y text);
ALTER TABLE t ADD CONSTRAINT t_x_key UNIQUE (x);
CREATE UNIQUE INDEX t_x_ci ON t (x COLLATE ci);
INSERT INTO t VALUES ('a', 'first');
INSERT INTO t VALUES ('A', 'second') ON CONFLICT (x) DO UPDATE SET y = excluded.y;
-- the end result here is ('a', 'second'), showing that ON CONFLICT worked.
SELECT x, y FROM t;
-- repeat, but use ON CONFLICT ON CONSTRAINT. Throws error but shouldn't.
INSERT INTO t VALUES ('A', 'third') ON CONFLICT ON CONSTRAINT t_x_key DO UPDATE SET y = excluded.y;
It's not on this patch to solve this problem, as it's not a new problem.
But we should consider a backpatchable fix at some point.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"I can't go to a restaurant and order food because I keep looking at the
fonts on the menu. Five minutes later I realize that it's also talking
about food" (Donald Knuth)
Attachments:
[text/x-diff] 0001-Tighten-definition-of-ON-CONFLICT-arbiter-index-equi.patch (28.6K, ../../aqwnMDPBLBQ64Eus@alvherre.pgsql/2-0001-Tighten-definition-of-ON-CONFLICT-arbiter-index-equi.patch)
download | inline diff:
From 8c01c41b126002fac2cf22b38ca25501e02e730c Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvherre@kurilemu.de>
Date: Thu, 17 Sep 2026 19:32:20 +0200
Subject: [PATCH] Tighten definition of ON CONFLICT arbiter index equivalence
Commits 2bc7e886fc1b and 90eae926abbb taught ON CONFLICT to include
indexes matching an already selected arbiter, so that an index left
behind by REINDEX CONCURRENTLY continues to arbitrate together with its
replacement. Both checks were too permissive:
a) infer_arbiter_indexes() compared a candidate with a named
constraint's index using only attributes, expressions and predicate, but
ignored collation, NULLS NOT DISTINCT setting or deferrability. As a
result, an index with a difference in these settings could be accepted
even though it did not identify the same conflicts. Also, a deferrable
index that otherwise matches an arbiter index would also cause ON
CONFLICT to fail with "ON CONFLICT does not support deferrable unique
constraints/exclusion constraints as arbiters".
b) ExecInitPartitionInfo() also failed due to the failure to compare
deferrability, so partition-local deferrable indexes would be considered
and break inserts routed to that partition with the error mentioned
above.
Fix by making IsIndexCompatibleAsArbiter() compare those properties, and
changing infer_arbiter_indexes() to use that routine instead of open
coding equivalent logic. Also handle the named-constraint case in
infer_arbiter_indexes() separately instead of passing the constraint
index through the regular clause-inference matching. That function is
not static anymore, so move it to index.c, and also reimplement it to
use the Relation from the indexes only, no longer receiving the
IndexInfo (which wasn't really necessary.)
Add tests for ON CONFLICT ON CONSTRAINT with deferrable, NULLS NOT
DISTINCT, and different-collation sibling indexes, preserving the
behavior of released pre-19 versions. Also test routed inserts with a
partition-local deferrable unique constraint.
Author: Zsolt Parragi <zsolt.parragi@percona.com>
Author: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Reported-by: Zsolt Parragi <zsolt.parragi@percona.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Backpatch-through: 19
Discussion: https://postgr.es/m/CAN4CZFPEYXeYFTxHpoPujfVFb+1Tx1jnXVboDMBg-ZhpgpQ-_g@mail.gmail.com
---
src/backend/catalog/index.c | 74 +++++++++++++
src/backend/executor/execPartition.c | 65 +----------
src/backend/optimizer/util/plancat.c | 102 +++++++-----------
src/include/catalog/index.h | 3 +
.../regress/expected/collate.icu.utf8.out | 28 +++++
src/test/regress/expected/insert_conflict.out | 96 +++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 18 ++++
src/test/regress/sql/insert_conflict.sql | 59 ++++++++++
8 files changed, 318 insertions(+), 127 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ec21b83b6b8..2a46cc4de19 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2752,6 +2752,80 @@ BuildSpeculativeIndexInfo(Relation index, IndexInfo *ii)
}
}
+/* ----------------
+ * IsIndexCompatibleAsArbiter
+ * Return true if two indexes of the same table are interchangeable as
+ * speculative insertion arbiters for INSERT ON CONFLICT.
+ *
+ * To be interchangeable, the two indexes must agree on which tuples conflict,
+ * so every property bearing on that must be identical. Indexes that merely
+ * index the same columns can each have their own notion of what a duplicate
+ * is, and treating one as arbiter in place of the other would resolve
+ * conflicts the other does not have.
+ *
+ * This is built for REINDEX CONCURRENTLY: while it processes an arbiter
+ * index, an exact copy of it built by index_create_copy() exists alongside,
+ * and both copies must arbitrate together for all concurrent sessions to
+ * agree on the set of arbiters.
+ *
+ * Properties that do not affect which tuples conflict, such as validity,
+ * are deliberately not examined here; callers must check them as needed.
+ * ----------------
+ */
+bool
+IsIndexCompatibleAsArbiter(Relation indexRel1, Relation indexRel2)
+{
+ Form_pg_index indexForm1 = indexRel1->rd_index;
+ Form_pg_index indexForm2 = indexRel2->rd_index;
+
+ /* Only indexes of the same relation can be compared. */
+ Assert(indexForm1->indrelid == indexForm2->indrelid);
+
+ /* must match whether they're unique */
+ if (indexForm1->indisunique != indexForm2->indisunique)
+ return false;
+
+ /* No support currently for comparing exclusion indexes. */
+ if (indexForm1->indisexclusion || indexForm2->indisexclusion)
+ return false;
+
+ /* a deferrable index detects conflicts at a different time */
+ if (indexForm1->indimmediate != indexForm2->indimmediate)
+ return false;
+
+ /* the "nulls not distinct" criterion must match */
+ if (indexForm1->indnullsnotdistinct != indexForm2->indnullsnotdistinct)
+ return false;
+
+ /* number of key attributes must match */
+ if (indexForm1->indnkeyatts != indexForm2->indnkeyatts)
+ return false;
+
+ /* key columns, and their collations and opfamilies, must match */
+ for (int i = 0; i < indexForm1->indnkeyatts; i++)
+ {
+ if (indexForm1->indkey.values[i] != indexForm2->indkey.values[i])
+ return false;
+
+ if (indexRel1->rd_indcollation[i] != indexRel2->rd_indcollation[i])
+ return false;
+
+ if (indexRel1->rd_opfamily[i] != indexRel2->rd_opfamily[i])
+ return false;
+ }
+
+ /* index expressions and predicate must match */
+ if (!equal(RelationGetIndexExpressions(indexRel1),
+ RelationGetIndexExpressions(indexRel2)))
+ return false;
+
+ if (!equal(RelationGetIndexPredicate(indexRel1),
+ RelationGetIndexPredicate(indexRel2)))
+ return false;
+
+ return true;
+}
+
/* ----------------
* FormIndexDatum
* Construct values[] and isnull[] arrays for a new index tuple.
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
index 2b5f2e3cead..86fa0f0deaa 100644
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -493,65 +493,6 @@ ExecFindPartition(ModifyTableState *mtstate,
return rri;
}
-/*
- * IsIndexCompatibleAsArbiter
- * Return true if two indexes are identical for INSERT ON CONFLICT
- * purposes.
- *
- * Only indexes of the same relation are supported.
- */
-static bool
-IsIndexCompatibleAsArbiter(Relation arbiterIndexRelation,
- IndexInfo *arbiterIndexInfo,
- Relation indexRelation,
- IndexInfo *indexInfo)
-{
- Assert(arbiterIndexRelation->rd_index->indrelid == indexRelation->rd_index->indrelid);
-
- /* must match whether they're unique */
- if (arbiterIndexInfo->ii_Unique != indexInfo->ii_Unique)
- return false;
-
- /* No support currently for comparing exclusion indexes. */
- if (arbiterIndexInfo->ii_ExclusionOps != NULL ||
- indexInfo->ii_ExclusionOps != NULL)
- return false;
-
- /* the "nulls not distinct" criterion must match */
- if (arbiterIndexInfo->ii_NullsNotDistinct !=
- indexInfo->ii_NullsNotDistinct)
- return false;
-
- /* number of key attributes must match */
- if (arbiterIndexInfo->ii_NumIndexKeyAttrs !=
- indexInfo->ii_NumIndexKeyAttrs)
- return false;
-
- for (int i = 0; i < arbiterIndexInfo->ii_NumIndexKeyAttrs; i++)
- {
- if (arbiterIndexRelation->rd_indcollation[i] !=
- indexRelation->rd_indcollation[i])
- return false;
-
- if (arbiterIndexRelation->rd_opfamily[i] !=
- indexRelation->rd_opfamily[i])
- return false;
-
- if (arbiterIndexRelation->rd_index->indkey.values[i] !=
- indexRelation->rd_index->indkey.values[i])
- return false;
- }
-
- if (list_difference(RelationGetIndexExpressions(arbiterIndexRelation),
- RelationGetIndexExpressions(indexRelation)) != NIL)
- return false;
-
- if (list_difference(RelationGetIndexPredicate(arbiterIndexRelation),
- RelationGetIndexPredicate(indexRelation)) != NIL)
- return false;
- return true;
-}
-
/*
* ExecInitPartitionInfo
* Lock the partition and initialize ResultRelInfo. Also setup other
@@ -846,19 +787,15 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
foreach_int(arbiter_i, arbiters_listidxs)
{
Relation arbiter_rel;
- IndexInfo *arbiter_ii;
arbiter_rel = leaf_part_rri->ri_IndexRelationDescs[arbiter_i];
- arbiter_ii = leaf_part_rri->ri_IndexRelationInfo[arbiter_i];
/*
* If the non-ancestor index is compatible with the
* arbiter, use the non-ancestor as arbiter too.
*/
if (IsIndexCompatibleAsArbiter(arbiter_rel,
- arbiter_ii,
- unparented_rel,
- unparented_ii))
+ unparented_rel))
{
arbiterIndexes = lappend_oid(arbiterIndexes,
unparented_rel->rd_index->indexrelid);
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 4c9c5e9fc33..5231d0a26a3 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -27,6 +27,7 @@
#include "access/xlog.h"
#include "catalog/catalog.h"
#include "catalog/heap.h"
+#include "catalog/index.h"
#include "catalog/pg_am.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_statistic_ext.h"
@@ -795,6 +796,13 @@ find_relation_notnullatts(PlannerInfo *root, Oid relid)
* the purposes of inference. If no opclass (or collation) is specified, then
* all matching indexes (that may or may not match the default in terms of
* each attribute opclass/collation) are used for inference.
+ *
+ * If a named constraint was specified, none of that matching happens: the
+ * constraint's index is used, along with any index that is an exact
+ * structural equivalent of it. Such equivalents exist transiently while
+ * REINDEX CONCURRENTLY processes the constraint's index, and they must all
+ * arbitrate together so that every concurrent session resolves conflicts
+ * against the same set of indexes.
*/
List *
infer_arbiter_indexes(PlannerInfo *root)
@@ -804,18 +812,13 @@ infer_arbiter_indexes(PlannerInfo *root)
/* Iteration state */
Index varno;
RangeTblEntry *rte;
- Relation relation;
+ Relation relation,
+ indexRelFromConstraint = NULL;
Oid indexOidFromConstraint = InvalidOid;
List *indexList;
List *indexRelList = NIL;
- /*
- * Required attributes and expressions used to match indexes to the clause
- * given by the user. In the ON CONFLICT ON CONSTRAINT case, we compute
- * these from that constraint's index to match all other indexes, to
- * account for the case where that index is being concurrently reindexed.
- */
- List *inferIndexExprs = (List *) onconflict->arbiterWhere;
+ /* Normalized inference attributes and inference expressions: */
Bitmapset *inferAttrs = NULL;
List *inferElems = NIL;
@@ -911,39 +914,22 @@ infer_arbiter_indexes(PlannerInfo *root)
errmsg("constraint in ON CONFLICT clause has no associated index")));
/*
- * Find the named constraint index to extract its attributes and
- * predicates.
+ * Find that index in the list, so that candidate indexes can be
+ * compared against it below. The constraint belongs to the target
+ * relation, so its index must be here.
*/
foreach_ptr(RelationData, idxRel, indexRelList)
{
- Form_pg_index idxForm = idxRel->rd_index;
-
- if (indexOidFromConstraint == idxForm->indexrelid)
+ if (indexOidFromConstraint == RelationGetRelid(idxRel))
{
- /* Found it. */
- Assert(idxForm->indisready);
-
- /*
- * Set up inferElems and inferIndexExprs to match the
- * constraint index, so that we can match them in the loop
- * below.
- */
- for (int natt = 0; natt < idxForm->indnkeyatts; natt++)
- {
- int attno;
-
- attno = idxRel->rd_index->indkey.values[natt];
- if (attno != InvalidAttrNumber)
- inferAttrs =
- bms_add_member(inferAttrs,
- attno - FirstLowInvalidHeapAttributeNumber);
- }
-
- inferElems = RelationGetIndexExpressions(idxRel);
- inferIndexExprs = RelationGetIndexPredicate(idxRel);
+ Assert(idxRel->rd_index->indisready);
+ indexRelFromConstraint = idxRel;
break;
}
}
+ if (indexRelFromConstraint == NULL)
+ elog(ERROR, "could not find index %u of ON CONFLICT constraint",
+ indexOidFromConstraint);
}
/*
@@ -1028,13 +1014,16 @@ infer_arbiter_indexes(PlannerInfo *root)
else if (indexOidFromConstraint != InvalidOid)
{
/*
- * In the case of "ON constraint_name DO SELECT/UPDATE" we need to
- * skip non-unique candidates.
+ * When a constraint is named, the only other indexes that may
+ * arbitrate are exact structural equivalents of its index, which
+ * exist while REINDEX CONCURRENTLY is processing it.
*/
- if (!idxForm->indisunique &&
- (onconflict->action == ONCONFLICT_UPDATE ||
- onconflict->action == ONCONFLICT_SELECT))
- continue;
+ if (IsIndexCompatibleAsArbiter(indexRelFromConstraint, idxRel))
+ {
+ results = lappend_oid(results, idxForm->indexrelid);
+ foundValid |= idxForm->indisvalid;
+ }
+ continue;
}
else
{
@@ -1079,10 +1068,7 @@ infer_arbiter_indexes(PlannerInfo *root)
idxExprs = (List *) eval_const_expressions(root, (Node *) idxExprs);
}
- /*
- * If arbiterElems are present, check them. (Note that if a
- * constraint name was given in the command line, this list is NIL.)
- */
+ /* Check the arbiterElems against this index. */
match = true;
foreach_ptr(InferenceElem, elem, onconflict->arbiterElems)
{
@@ -1125,14 +1111,11 @@ infer_arbiter_indexes(PlannerInfo *root)
continue;
/*
- * In case of inference from an attribute list, ensure that the
+ * Now that all inference elements were matched, ensure that the
* expression elements from inference clause are not missing any
* cataloged expressions. This does the right thing when unique
* indexes redundantly repeat the same attribute, or if attributes
* redundantly appear multiple times within an inference clause.
- *
- * In case a constraint was named, ensure the candidate has an equal
- * set of expressions as the named constraint's index.
*/
if (list_difference(idxExprs, inferElems) != NIL)
continue;
@@ -1150,21 +1133,12 @@ infer_arbiter_indexes(PlannerInfo *root)
}
/*
- * Partial indexes affect each form of ON CONFLICT differently: if a
- * constraint was named, then the predicates must be identical. In
- * conventional inference, the index's predicate must be implied by
- * the WHERE clause.
+ * If it's a partial index, its predicate must be implied by the ON
+ * CONFLICT's WHERE clause.
*/
- if (OidIsValid(indexOidFromConstraint))
- {
- if (list_difference(predExprs, inferIndexExprs) != NIL)
- continue;
- }
- else
- {
- if (!predicate_implied_by(predExprs, inferIndexExprs, false))
- continue;
- }
+ if (!predicate_implied_by(predExprs,
+ (List *) onconflict->arbiterWhere, false))
+ continue;
/* All good -- consider this index a match */
results = lappend_oid(results, idxForm->indexrelid);
@@ -1202,7 +1176,9 @@ infer_arbiter_indexes(PlannerInfo *root)
*
* At least historically, Postgres has not offered collations or opclasses
* with alternative-to-default notions of equality, so these additional
- * criteria should only be required infrequently.
+ * criteria should only be required infrequently. XXX That is no longer
+ * true: nondeterministic collations, supported since PostgreSQL 12, do
+ * equate values that the default notion of equality keeps distinct.
*
* Don't give up immediately when an inference element matches some attribute
* cataloged as indexed but not matching additional opclass/collation
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index b952ad071d3..5add66817cf 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -140,6 +140,9 @@ extern bool CompareIndexInfo(const IndexInfo *info1, const IndexInfo *info2,
extern void BuildSpeculativeIndexInfo(Relation index, IndexInfo *ii);
+extern bool IsIndexCompatibleAsArbiter(Relation indexRel1,
+ Relation indexRel2);
+
extern void FormIndexDatum(IndexInfo *indexInfo,
TupleTableSlot *slot,
EState *estate,
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index a32e8965725..5ec848b82e4 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2102,6 +2102,34 @@ DETAIL: Key (x)=(ABC) already exists.
CREATE UNIQUE INDEX ON test3ci (x); -- error
ERROR: could not create unique index "test3ci_x_idx"
DETAIL: Key (x)=(abc) is duplicated.
+-- ON CONFLICT ON CONSTRAINT must not use an index that differs from the
+-- named constraint's index in collation
+CREATE TABLE test_arbiter_ci (x text, y text);
+ALTER TABLE test_arbiter_ci ADD CONSTRAINT test_arbiter_ci_x_key UNIQUE (x);
+CREATE UNIQUE INDEX test_arbiter_ci_x_ci
+ ON test_arbiter_ci (x COLLATE case_insensitive);
+INSERT INTO test_arbiter_ci VALUES ('abc', 'first');
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'second')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key
+ DO UPDATE SET y = excluded.y; -- error
+ERROR: duplicate key value violates unique constraint "test_arbiter_ci_x_ci"
+DETAIL: Key (x)=(ABC) already exists.
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'third')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key DO NOTHING; -- error
+ERROR: duplicate key value violates unique constraint "test_arbiter_ci_x_ci"
+DETAIL: Key (x)=(ABC) already exists.
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'fourth')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key
+ DO SELECT RETURNING *; -- error
+ERROR: duplicate key value violates unique constraint "test_arbiter_ci_x_ci"
+DETAIL: Key (x)=(ABC) already exists.
+SELECT x, y FROM test_arbiter_ci;
+ x | y
+-----+-------
+ abc | first
+(1 row)
+
+DROP TABLE test_arbiter_ci;
SELECT string_to_array('ABC,DEF,GHI' COLLATE case_insensitive, ',', 'abc');
string_to_array
-----------------
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 34e2e7ee355..b4c9bdb0e55 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -1064,6 +1064,102 @@ insert into parted_conflict_1 values (40, 'cuarenta')
on conflict (a) do update set b = excluded.b;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
drop table parted_conflict;
+-- a partition-local deferrable unique constraint on the arbiter columns
+-- must not be picked up as an additional arbiter for routed inserts
+create table parted_conflict (a int, b text, primary key (a)) partition by range (a);
+create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000);
+alter table parted_conflict_1 add constraint parted_conflict_1_a_def unique (a) deferrable;
+insert into parted_conflict values (40, 'forty');
+insert into parted_conflict values (40, 'cuarenta')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (41, 'forty-one')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (42, 'forty-two') on conflict (a) do nothing;
+select * from parted_conflict order by a;
+ a | b
+----+-----------
+ 40 | cuarenta
+ 41 | forty-one
+ 42 | forty-two
+(3 rows)
+
+drop table parted_conflict;
+-- ON CONFLICT ON CONSTRAINT has the same hazard: an index that merely covers
+-- the same columns as the named constraint's index, but is deferrable or
+-- disagrees with it about duplicates, must not join the arbiter set
+create table namedconstraint (a int, b text, c text);
+alter table namedconstraint add constraint namedconstraint_a_key unique (a);
+alter table namedconstraint add constraint namedconstraint_a_def unique (a) deferrable;
+create unique index namedconstraint_b_nnd on namedconstraint (b) nulls not distinct;
+alter table namedconstraint add constraint namedconstraint_b_key unique (b);
+insert into namedconstraint values (1, 'x', 'first');
+insert into namedconstraint values (2, null, 'first');
+insert into namedconstraint values (1, 'y', 'second')
+ on conflict on constraint namedconstraint_a_key do update set c = excluded.c;
+insert into namedconstraint values (3, null, 'second')
+ on conflict on constraint namedconstraint_b_key do update set c = excluded.c; -- error
+ERROR: duplicate key value violates unique constraint "namedconstraint_b_nnd"
+DETAIL: Key (b)=(null) already exists.
+insert into namedconstraint values (4, 'z', 'third')
+ on conflict on constraint namedconstraint_a_key do nothing;
+insert into namedconstraint values (5, null, 'third')
+ on conflict on constraint namedconstraint_b_key do nothing; -- error
+ERROR: duplicate key value violates unique constraint "namedconstraint_b_nnd"
+DETAIL: Key (b)=(null) already exists.
+select * from namedconstraint order by a;
+ a | b | c
+---+---+--------
+ 1 | x | second
+ 2 | | first
+ 4 | z | third
+(3 rows)
+
+drop table namedconstraint;
+-- same, with the indexes created in the opposite order
+create table namedconstraint2 (a int, b text, c text);
+alter table namedconstraint2 add constraint namedconstraint2_a_def unique (a) deferrable;
+alter table namedconstraint2 add constraint namedconstraint2_a_key unique (a);
+alter table namedconstraint2 add constraint namedconstraint2_b_key unique (b);
+create unique index namedconstraint2_b_nnd on namedconstraint2 (b) nulls not distinct;
+insert into namedconstraint2 values (1, 'x', 'first');
+insert into namedconstraint2 values (2, null, 'first');
+insert into namedconstraint2 values (1, 'y', 'second')
+ on conflict on constraint namedconstraint2_a_key do update set c = excluded.c;
+insert into namedconstraint2 values (3, 'z', 'second')
+ on conflict on constraint namedconstraint2_a_key do nothing;
+insert into namedconstraint2 values (4, null, 'second')
+ on conflict on constraint namedconstraint2_b_key do nothing; -- error
+ERROR: duplicate key value violates unique constraint "namedconstraint2_b_nnd"
+DETAIL: Key (b)=(null) already exists.
+-- ON CONFLICT DO SELECT reaches the same code
+insert into namedconstraint2 values (1, 'w', 'third')
+ on conflict on constraint namedconstraint2_a_key do select returning *;
+ a | b | c
+---+---+--------
+ 1 | x | second
+(1 row)
+
+insert into namedconstraint2 values (5, 'v', 'third')
+ on conflict on constraint namedconstraint2_a_key do select returning *;
+ a | b | c
+---+---+-------
+ 5 | v | third
+(1 row)
+
+insert into namedconstraint2 values (6, null, 'fourth')
+ on conflict on constraint namedconstraint2_b_key do select returning *; -- error
+ERROR: duplicate key value violates unique constraint "namedconstraint2_b_nnd"
+DETAIL: Key (b)=(null) already exists.
+select * from namedconstraint2 order by a;
+ a | b | c
+---+---+--------
+ 1 | x | second
+ 2 | | first
+ 3 | z | second
+ 5 | v | third
+(4 rows)
+
+drop table namedconstraint2;
-- test whole-row Vars in ON CONFLICT expressions
create table parted_conflict (a int, b text, c int) partition by range (a);
create table parted_conflict_1 (drp text, c int, a int, b text);
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index f0d9ee2c96d..b50cf3f6c7c 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -748,6 +748,24 @@ SELECT x, row_number() OVER (ORDER BY x), rank() OVER (ORDER BY x) FROM test3ci
CREATE UNIQUE INDEX ON test1ci (x); -- ok
INSERT INTO test1ci VALUES ('ABC'); -- error
CREATE UNIQUE INDEX ON test3ci (x); -- error
+
+-- ON CONFLICT ON CONSTRAINT must not use an index that differs from the
+-- named constraint's index in collation
+CREATE TABLE test_arbiter_ci (x text, y text);
+ALTER TABLE test_arbiter_ci ADD CONSTRAINT test_arbiter_ci_x_key UNIQUE (x);
+CREATE UNIQUE INDEX test_arbiter_ci_x_ci
+ ON test_arbiter_ci (x COLLATE case_insensitive);
+INSERT INTO test_arbiter_ci VALUES ('abc', 'first');
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'second')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key
+ DO UPDATE SET y = excluded.y; -- error
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'third')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key DO NOTHING; -- error
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'fourth')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key
+ DO SELECT RETURNING *; -- error
+SELECT x, y FROM test_arbiter_ci;
+DROP TABLE test_arbiter_ci;
SELECT string_to_array('ABC,DEF,GHI' COLLATE case_insensitive, ',', 'abc');
SELECT string_to_array('ABCDEFGHI' COLLATE case_insensitive, NULL, 'b');
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index a5a84d1d4b8..d119158549f 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -619,6 +619,65 @@ insert into parted_conflict_1 values (40, 'cuarenta')
on conflict (a) do update set b = excluded.b;
drop table parted_conflict;
+-- a partition-local deferrable unique constraint on the arbiter columns
+-- must not be picked up as an additional arbiter for routed inserts
+create table parted_conflict (a int, b text, primary key (a)) partition by range (a);
+create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000);
+alter table parted_conflict_1 add constraint parted_conflict_1_a_def unique (a) deferrable;
+insert into parted_conflict values (40, 'forty');
+insert into parted_conflict values (40, 'cuarenta')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (41, 'forty-one')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (42, 'forty-two') on conflict (a) do nothing;
+select * from parted_conflict order by a;
+drop table parted_conflict;
+
+-- ON CONFLICT ON CONSTRAINT has the same hazard: an index that merely covers
+-- the same columns as the named constraint's index, but is deferrable or
+-- disagrees with it about duplicates, must not join the arbiter set
+create table namedconstraint (a int, b text, c text);
+alter table namedconstraint add constraint namedconstraint_a_key unique (a);
+alter table namedconstraint add constraint namedconstraint_a_def unique (a) deferrable;
+create unique index namedconstraint_b_nnd on namedconstraint (b) nulls not distinct;
+alter table namedconstraint add constraint namedconstraint_b_key unique (b);
+insert into namedconstraint values (1, 'x', 'first');
+insert into namedconstraint values (2, null, 'first');
+insert into namedconstraint values (1, 'y', 'second')
+ on conflict on constraint namedconstraint_a_key do update set c = excluded.c;
+insert into namedconstraint values (3, null, 'second')
+ on conflict on constraint namedconstraint_b_key do update set c = excluded.c; -- error
+insert into namedconstraint values (4, 'z', 'third')
+ on conflict on constraint namedconstraint_a_key do nothing;
+insert into namedconstraint values (5, null, 'third')
+ on conflict on constraint namedconstraint_b_key do nothing; -- error
+select * from namedconstraint order by a;
+drop table namedconstraint;
+
+-- same, with the indexes created in the opposite order
+create table namedconstraint2 (a int, b text, c text);
+alter table namedconstraint2 add constraint namedconstraint2_a_def unique (a) deferrable;
+alter table namedconstraint2 add constraint namedconstraint2_a_key unique (a);
+alter table namedconstraint2 add constraint namedconstraint2_b_key unique (b);
+create unique index namedconstraint2_b_nnd on namedconstraint2 (b) nulls not distinct;
+insert into namedconstraint2 values (1, 'x', 'first');
+insert into namedconstraint2 values (2, null, 'first');
+insert into namedconstraint2 values (1, 'y', 'second')
+ on conflict on constraint namedconstraint2_a_key do update set c = excluded.c;
+insert into namedconstraint2 values (3, 'z', 'second')
+ on conflict on constraint namedconstraint2_a_key do nothing;
+insert into namedconstraint2 values (4, null, 'second')
+ on conflict on constraint namedconstraint2_b_key do nothing; -- error
+-- ON CONFLICT DO SELECT reaches the same code
+insert into namedconstraint2 values (1, 'w', 'third')
+ on conflict on constraint namedconstraint2_a_key do select returning *;
+insert into namedconstraint2 values (5, 'v', 'third')
+ on conflict on constraint namedconstraint2_a_key do select returning *;
+insert into namedconstraint2 values (6, null, 'fourth')
+ on conflict on constraint namedconstraint2_b_key do select returning *; -- error
+select * from namedconstraint2 order by a;
+drop table namedconstraint2;
+
-- test whole-row Vars in ON CONFLICT expressions
create table parted_conflict (a int, b text, c int) partition by range (a);
create table parted_conflict_1 (drp text, c int, a int, b text);
--
2.47.3
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 23:47 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-01 06:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Michael Paquier <michael@paquier.xyz>
2026-09-01 09:15 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-09-01 22:05 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-17 21:08 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Álvaro Herrera <alvherre@kurilemu.de>
@ 2026-09-18 11:42 ` Álvaro Herrera <alvherre@kurilemu.de>
0 siblings, 0 replies; 16+ messages in thread
From: Álvaro Herrera @ 2026-09-18 11:42 UTC (permalink / raw)
To: Zsolt Parragi <zsolt.parragi@percona.com>; +Cc: Mihail Nikalayeu <mihailnikalayeu@gmail.com>; pgsql-hackers@lists.postgresql.org, Michael Paquier <michael@paquier.xyz>
Pushed, thanks.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
Syntax error: function hell() needs an argument.
Please choose what hell you want to involve.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 23:47 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-01 06:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Michael Paquier <michael@paquier.xyz>
2026-09-01 09:15 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
@ 2026-09-16 20:56 ` Dmitry Dolgov <9erthalion6@gmail.com>
2026-09-18 11:54 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Álvaro Herrera <alvherre@kurilemu.de>
1 sibling, 1 reply; 16+ messages in thread
From: Dmitry Dolgov @ 2026-09-16 20:56 UTC (permalink / raw)
To: Mihail Nikalayeu <mihailnikalayeu@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Zsolt Parragi <zsolt.parragi@percona.com>; pgsql-hackers@lists.postgresql.org
> On Tue, Sep 01, 2026 at 11:15:09AM +0200, Mihail Nikalayeu wrote:
> > 2bc7e886fc1 and the commit that has triggered this thread both refer
> > to the same set of improvements regarding the interactions of rebuilt
> > indexes and ON CONFLICT, so my take is that if we finish by updating
> > the same area of the code we could just group both changes together,
> > but I agree that my line of thoughts may be thin. It seems to me that
> > we'd better look first at this secondary issue before drawing a
> > conclusion regarding if both issues should be handled together or
> > separately.
>
> This is a "grouped" version. Also, it handles possible collation
> issues + provides a set of tests to pin the correct behaviour.
+/*
+ * IsIndexCompatibleAsArbiter
+ * Return true if two indexes of the same table are interchangeable as
+ * speculative insertion arbiters for INSERT ON CONFLICT.
+ *
+ * To be interchangeable, the two indexes must agree on which tuples conflict,
[...]
+ /* number of key attributes must match */
+ if (indexForm1->indnkeyatts != indexForm2->indnkeyatts)
+ return false;
I see that it was like this in the original commit, but isn't it too
restrictive regarding the goal stated in the function comment? If say
there are two unique indexes on columns (a), and (a, b), they have the
same understanding of what tuples will conflict, but the latter one will
not be used as an arbiter index. To be fair, I don't see how this may
become problem in practice, but still.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 23:47 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-01 06:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Michael Paquier <michael@paquier.xyz>
2026-09-01 09:15 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-09-16 20:56 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Dmitry Dolgov <9erthalion6@gmail.com>
@ 2026-09-18 11:54 ` Álvaro Herrera <alvherre@kurilemu.de>
2026-09-18 14:28 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Dmitry Dolgov <9erthalion6@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Álvaro Herrera @ 2026-09-18 11:54 UTC (permalink / raw)
To: Dmitry Dolgov <9erthalion6@gmail.com>; +Cc: Mihail Nikalayeu <mihailnikalayeu@gmail.com>; Michael Paquier <michael@paquier.xyz>; Zsolt Parragi <zsolt.parragi@percona.com>; pgsql-hackers@lists.postgresql.org
On 2026-Sep-16, Dmitry Dolgov wrote:
> + /* number of key attributes must match */
> + if (indexForm1->indnkeyatts != indexForm2->indnkeyatts)
> + return false;
>
> I see that it was like this in the original commit, but isn't it too
> restrictive regarding the goal stated in the function comment? If say
> there are two unique indexes on columns (a), and (a, b), they have the
> same understanding of what tuples will conflict, but the latter one will
> not be used as an arbiter index. To be fair, I don't see how this may
> become problem in practice, but still.
I'm not sure I understand this concern. Do you want to elaborate?
This functionality is there to support having two copies of "the same"
index during REINDEX CONCURRENTLY, and of course the second copy is
going to be identical in definition to the first one.
Maybe you want to propose a different name or a different comment for
this new function?
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 23:47 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-01 06:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Michael Paquier <michael@paquier.xyz>
2026-09-01 09:15 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-09-16 20:56 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Dmitry Dolgov <9erthalion6@gmail.com>
2026-09-18 11:54 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Álvaro Herrera <alvherre@kurilemu.de>
@ 2026-09-18 14:28 ` Dmitry Dolgov <9erthalion6@gmail.com>
2026-09-18 14:46 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Álvaro Herrera <alvherre@kurilemu.de>
0 siblings, 1 reply; 16+ messages in thread
From: Dmitry Dolgov @ 2026-09-18 14:28 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@kurilemu.de>; +Cc: Mihail Nikalayeu <mihailnikalayeu@gmail.com>; Michael Paquier <michael@paquier.xyz>; Zsolt Parragi <zsolt.parragi@percona.com>; pgsql-hackers@lists.postgresql.org
> On Fri, Sep 18, 2026 at 01:54:46PM +0200, Álvaro Herrera wrote:
> On 2026-Sep-16, Dmitry Dolgov wrote:
>
> > + /* number of key attributes must match */
> > + if (indexForm1->indnkeyatts != indexForm2->indnkeyatts)
> > + return false;
> >
> > I see that it was like this in the original commit, but isn't it too
> > restrictive regarding the goal stated in the function comment? If say
> > there are two unique indexes on columns (a), and (a, b), they have the
> > same understanding of what tuples will conflict, but the latter one will
> > not be used as an arbiter index. To be fair, I don't see how this may
> > become problem in practice, but still.
>
> I'm not sure I understand this concern. Do you want to elaborate?
>
> This functionality is there to support having two copies of "the same"
> index during REINDEX CONCURRENTLY, and of course the second copy is
> going to be identical in definition to the first one.
>
> Maybe you want to propose a different name or a different comment for
> this new function?
Yes, I get that. But the way how comment is written seems to emphasize
interchangeability of indexes in general, mentioning REINDEX
CONCURRENTLY as one use case, but not necessarily the only one -- or at
least it's my reading of it. Maybe a better commentary can solve it, but
since it's already pushed, consider it to be an optional nit pick.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 10:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 23:47 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-01 06:00 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Michael Paquier <michael@paquier.xyz>
2026-09-01 09:15 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-09-16 20:56 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Dmitry Dolgov <9erthalion6@gmail.com>
2026-09-18 11:54 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Álvaro Herrera <alvherre@kurilemu.de>
2026-09-18 14:28 ` Re: Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Dmitry Dolgov <9erthalion6@gmail.com>
@ 2026-09-18 14:46 ` Álvaro Herrera <alvherre@kurilemu.de>
0 siblings, 0 replies; 16+ messages in thread
From: Álvaro Herrera @ 2026-09-18 14:46 UTC (permalink / raw)
To: Dmitry Dolgov <9erthalion6@gmail.com>; +Cc: Mihail Nikalayeu <mihailnikalayeu@gmail.com>; Michael Paquier <michael@paquier.xyz>; Zsolt Parragi <zsolt.parragi@percona.com>; pgsql-hackers@lists.postgresql.org
On 2026-Sep-18, Dmitry Dolgov wrote:
> Yes, I get that. But the way how comment is written seems to emphasize
> interchangeability of indexes in general, mentioning REINDEX
> CONCURRENTLY as one use case, but not necessarily the only one -- or at
> least it's my reading of it. Maybe a better commentary can solve it, but
> since it's already pushed, consider it to be an optional nit pick.
I'll gladly take your nit pick.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
<inflex> really, I see PHP as like a strange amalgamation of C, Perl, Shell
<crab> inflex: you know that "amalgam" means "mixture with mercury",
more or less, right?
<crab> i.e., "deadly poison"
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2026-09-18 14:46 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-28 20:19 Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master Zsolt Parragi <zsolt.parragi@percona.com>
2026-08-31 08:07 ` Michael Paquier <michael@paquier.xyz>
2026-08-31 10:00 ` Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 22:21 ` Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-08-31 23:47 ` Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-01 06:00 ` Michael Paquier <michael@paquier.xyz>
2026-09-01 09:15 ` Mihail Nikalayeu <mihailnikalayeu@gmail.com>
2026-09-01 22:05 ` Zsolt Parragi <zsolt.parragi@percona.com>
2026-09-16 15:42 ` Nathan Bossart <nathandbossart@gmail.com>
2026-09-16 17:39 ` Alvaro Herrera <alvherre@kurilemu.de>
2026-09-17 21:08 ` Álvaro Herrera <alvherre@kurilemu.de>
2026-09-18 11:42 ` Álvaro Herrera <alvherre@kurilemu.de>
2026-09-16 20:56 ` Dmitry Dolgov <9erthalion6@gmail.com>
2026-09-18 11:54 ` Álvaro Herrera <alvherre@kurilemu.de>
2026-09-18 14:28 ` Dmitry Dolgov <9erthalion6@gmail.com>
2026-09-18 14:46 ` Álvaro Herrera <alvherre@kurilemu.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox