Attached patch fixes some issues with ON CONFLICT DO UPDATE/DO
NOTHING. There is a commit message which explains the changes at a
high level. The only real bug fix is around deparsing by ruleutils.c.
Note that the patch proposes to de-support CREATE RULE with an
alternative action involving ON CONFLICT DO UPDATE. Such a rule seems
particularly questionable to me, and I'm pretty sure it was understood
that only ON CONFLICT DO NOTHING should be supported as an action for
a rule (recall that INSERT statements with ON CONFLICT can, in
general, never target relations with rules). At least, I believe
Heikki said that.
Thoughts?
--
Peter Geoghegan
From 349a0a1fee6d86de8c5cc4120474ddc48aeb43e0 Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <peter.geoghega...@gmail.com>
Date: Mon, 11 May 2015 15:37:54 -0700
Subject: [PATCH] Fixes to a variety of minor ON CONFLICT issues
Deparsing with an inference clause is now correctly supported. However,
user-defined rules with ON CONFLICT DO UPDATE are now formally
disallowed/unsupported. It seemed there would be significant complexity
involved in making this work correctly with the EXCLUDED.*
pseudo-relation, which was not deemed worthwhile. Such a user-defined
rule seems very questionable anyway.
In passing, re-factor InferenceElem representation of opclass, to defer
opfamily lookup for Relation index matching until index inference proper
(i.e., within the optimizer). This is done for the convenience of the
new ruleutils.c code, but independently make senses.
Finally, fix a few typos, and rename a variable -- "candidates" seemed
like a misnomer for the return value of infer_arbiter_indexes().
---
contrib/pg_stat_statements/pg_stat_statements.c | 3 +-
doc/src/sgml/ref/create_rule.sgml | 5 ++
src/backend/executor/nodeModifyTable.c | 2 +-
src/backend/nodes/copyfuncs.c | 3 +-
src/backend/nodes/equalfuncs.c | 3 +-
src/backend/nodes/outfuncs.c | 3 +-
src/backend/nodes/readfuncs.c | 3 +-
src/backend/optimizer/plan/setrefs.c | 3 +-
src/backend/optimizer/util/plancat.c | 34 +++++++----
src/backend/parser/parse_clause.c | 16 ++----
src/backend/parser/parse_utilcmd.c | 5 ++
src/backend/utils/adt/ruleutils.c | 75 ++++++++++++++++++++++++-
src/include/nodes/primnodes.h | 3 +-
src/test/regress/expected/insert_conflict.out | 2 +-
src/test/regress/expected/rules.out | 67 ++++++++++++----------
src/test/regress/sql/rules.sql | 32 +++++++----
16 files changed, 177 insertions(+), 82 deletions(-)
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 6abe3f0..f97cc2c 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -2637,8 +2637,7 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
InferenceElem *ie = (InferenceElem *) node;
APP_JUMB(ie->infercollid);
- APP_JUMB(ie->inferopfamily);
- APP_JUMB(ie->inferopcinputtype);
+ APP_JUMB(ie->inferopclass);
JumbleExpr(jstate, ie->expr);
}
break;
diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml
index 53fdf56..46a8a7c 100644
--- a/doc/src/sgml/ref/create_rule.sgml
+++ b/doc/src/sgml/ref/create_rule.sgml
@@ -281,6 +281,11 @@ UPDATE mytable SET name = 'foo' WHERE id = 42;
match the condition <literal>id = 42</literal>. This is an
implementation restriction that might be fixed in future releases.
</para>
+ <para>
+ Presently, a rule alternative action cannot contain <literal>ON
+ CONFLICT DO UPDATE</literal>. This is an implementation
+ restriction that might be fixed in future releases.
+ </para>
</refsect1>
<refsect1>
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 34435c7..55a1cc7 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -414,7 +414,7 @@ ExecInsert(ModifyTableState *mtstate,
estate, true, &specConflict,
arbiterIndexes);
- /* adjust the tuple's state accordingly */
+ /* adjust the tuple's state */
if (!specConflict)
heap_finish_speculative(resultRelationDesc, tuple);
else
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 76b63af..957c2bc 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1803,8 +1803,7 @@ _copyInferenceElem(const InferenceElem *from)
COPY_NODE_FIELD(expr);
COPY_SCALAR_FIELD(infercollid);
- COPY_SCALAR_FIELD(inferopfamily);
- COPY_SCALAR_FIELD(inferopcinputtype);
+ COPY_SCALAR_FIELD(inferopclass);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index e032142..f26626e 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -687,8 +687,7 @@ _equalInferenceElem(const InferenceElem *a, const InferenceElem *b)
{
COMPARE_NODE_FIELD(expr);
COMPARE_SCALAR_FIELD(infercollid);
- COMPARE_SCALAR_FIELD(inferopfamily);
- COMPARE_SCALAR_FIELD(inferopcinputtype);
+ COMPARE_SCALAR_FIELD(inferopclass);
return true;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index fe868b8..454d9ec 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1450,8 +1450,7 @@ _outInferenceElem(StringInfo str, const InferenceElem *node)
WRITE_NODE_FIELD(expr);
WRITE_OID_FIELD(infercollid);
- WRITE_OID_FIELD(inferopfamily);
- WRITE_OID_FIELD(inferopcinputtype);
+ WRITE_OID_FIELD(inferopclass);
}
static void
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 8136306..81b6243 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1141,8 +1141,7 @@ _readInferenceElem(void)
READ_NODE_FIELD(expr);
READ_OID_FIELD(infercollid);
- READ_OID_FIELD(inferopfamily);
- READ_OID_FIELD(inferopcinputtype);
+ READ_OID_FIELD(inferopclass);
READ_DONE();
}
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index fac51c9..df11eea 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -740,7 +740,7 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
/*
* We treat ModifyTable with ON CONFLICT as a form of 'pseudo
- * join', where the inner side is the EXLUDED tuple. Therefore
+ * join', where the inner side is the EXCLUDED tuple. Therefore
* use fix_join_expr to setup the relevant variables to
* INNER_VAR. We explicitly don't create any OUTER_VARs as
* those are already used by RETURNING and it seems better to
@@ -793,6 +793,7 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
* global list.
*/
splan->resultRelIndex = list_length(root->glob->resultRelations);
+
root->glob->resultRelations =
list_concat(root->glob->resultRelations,
list_copy(splan->resultRelations));
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index b425680..a857ba3 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -438,8 +438,8 @@ infer_arbiter_indexes(PlannerInfo *root)
Bitmapset *inferAttrs = NULL;
List *inferElems = NIL;
- /* Result */
- List *candidates = NIL;
+ /* Results */
+ List *results = NIL;
/*
* Quickly return NIL for ON CONFLICT DO NOTHING without an inference
@@ -565,11 +565,11 @@ infer_arbiter_indexes(PlannerInfo *root)
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("ON CONFLICT DO UPDATE not supported with exclusion constraints")));
- candidates = lappend_oid(candidates, idxForm->indexrelid);
+ results = lappend_oid(results, idxForm->indexrelid);
list_free(indexList);
index_close(idxRel, NoLock);
heap_close(relation, NoLock);
- return candidates;
+ return results;
}
else if (indexOidFromConstraint != InvalidOid)
{
@@ -633,7 +633,7 @@ infer_arbiter_indexes(PlannerInfo *root)
* index definition.
*/
if (elem->infercollid != InvalidOid ||
- elem->inferopfamily != InvalidOid ||
+ elem->inferopclass != InvalidOid ||
list_member(idxExprs, elem->expr))
continue;
@@ -660,7 +660,7 @@ infer_arbiter_indexes(PlannerInfo *root)
if (!predicate_implied_by(predExprs, whereExplicit))
goto next;
- candidates = lappend_oid(candidates, idxForm->indexrelid);
+ results = lappend_oid(results, idxForm->indexrelid);
next:
index_close(idxRel, NoLock);
}
@@ -668,12 +668,12 @@ next:
list_free(indexList);
heap_close(relation, NoLock);
- if (candidates == NIL)
+ if (results == NIL)
ereport(ERROR,
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
errmsg("there is no unique or exclusion constraint matching the ON CONFLICT specification")));
- return candidates;
+ return results;
}
/*
@@ -709,23 +709,33 @@ infer_collation_opclass_match(InferenceElem *elem, Relation idxRel,
Bitmapset *inferAttrs, List *idxExprs)
{
AttrNumber natt;
+ Oid inferopfamily = InvalidOid; /* OID of att opfamily */
+ Oid inferopcinputtype = InvalidOid; /* OID of att opfamily */
/*
* If inference specification element lacks collation/opclass, then no
* need to check for exact match.
*/
- if (elem->infercollid == InvalidOid && elem->inferopfamily == InvalidOid)
+ if (elem->infercollid == InvalidOid && elem->inferopclass == InvalidOid)
return true;
+ /*
+ * Lookup opfamily and input type, for matching indexes
+ */
+ if (elem->inferopclass)
+ {
+ inferopfamily = get_opclass_family(elem->inferopclass);
+ inferopcinputtype = get_opclass_input_type(elem->inferopclass);
+ }
+
for (natt = 1; natt <= idxRel->rd_att->natts; natt++)
{
Oid opfamily = idxRel->rd_opfamily[natt - 1];
Oid opcinputtype = idxRel->rd_opcintype[natt - 1];
Oid collation = idxRel->rd_indcollation[natt - 1];
- if (elem->inferopfamily != InvalidOid &&
- (elem->inferopfamily != opfamily ||
- elem->inferopcinputtype != opcinputtype))
+ if (elem->inferopclass != InvalidOid &&
+ (inferopfamily != opfamily || inferopcinputtype != opcinputtype))
{
/* Attribute needed to match opclass, but didn't */
continue;
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 73c505e..77a7a43 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -2258,18 +2258,10 @@ resolve_unique_index_expr(ParseState *pstate, InferClause *infer,
exprLocation(pInfer->expr));
if (!ielem->opclass)
- {
- pInfer->inferopfamily = InvalidOid;
- pInfer->inferopcinputtype = InvalidOid;
- }
+ pInfer->inferopclass = InvalidOid;
else
- {
- Oid opclass = get_opclass_oid(BTREE_AM_OID, ielem->opclass,
- false);
-
- pInfer->inferopfamily = get_opclass_family(opclass);
- pInfer->inferopcinputtype = get_opclass_input_type(opclass);
- }
+ pInfer->inferopclass = get_opclass_oid(BTREE_AM_OID,
+ ielem->opclass, false);
result = lappend(result, pInfer);
}
@@ -2301,7 +2293,7 @@ transformOnConflictArbiter(ParseState *pstate,
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("ON CONFLICT DO UPDATE requires inference specification or constraint name"),
- errhint("For example, ON CONFLICT ON CONFLICT (<column>)."),
+ errhint("For example, ON CONFLICT (<column>)."),
parser_errposition(pstate,
exprLocation((Node *) onConflictClause))));
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 0a55db4..f0e996b 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -2221,6 +2221,11 @@ transformRuleStmt(RuleStmt *stmt, const char *queryString,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
errmsg("rules with WHERE conditions can only have SELECT, INSERT, UPDATE, or DELETE actions")));
+ if (top_subqry->onConflict && top_subqry->onConflict->action == ONCONFLICT_UPDATE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("rules with ON CONFLICT DO UPDATE are not implemented")));
+
/*
* If the action is INSERT...SELECT, OLD/NEW have been pushed down
* into the SELECT, and that's what we need to look at. (Ugly
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 903e80a..355acc9 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5319,13 +5319,38 @@ get_insert_query_def(Query *query, deparse_context *context)
{
OnConflictExpr *confl = query->onConflict;
+ appendStringInfo(buf, " ON CONFLICT");
+
+ if (confl->arbiterElems)
+ {
+ /* Add the single-VALUES expression list */
+ appendStringInfoChar(buf, '(');
+ get_rule_expr((Node *) confl->arbiterElems, context, false);
+ appendStringInfoChar(buf, ')');
+
+ /* Add a WHERE clause (for partial indexes) if given */
+ if (confl->arbiterWhere != NULL)
+ {
+ appendContextKeyword(context, " WHERE ",
+ -PRETTYINDENT_STD, PRETTYINDENT_STD, 1);
+ get_rule_expr(confl->arbiterWhere, context, false);
+ }
+ }
+ else
+ {
+ char *constraint = get_constraint_name(confl->constraint);
+
+ appendStringInfo(buf, " ON CONSTRAINT %s",
+ quote_qualified_identifier(NULL, constraint));
+ }
+
if (confl->action == ONCONFLICT_NOTHING)
{
- appendStringInfoString(buf, " ON CONFLICT DO NOTHING");
+ appendStringInfoString(buf, " DO NOTHING");
}
else
{
- appendStringInfoString(buf, " ON CONFLICT DO UPDATE SET ");
+ appendStringInfoString(buf, " DO UPDATE SET ");
/* Deparse targetlist */
get_update_query_targetlist_def(query, confl->onConflictSet,
context, rte);
@@ -7716,6 +7741,52 @@ get_rule_expr(Node *node, deparse_context *context,
}
break;
+ case T_InferenceElem:
+ {
+ InferenceElem *iexpr = (InferenceElem *) node;
+ bool varprefix = context->varprefix;
+ bool need_parens;
+
+ /*
+ * InfereneElem can only refer to target relation, so prefix is
+ * never useful
+ */
+ context->varprefix = false;
+
+ /*
+ * Parenthesize the element unless it's a simple Var or a bare
+ * function call. Follows pg_get_indexdef_worker().
+ */
+ need_parens = !IsA(iexpr->expr, Var);
+ if (IsA(iexpr->expr, FuncExpr) &&
+ ((FuncExpr *) iexpr->expr)->funcformat ==
+ COERCE_EXPLICIT_CALL)
+ need_parens = false;
+
+ if (need_parens)
+ appendStringInfoChar(buf, '(');
+ get_rule_expr((Node *) iexpr->expr,
+ context, showimplicit);
+
+ context->varprefix = varprefix;
+
+ if (iexpr->infercollid)
+ appendStringInfo(buf, " COLLATE %s",
+ generate_collation_name(iexpr->infercollid));
+
+ /* Add the operator class name, if not default */
+ if (iexpr->inferopclass)
+ {
+ Oid inferopclass = iexpr->inferopclass;
+ Oid inferopcinputtype = get_opclass_input_type(iexpr->inferopclass);
+
+ get_opclass_name(inferopclass, inferopcinputtype, buf);
+ }
+ if (need_parens)
+ appendStringInfoChar(buf, ')');
+ }
+ break;
+
case T_List:
{
char *sep;
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index f10ae4e..51a65bd 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1159,8 +1159,7 @@ typedef struct InferenceElem
Expr xpr;
Node *expr; /* expression to infer from, or NULL */
Oid infercollid; /* OID of collation, or InvalidOid */
- Oid inferopfamily; /* OID of att opfamily, or InvalidOid */
- Oid inferopcinputtype; /* OID of att input type, or InvalidOid */
+ Oid inferopclass; /* OID of att opclass, or InvalidOid */
} InferenceElem;
/*--------------------
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 3273d98..a6abd8b 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -192,7 +192,7 @@ insert into insertconflicttest values (1, 'Apple') on conflict do update set fru
ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name
LINE 1: ...nsert into insertconflicttest values (1, 'Apple') on conflic...
^
-HINT: For example, ON CONFLICT ON CONFLICT (<column>).
+HINT: For example, ON CONFLICT (<column>).
-- inference succeeds:
insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit;
insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key) do update set fruit = excluded.fruit;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index a379a72..320758d 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2777,16 +2777,17 @@ CREATE TABLE hats (
hat_color char(10) -- hat color
);
CREATE TABLE hat_data (
- hat_name char(10) primary key,
+ hat_name char(10),
hat_color char(10) -- hat color
);
+create unique index hat_data_cons on hat_data (hat_name COLLATE "C" bpchar_pattern_ops);
-- okay
CREATE RULE hat_nosert AS ON INSERT TO hats
DO INSTEAD
INSERT INTO hat_data VALUES (
NEW.hat_name,
NEW.hat_color)
- ON CONFLICT (hat_name) DO NOTHING RETURNING *;
+ ON CONFLICT (hat_name COLLATE "C" bpchar_pattern_ops) WHERE hat_color = 'green' DO NOTHING RETURNING *;
-- Works (projects row)
INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
hat_name | hat_color
@@ -2800,42 +2801,48 @@ INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
----------+-----------
(0 rows)
+-- Deparse statement, and drop rule
SELECT tablename, rulename, definition FROM pg_rules
WHERE tablename = 'hats';
- tablename | rulename | definition
------------+------------+------------------------------------------------------------------------------
- hats | hat_nosert | CREATE RULE hat_nosert AS +
- | | ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color)+
- | | VALUES (new.hat_name, new.hat_color) ON CONFLICT DO NOTHING +
- | | RETURNING hat_data.hat_name, +
+ tablename | rulename | definition
+-----------+------------+---------------------------------------------------------------------------------------------
+ hats | hat_nosert | CREATE RULE hat_nosert AS +
+ | | ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
+ | | VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+
+ | | WHERE (hat_data.hat_color = 'green'::bpchar) DO NOTHING +
+ | | RETURNING hat_data.hat_name, +
| | hat_data.hat_color;
(1 row)
DROP RULE hat_nosert ON hats;
+-- okay (but requires additional parens)
+CREATE RULE hat_nosert2 AS ON INSERT TO hats
+ DO INSTEAD
+ INSERT INTO hat_data VALUES (
+ NEW.hat_name,
+ NEW.hat_color)
+ ON CONFLICT ((hat_name || ' Complex Statement')) DO NOTHING RETURNING *;
+-- Deparse statement, and drop rule
+SELECT tablename, rulename, definition FROM pg_rules
+ WHERE tablename = 'hats';
+ tablename | rulename | definition
+-----------+-------------+-------------------------------------------------------------------------------------------------------------------
+ hats | hat_nosert2 | CREATE RULE hat_nosert2 AS +
+ | | ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
+ | | VALUES (new.hat_name, new.hat_color) ON CONFLICT((((hat_name)::text || ' Complex Statement'::text))) DO NOTHING+
+ | | RETURNING hat_data.hat_name, +
+ | | hat_data.hat_color;
+(1 row)
+
+DROP RULE hat_nosert2 ON hats;
+-- Fails (unsupported)
CREATE RULE hat_upsert AS ON INSERT TO hats
DO INSTEAD
- INSERT INTO hat_data VALUES (
- NEW.hat_name,
+ INSERT INTO hat_data AS h VALUES (
+ 'orange',
NEW.hat_color)
- ON CONFLICT (hat_name) DO UPDATE SET hat_color = 'Orange' RETURNING *;
--- Works (does upsert)
-INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
- hat_name | hat_color
-------------+------------
- h7 | Orange
-(1 row)
-
-SELECT tablename, rulename, definition FROM pg_rules
- WHERE tablename = 'hats';
- tablename | rulename | definition
------------+------------+-----------------------------------------------------------------------------------------------
- hats | hat_upsert | CREATE RULE hat_upsert AS +
- | | ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
- | | VALUES (new.hat_name, new.hat_color) ON CONFLICT DO UPDATE SET hat_color = 'Orange'::bpchar+
- | | RETURNING hat_data.hat_name, +
- | | hat_data.hat_color;
-(1 row)
-
-DROP RULE hat_upsert ON hats;
+ ON CONFLICT (hat_name) DO UPDATE SET hat_color = excluded.hat_color
+ WHERE h.hat_color != 'yellow' and excluded.hat_color != 'grey' RETURNING *;
+ERROR: rules with ON CONFLICT DO UPDATE are not implemented
drop table hats;
drop table hat_data;
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
index 6f1a1b8..611f1c4 100644
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -1049,9 +1049,10 @@ CREATE TABLE hats (
);
CREATE TABLE hat_data (
- hat_name char(10) primary key,
+ hat_name char(10),
hat_color char(10) -- hat color
);
+create unique index hat_data_cons on hat_data (hat_name COLLATE "C" bpchar_pattern_ops);
-- okay
CREATE RULE hat_nosert AS ON INSERT TO hats
@@ -1059,28 +1060,37 @@ CREATE RULE hat_nosert AS ON INSERT TO hats
INSERT INTO hat_data VALUES (
NEW.hat_name,
NEW.hat_color)
- ON CONFLICT (hat_name) DO NOTHING RETURNING *;
+ ON CONFLICT (hat_name COLLATE "C" bpchar_pattern_ops) WHERE hat_color = 'green' DO NOTHING RETURNING *;
-- Works (projects row)
INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
-- Works (does nothing)
INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
+-- Deparse statement, and drop rule
SELECT tablename, rulename, definition FROM pg_rules
WHERE tablename = 'hats';
DROP RULE hat_nosert ON hats;
+-- okay (but requires additional parens)
+CREATE RULE hat_nosert2 AS ON INSERT TO hats
+ DO INSTEAD
+ INSERT INTO hat_data VALUES (
+ NEW.hat_name,
+ NEW.hat_color)
+ ON CONFLICT ((hat_name || ' Complex Statement')) DO NOTHING RETURNING *;
+-- Deparse statement, and drop rule
+SELECT tablename, rulename, definition FROM pg_rules
+ WHERE tablename = 'hats';
+DROP RULE hat_nosert2 ON hats;
+
+-- Fails (unsupported)
CREATE RULE hat_upsert AS ON INSERT TO hats
DO INSTEAD
- INSERT INTO hat_data VALUES (
- NEW.hat_name,
+ INSERT INTO hat_data AS h VALUES (
+ 'orange',
NEW.hat_color)
- ON CONFLICT (hat_name) DO UPDATE SET hat_color = 'Orange' RETURNING *;
-
--- Works (does upsert)
-INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
-SELECT tablename, rulename, definition FROM pg_rules
- WHERE tablename = 'hats';
-DROP RULE hat_upsert ON hats;
+ ON CONFLICT (hat_name) DO UPDATE SET hat_color = excluded.hat_color
+ WHERE h.hat_color != 'yellow' and excluded.hat_color != 'grey' RETURNING *;
drop table hats;
drop table hat_data;
--
1.9.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
