Fix ON CONFLICT inference for index expressions simplified to a Var
infer_arbiter_indexes() classifies each inference element by its
const-simplified form: a plain Var is matched against the index's
plain attributes, and anything else against its expressions. Index
columns, however, were classified by indkey alone, so an expression
column stayed an expression even when const-simplification reduced it
to a plain Var. Since commit 10c4fe074, COALESCE on a NOT NULL column
is reduced to the column itself, so an inference element such as
COALESCE(b, '') became a plain Var that no longer matched the unique
index on that same expression, and the statement failed with "there is
no unique or exclusion constraint matching the ON CONFLICT
specification".
To fix, classify index columns the same way as inference elements: an
index expression that is simplified to a plain Var is treated as a
plain attribute. Also, teach infer_collation_opclass_match() to
compare such a column as an expression, so that an inference element
that specifies a collation or operator class can still match it.
As a consequence, an inference element naming the column directly now
matches such an index too, which is consistent with how an element
written as the expression already matches a plain index on the column.
Author: Shihao Zhong <zhong950419@gmail.com>
Reviewed-by: jian he <jian.universality@gmail.com>
Reviewed-by: Richard Guo <guofenglinux@gmail.com>
Discussion: https://postgr.es/m/CAGRkXqR7VXacmRP41UBaM5u4aseeCH5ECVmQgn_YS=L7=xdhTw@mail.gmail.com
Backpatch-through: 19
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/62e46c00c657fc2e092ceec0cd79d16035798afe
Modified Files
--------------
src/backend/optimizer/util/plancat.c | 64 +++++++++++++++++++--------
src/test/regress/expected/insert_conflict.out | 27 +++++++++++
src/test/regress/sql/insert_conflict.sql | 13 ++++++
3 files changed, 85 insertions(+), 19 deletions(-)
