Hi,
While reviewing a patch I noticed that we allow some extraneous items in
ON CONFLICT clauses -- for instance,
create table tab (a int unique, b int);
insert into tab values (1, 1) on conflict (a int4_ops (fillfactor=10)) do
nothing;
Why do we accept reloptions there without complaint? Should we tighten
this up a little bit, or maybe it makes sense to accept this for some
reason? I suspect the reloptions were added to index_elems after the ON
CONFLICT clause was made to use that production, but I didn't check the
git history.
So what about the attached patch? I ran all tests and everything seems
to work correctly. (Maybe I'd add some tests to verify that this
new error is covered, as the ones just above.) It would complain to the
above:
ERROR: operator class options are not allowed in ON CONFLICT clause
LÍNEA 1: insert into tab values (1, 1) on conflict (a int4_ops (fillf...
^
This is certainly not very critical.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"El Maquinismo fue proscrito so pena de cosquilleo hasta la muerte"
(Ijon Tichy en Viajes, Stanislaw Lem)
>From 332db7b70a395da7e8628350285dd81505d81c20 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <[email protected]>
Date: Thu, 27 Nov 2025 16:51:31 +0100
Subject: [PATCH] Reject opclass options in ON CONFLICT clause
---
src/backend/parser/parse_clause.c | 16 +++++++++++-----
1 file changed, 11 insertions(+), 5 deletions(-)
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index ca26f6f61f2..bee9860c513 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -3277,11 +3277,11 @@ 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,
@@ -3295,6 +3295,12 @@ resolve_unique_index_expr(ParseState *pstate, InferClause *infer,
errmsg("NULLS FIRST/LAST is not allowed in ON CONFLICT clause"),
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)
{
--
2.47.3
