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 <[email protected]> Reviewed-by: jian he <[email protected]> Reviewed-by: Richard Guo <[email protected]> Discussion: https://postgr.es/m/CAGRkXqR7VXacmRP41UBaM5u4aseeCH5ECVmQgn_YS=L7=xd...@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(-)
