pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Tighten definition of ON CONFLICT arbiter index equivalence 2+ messages / 1 participants [nested] [flat]
* pgsql: Tighten definition of ON CONFLICT arbiter index equivalence @ 2026-09-18 11:41 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 2+ messages in thread From: Álvaro Herrera @ 2026-09-18 11:41 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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); also, use equal() to compare the expression and predicate lists rather than list_difference(). 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 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/dc5f8cb93467d8e46df9cebea0e35aed4f863768 Modified Files -------------- src/backend/catalog/index.c | 74 +++++++++++++++++ src/backend/executor/execPartition.c | 65 +-------------- src/backend/optimizer/util/plancat.c | 107 ++++++++++--------------- src/include/catalog/index.h | 3 + src/test/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, 321 insertions(+), 129 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Tighten definition of ON CONFLICT arbiter index equivalence @ 2026-09-18 11:41 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 2+ messages in thread From: Álvaro Herrera @ 2026-09-18 11:41 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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); also, use equal() to compare the expression and predicate lists rather than list_difference(). 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 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/67fd7002b6d4d95fd356b90b4c9c18e5c7f3c039 Modified Files -------------- src/backend/catalog/index.c | 74 +++++++++++++++++ src/backend/executor/execPartition.c | 65 +-------------- src/backend/optimizer/util/plancat.c | 107 ++++++++++--------------- src/include/catalog/index.h | 3 + src/test/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, 321 insertions(+), 129 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-18 11:41 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-09-18 11:41 pgsql: Tighten definition of ON CONFLICT arbiter index equivalence Álvaro Herrera <alvherre@kurilemu.de> 2026-09-18 11:41 pgsql: Tighten definition of ON CONFLICT arbiter index equivalence Á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