From 630a93799d538c35c94187e07ef64d566a573a4e Mon Sep 17 00:00:00 2001 From: =?utf8?q?=C3=81lvaro=20Herrera?= Date: Fri, 12 Dec 2025 14:26:42 +0100 Subject: [PATCH] Reject opclass options in ON CONFLICT clause MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit It's as pointless as ASC/DESC and NULLS FIRST/LAST are, so reject all of them in the same way. While at it, normalize the others' error messages to have less translatable strings. Add tests for these errors. Noticed while reviewing recent INSERT ON CONFLICT patches. Author: Álvaro Herrera Reviewed-by: Peter Geoghegan Discussion: https://postgr.es/m/202511271516.oiefpvn3z27m@alvherre.pgsql --- src/backend/parser/parse_clause.c | 22 +++++++++++++------ src/test/regress/expected/insert_conflict.out | 13 +++++++++++ src/test/regress/sql/insert_conflict.sql | 5 +++++ 3 files changed, 33 insertions(+), 7 deletions(-) diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 944482207f3..57609e2d55c 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -3277,24 +3277,32 @@ resolve_unique_index_expr(ParseState *pstate, InferClause *infer, * Raw grammar re-uses CREATE INDEX infrastructure for unique index * inference clause, and so will accept opclasses by name and so on. * - * Make no attempt to match ASC or DESC ordering or NULLS FIRST/NULLS - * LAST ordering, since those are not significant for inference - * purposes (any unique index matching the inference specification in - * other regards is accepted indifferently). Actively reject this as - * wrong-headed. + * Make no attempt to match ASC or DESC ordering, NULLS FIRST/NULLS + * LAST ordering or opclass options, since those are not significant + * for inference purposes (any unique index matching the inference + * specification in other regards is accepted indifferently). Actively + * reject this as wrong-headed. */ if (ielem->ordering != SORTBY_DEFAULT) ereport(ERROR, (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), - errmsg("ASC/DESC is not allowed in ON CONFLICT clause"), + errmsg("%s is not allowed in ON CONFLICT clause", + "ASC/DESC"), parser_errposition(pstate, exprLocation((Node *) infer)))); if (ielem->nulls_ordering != SORTBY_NULLS_DEFAULT) ereport(ERROR, (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), - errmsg("NULLS FIRST/LAST is not allowed in ON CONFLICT clause"), + errmsg("%s is not allowed in ON CONFLICT clause", + "NULLS FIRST/LAST"), parser_errposition(pstate, exprLocation((Node *) infer)))); + if (ielem->opclassopts) + ereport(ERROR, + errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("operator class options are not allowed in ON CONFLICT clause"), + parser_errposition(pstate, + exprLocation((Node *) infer))); if (!ielem->expr) { diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out index db668474684..91fbe91844d 100644 --- a/src/test/regress/expected/insert_conflict.out +++ b/src/test/regress/expected/insert_conflict.out @@ -2,6 +2,19 @@ -- insert...on conflict do unique index inference -- create table insertconflicttest(key int4, fruit text); +-- invalid clauses +insert into insertconflicttest values (1) on conflict (key int4_ops (fillfactor=10)) do nothing; +ERROR: operator class options are not allowed in ON CONFLICT clause +LINE 1: ...rt into insertconflicttest values (1) on conflict (key int4_... + ^ +insert into insertconflicttest values (1) on conflict (key asc) do nothing; +ERROR: ASC/DESC is not allowed in ON CONFLICT clause +LINE 1: ...rt into insertconflicttest values (1) on conflict (key asc) ... + ^ +insert into insertconflicttest values (1) on conflict (key nulls last) do nothing; +ERROR: NULLS FIRST/LAST is not allowed in ON CONFLICT clause +LINE 1: ...rt into insertconflicttest values (1) on conflict (key nulls... + ^ -- These things should work through a view, as well create view insertconflictview as select * from insertconflicttest; -- diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql index 549c46452ec..03b1f0e44b0 100644 --- a/src/test/regress/sql/insert_conflict.sql +++ b/src/test/regress/sql/insert_conflict.sql @@ -3,6 +3,11 @@ -- create table insertconflicttest(key int4, fruit text); +-- invalid clauses +insert into insertconflicttest values (1) on conflict (key int4_ops (fillfactor=10)) do nothing; +insert into insertconflicttest values (1) on conflict (key asc) do nothing; +insert into insertconflicttest values (1) on conflict (key nulls last) do nothing; + -- These things should work through a view, as well create view insertconflictview as select * from insertconflicttest; -- 2.39.5