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(-)
