Add ON CONFLICT DO RETURN clause
Started by Wolfgang Waltherover 3 years ago4 messages
When using ON CONFLICT DO NOTHING together with RETURNING, the
conflicted rows are not returned. Sometimes, this would be useful
though, for example when generated columns or default values are in play:
CREATE TABLE x (
id INT PRIMARY KEY,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMEMSTAMP
);
To get the created_at timestamp for a certain id **and** at the same
time create this id in case it does not exist, yet, I can currently do:
INSERT INTO x (id) VALUES (1)
ON CONFLICT DO UPDATE
SET id=EXCLUDED.id
RETURNING created_at;
However that will result in a useless UPDATE of the row.
I could probably add a trigger to prevent the UPDATE in that case. Or I
could do something in a CTE. Or in multiple statements in plpgsql - this
is what I currently do in application code.
The attached patch adds a DO RETURN clause to be able to do this:
INSERT INTO x (id) VALUES (1)
ON CONFLICT DO RETURN
RETURNING created_at;
Much simpler. This will either insert or do nothing - but in both cases
return a row.
Thoughts?
Best
Wolfgang
Attachments:
v1-0001-Add-ON-CONFLICT-DO-RETURN-clause.patchtext/plain; charset=UTF-8; name=v1-0001-Add-ON-CONFLICT-DO-RETURN-clause.patchDownload
>From 83a0031ed2ded46cbf6fd130bd76680267db7a5e Mon Sep 17 00:00:00 2001
From: Wolfgang Walther <walther@technowledgy.de>
Date: Sun, 25 Sep 2022 16:20:44 +0200
Subject: [PATCH v1] Add ON CONFLICT DO RETURN clause
This behaves the same as DO NOTHING, but returns the row when used together with RETURNING.
---
doc/src/sgml/postgres-fdw.sgml | 6 +-
doc/src/sgml/ref/insert.sgml | 15 +-
src/backend/commands/explain.c | 21 +-
src/backend/executor/nodeModifyTable.c | 24 +-
src/backend/optimizer/util/plancat.c | 4 +
src/backend/parser/gram.y | 10 +
src/backend/parser/parse_clause.c | 7 +
src/backend/utils/adt/ruleutils.c | 4 +
src/include/nodes/nodes.h | 1 +
.../expected/insert-conflict-do-nothing-2.out | 186 ++++++++++++++++
.../expected/insert-conflict-do-nothing.out | 46 ++++
.../expected/partition-key-update-3.out | 206 ++++++++++++++++++
.../specs/insert-conflict-do-nothing-2.spec | 11 +
.../specs/insert-conflict-do-nothing.spec | 5 +
.../specs/partition-key-update-3.spec | 11 +
src/test/regress/expected/insert_conflict.out | 25 +++
src/test/regress/sql/insert_conflict.sql | 19 ++
17 files changed, 587 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index bfd344cdc0..e5b6b8501f 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -80,9 +80,9 @@
<para>
Note that <filename>postgres_fdw</filename> currently lacks support for
<command>INSERT</command> statements with an <literal>ON CONFLICT DO
- UPDATE</literal> clause. However, the <literal>ON CONFLICT DO NOTHING</literal>
- clause is supported, provided a unique index inference specification
- is omitted.
+ UPDATE</literal> or <literal>ON CONFLICT DO RETURN</literal> clause.
+ However, the <literal>ON CONFLICT DO NOTHING</literal> clause is supported,
+ provided a unique index inference specification is omitted.
Note also that <filename>postgres_fdw</filename> supports row movement
invoked by <command>UPDATE</command> statements executed on partitioned
tables, but it currently does not handle the case where a remote partition
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 7cea70329e..eb0c721637 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -36,6 +36,7 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
<phrase>and <replaceable class="parameter">conflict_action</replaceable> is one of:</phrase>
DO NOTHING
+ DO RETURN
DO UPDATE SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> )
@@ -336,9 +337,11 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
<parameter>conflict_target</parameter> is violated, the
alternative <parameter>conflict_action</parameter> is taken.
<literal>ON CONFLICT DO NOTHING</literal> simply avoids inserting
- a row as its alternative action. <literal>ON CONFLICT DO
- UPDATE</literal> updates the existing row that conflicts with the
- row proposed for insertion as its alternative action.
+ a row as its alternative action. <literal>ON CONFLICT DO RETURN</literal>
+ avoids inserting the row, but returns the row when <literal>RETURNING</literal>
+ is specified. <literal>ON CONFLICT DO UPDATE</literal> updates the
+ existing row that conflicts with the row proposed for insertion as
+ its alternative action.
</para>
<para>
@@ -379,7 +382,7 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
the alternative action on by choosing <firstterm>arbiter
indexes</firstterm>. Either performs <emphasis>unique index
inference</emphasis>, or names a constraint explicitly. For
- <literal>ON CONFLICT DO NOTHING</literal>, it is optional to
+ <literal>DO NOTHING</literal> and <literal>DO RETURN</literal>, it is optional to
specify a <parameter>conflict_target</parameter>; when
omitted, conflicts with all usable constraints (and unique
indexes) are handled. For <literal>ON CONFLICT DO
@@ -395,8 +398,8 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
<para>
<parameter>conflict_action</parameter> specifies an
alternative <literal>ON CONFLICT</literal> action. It can be
- either <literal>DO NOTHING</literal>, or a <literal>DO
- UPDATE</literal> clause specifying the exact details of the
+ either one of <literal>DO NOTHING</literal> and <literal>DO RETURN</literal>
+ or a <literal>DO UPDATE</literal> clause specifying the exact details of the
<literal>UPDATE</literal> action to be performed in case of a
conflict. The <literal>SET</literal> and
<literal>WHERE</literal> clauses in <literal>ON CONFLICT DO
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index f86983c660..632ef837cd 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -4022,10 +4022,23 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
if (node->onConflictAction != ONCONFLICT_NONE)
{
- ExplainPropertyText("Conflict Resolution",
- node->onConflictAction == ONCONFLICT_NOTHING ?
- "NOTHING" : "UPDATE",
- es);
+ const char *action;
+
+ switch (node->onConflictAction)
+ {
+ case ONCONFLICT_NOTHING:
+ action = "NOTHING";
+ break;
+ case ONCONFLICT_RETURN:
+ action = "RETURN";
+ break;
+ case ONCONFLICT_UPDATE:
+ action = "UPDATE";
+ break;
+ default:
+ action = "???";
+ }
+ ExplainPropertyText("Conflict Resolution", action, es);
/*
* Don't display arbiter indexes at all when DO NOTHING variant
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 775960827d..e75eddee50 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -986,6 +986,29 @@ ExecInsert(ModifyTableContext *context,
else
goto vlock;
}
+ else if (onconflict == ONCONFLICT_RETURN && resultRelInfo->ri_projectReturning)
+ {
+ /*
+ * In case of ON CONFLICT DO RETURN, fetch the tuple and
+ * verify that it is visible to the executor's MVCC
+ * snapshot at higher isolation levels.
+ *
+ * Using ExecGetReturningSlot() to store the tuple isn't that
+ * pretty, but we can't trivially use the input slot, because
+ * it might not be of a compatible type. As there's no conflicting
+ * usage of ExecGetReturningSlot() in the DO RETURN case...
+ */
+ TupleTableSlot *returning = ExecGetReturningSlot(estate, resultRelInfo);
+
+ if (!table_tuple_fetch_row_version(resultRelationDesc, &conflictTid, SnapshotAny, returning))
+ elog(ERROR, "failed to fetch conflicting tuple for ON CONFLICT");
+
+ ExecCheckTupleVisible(estate, resultRelationDesc, returning);
+ result = ExecProcessReturning(resultRelInfo, returning, planSlot);
+ ExecClearTuple(returning);
+ InstrCountTuples2(&mtstate->ps, 1);
+ return result;
+ }
else
{
/*
@@ -999,7 +1022,6 @@ ExecInsert(ModifyTableContext *context,
* type. As there's no conflicting usage of
* ExecGetReturningSlot() in the DO NOTHING case...
*/
- Assert(onconflict == ONCONFLICT_NOTHING);
ExecCheckTIDVisible(estate, resultRelInfo, &conflictTid,
ExecGetReturningSlot(estate, resultRelInfo));
InstrCountTuples2(&mtstate->ps, 1);
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 6d5718ee4c..3af984df84 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -739,6 +739,10 @@ infer_arbiter_indexes(PlannerInfo *root)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("ON CONFLICT DO UPDATE not supported with exclusion constraints")));
+ if (!idxForm->indisunique && onconflict->action == ONCONFLICT_RETURN)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("ON CONFLICT DO RETURN not supported with exclusion constraints")));
results = lappend_oid(results, idxForm->indexrelid);
list_free(indexList);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0d8d292850..1dd93229d6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -12018,6 +12018,16 @@ opt_on_conflict:
$$->location = @1;
}
|
+ ON CONFLICT opt_conf_expr DO RETURN
+ {
+ $$ = makeNode(OnConflictClause);
+ $$->action = ONCONFLICT_RETURN;
+ $$->infer = $3;
+ $$->targetList = NIL;
+ $$->whereClause = NULL;
+ $$->location = @1;
+ }
+ |
ON CONFLICT opt_conf_expr DO NOTHING
{
$$ = makeNode(OnConflictClause);
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 202a38f813..50693b9fbf 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -3179,6 +3179,13 @@ transformOnConflictArbiter(ParseState *pstate,
errhint("For example, ON CONFLICT (column_name)."),
parser_errposition(pstate,
exprLocation((Node *) onConflictClause))));
+ if (onConflictClause->action == ONCONFLICT_RETURN && !infer)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("ON CONFLICT DO RETURN requires inference specification or constraint name"),
+ errhint("For example, ON CONFLICT (column_name)."),
+ parser_errposition(pstate,
+ exprLocation((Node *) onConflictClause))));
/*
* To simplify certain aspects of its design, speculative insertion into
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 2b7b1b0c0f..7f775b17bc 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -6786,6 +6786,10 @@ get_insert_query_def(Query *query, deparse_context *context,
{
appendStringInfoString(buf, " DO NOTHING");
}
+ else if (confl->action == ONCONFLICT_RETURN)
+ {
+ appendStringInfoString(buf, " DO RETURN");
+ }
else
{
appendStringInfoString(buf, " DO UPDATE SET ");
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index a80f43e540..e96a84f814 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -408,6 +408,7 @@ typedef enum OnConflictAction
{
ONCONFLICT_NONE, /* No "ON CONFLICT" clause */
ONCONFLICT_NOTHING, /* ON CONFLICT ... DO NOTHING */
+ ONCONFLICT_RETURN, /* ON CONFLICT ... DO RETURN */
ONCONFLICT_UPDATE /* ON CONFLICT ... DO UPDATE */
} OnConflictAction;
diff --git a/src/test/isolation/expected/insert-conflict-do-nothing-2.out b/src/test/isolation/expected/insert-conflict-do-nothing-2.out
index 22d41d33ed..2a2114499c 100644
--- a/src/test/isolation/expected/insert-conflict-do-nothing-2.out
+++ b/src/test/isolation/expected/insert-conflict-do-nothing-2.out
@@ -119,3 +119,189 @@ key|val
1|donothing2
(1 row)
+
+starting permutation: beginrr1 beginrr2 doreturn1 c1 doreturn2 c2 show
+step beginrr1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step beginrr2: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step doreturn1: INSERT INTO ints(key, val) VALUES(1, 'doreturn1') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn1
+(1 row)
+
+step c1: COMMIT;
+step doreturn2: INSERT INTO ints(key, val) VALUES(1, 'doreturn2'), (1, 'doreturn3') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn1
+ 1|doreturn1
+(2 rows)
+
+step c2: COMMIT;
+step show: SELECT * FROM ints;
+key|val
+---+---------
+ 1|doreturn1
+(1 row)
+
+
+starting permutation: beginrr1 beginrr2 doreturn2 c2 doreturn1 c1 show
+step beginrr1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step beginrr2: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step doreturn2: INSERT INTO ints(key, val) VALUES(1, 'doreturn2'), (1, 'doreturn3') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn2
+ 1|doreturn2
+(2 rows)
+
+step c2: COMMIT;
+step doreturn1: INSERT INTO ints(key, val) VALUES(1, 'doreturn1') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn2
+(1 row)
+
+step c1: COMMIT;
+step show: SELECT * FROM ints;
+key|val
+---+---------
+ 1|doreturn2
+(1 row)
+
+
+starting permutation: beginrr1 beginrr2 doreturn1 doreturn2 c1 c2 show
+step beginrr1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step beginrr2: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step doreturn1: INSERT INTO ints(key, val) VALUES(1, 'doreturn1') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn1
+(1 row)
+
+step doreturn2: INSERT INTO ints(key, val) VALUES(1, 'doreturn2'), (1, 'doreturn3') ON CONFLICT (key) DO RETURN RETURNING *; <waiting ...>
+step c1: COMMIT;
+step doreturn2: <... completed>
+ERROR: could not serialize access due to concurrent update
+step c2: COMMIT;
+step show: SELECT * FROM ints;
+key|val
+---+---------
+ 1|doreturn1
+(1 row)
+
+
+starting permutation: beginrr1 beginrr2 doreturn2 doreturn1 c2 c1 show
+step beginrr1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step beginrr2: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step doreturn2: INSERT INTO ints(key, val) VALUES(1, 'doreturn2'), (1, 'doreturn3') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn2
+ 1|doreturn2
+(2 rows)
+
+step doreturn1: INSERT INTO ints(key, val) VALUES(1, 'doreturn1') ON CONFLICT (key) DO RETURN RETURNING *; <waiting ...>
+step c2: COMMIT;
+step doreturn1: <... completed>
+ERROR: could not serialize access due to concurrent update
+step c1: COMMIT;
+step show: SELECT * FROM ints;
+key|val
+---+---------
+ 1|doreturn2
+(1 row)
+
+
+starting permutation: begins1 begins2 doreturn1 c1 doreturn2 c2 show
+step begins1: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step begins2: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step doreturn1: INSERT INTO ints(key, val) VALUES(1, 'doreturn1') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn1
+(1 row)
+
+step c1: COMMIT;
+step doreturn2: INSERT INTO ints(key, val) VALUES(1, 'doreturn2'), (1, 'doreturn3') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn1
+ 1|doreturn1
+(2 rows)
+
+step c2: COMMIT;
+step show: SELECT * FROM ints;
+key|val
+---+---------
+ 1|doreturn1
+(1 row)
+
+
+starting permutation: begins1 begins2 doreturn2 c2 doreturn1 c1 show
+step begins1: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step begins2: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step doreturn2: INSERT INTO ints(key, val) VALUES(1, 'doreturn2'), (1, 'doreturn3') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn2
+ 1|doreturn2
+(2 rows)
+
+step c2: COMMIT;
+step doreturn1: INSERT INTO ints(key, val) VALUES(1, 'doreturn1') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn2
+(1 row)
+
+step c1: COMMIT;
+step show: SELECT * FROM ints;
+key|val
+---+---------
+ 1|doreturn2
+(1 row)
+
+
+starting permutation: begins1 begins2 doreturn1 doreturn2 c1 c2 show
+step begins1: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step begins2: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step doreturn1: INSERT INTO ints(key, val) VALUES(1, 'doreturn1') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn1
+(1 row)
+
+step doreturn2: INSERT INTO ints(key, val) VALUES(1, 'doreturn2'), (1, 'doreturn3') ON CONFLICT (key) DO RETURN RETURNING *; <waiting ...>
+step c1: COMMIT;
+step doreturn2: <... completed>
+ERROR: could not serialize access due to concurrent update
+step c2: COMMIT;
+step show: SELECT * FROM ints;
+key|val
+---+---------
+ 1|doreturn1
+(1 row)
+
+
+starting permutation: begins1 begins2 doreturn2 doreturn1 c2 c1 show
+step begins1: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step begins2: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step doreturn2: INSERT INTO ints(key, val) VALUES(1, 'doreturn2'), (1, 'doreturn3') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn2
+ 1|doreturn2
+(2 rows)
+
+step doreturn1: INSERT INTO ints(key, val) VALUES(1, 'doreturn1') ON CONFLICT (key) DO RETURN RETURNING *; <waiting ...>
+step c2: COMMIT;
+step doreturn1: <... completed>
+ERROR: could not serialize access due to concurrent update
+step c1: COMMIT;
+step show: SELECT * FROM ints;
+key|val
+---+---------
+ 1|doreturn2
+(1 row)
+
diff --git a/src/test/isolation/expected/insert-conflict-do-nothing.out b/src/test/isolation/expected/insert-conflict-do-nothing.out
index cadf46d065..3837b2559f 100644
--- a/src/test/isolation/expected/insert-conflict-do-nothing.out
+++ b/src/test/isolation/expected/insert-conflict-do-nothing.out
@@ -25,3 +25,49 @@ key|val
(1 row)
step c2: COMMIT;
+
+starting permutation: doreturn1 doreturn2 c1 select2 c2
+step doreturn1: INSERT INTO ints(key, val) VALUES(1, 'doreturn1') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn1
+(1 row)
+
+step doreturn2: INSERT INTO ints(key, val) VALUES(1, 'doreturn2') ON CONFLICT (key) DO RETURN RETURNING *; <waiting ...>
+step c1: COMMIT;
+step doreturn2: <... completed>
+key|val
+---+---------
+ 1|doreturn1
+(1 row)
+
+step select2: SELECT * FROM ints;
+key|val
+---+---------
+ 1|doreturn1
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: doreturn1 doreturn2 a1 select2 c2
+step doreturn1: INSERT INTO ints(key, val) VALUES(1, 'doreturn1') ON CONFLICT (key) DO RETURN RETURNING *;
+key|val
+---+---------
+ 1|doreturn1
+(1 row)
+
+step doreturn2: INSERT INTO ints(key, val) VALUES(1, 'doreturn2') ON CONFLICT (key) DO RETURN RETURNING *; <waiting ...>
+step a1: ABORT;
+step doreturn2: <... completed>
+key|val
+---+---------
+ 1|doreturn2
+(1 row)
+
+step select2: SELECT * FROM ints;
+key|val
+---+---------
+ 1|doreturn2
+(1 row)
+
+step c2: COMMIT;
diff --git a/src/test/isolation/expected/partition-key-update-3.out b/src/test/isolation/expected/partition-key-update-3.out
index b5872b8b45..1e00307289 100644
--- a/src/test/isolation/expected/partition-key-update-3.out
+++ b/src/test/isolation/expected/partition-key-update-3.out
@@ -153,3 +153,209 @@ a|b
2|initial tuple -> moved by session-1
(2 rows)
+
+starting permutation: s2beginrr s3beginrr s1u s2doreturn s1c s2c s3doreturn s3c s2select
+step s2beginrr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s3beginrr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s1u: UPDATE foo SET a=2, b=b || ' -> moved by session-1' WHERE a=1;
+step s2doreturn: INSERT INTO foo VALUES(1, 'session-2 doreturn') ON CONFLICT (a) DO RETURN RETURNING *; <waiting ...>
+step s1c: COMMIT;
+step s2doreturn: <... completed>
+a|b
+-+------------------
+1|session-2 doreturn
+(1 row)
+
+step s2c: COMMIT;
+step s3doreturn: INSERT INTO foo VALUES(2, 'session-3 doreturn'), (2, 'session-3 doreturn2') ON CONFLICT (a) DO RETURN RETURNING *;
+a|b
+-+-----------------------------------
+2|initial tuple -> moved by session-1
+2|initial tuple -> moved by session-1
+(2 rows)
+
+step s3c: COMMIT;
+step s2select: SELECT * FROM foo ORDER BY a;
+a|b
+-+-----------------------------------
+1|session-2 doreturn
+2|initial tuple -> moved by session-1
+(2 rows)
+
+
+starting permutation: s2beginrr s3beginrr s1u s3doreturn s1c s3c s2doreturn s2c s2select
+step s2beginrr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s3beginrr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s1u: UPDATE foo SET a=2, b=b || ' -> moved by session-1' WHERE a=1;
+step s3doreturn: INSERT INTO foo VALUES(2, 'session-3 doreturn'), (2, 'session-3 doreturn2') ON CONFLICT (a) DO RETURN RETURNING *; <waiting ...>
+step s1c: COMMIT;
+step s3doreturn: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s3c: COMMIT;
+step s2doreturn: INSERT INTO foo VALUES(1, 'session-2 doreturn') ON CONFLICT (a) DO RETURN RETURNING *;
+a|b
+-+------------------
+1|session-2 doreturn
+(1 row)
+
+step s2c: COMMIT;
+step s2select: SELECT * FROM foo ORDER BY a;
+a|b
+-+-----------------------------------
+1|session-2 doreturn
+2|initial tuple -> moved by session-1
+(2 rows)
+
+
+starting permutation: s2beginrr s3beginrr s1u s2doreturn s3doreturn s1c s2c s3c s2select
+step s2beginrr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s3beginrr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s1u: UPDATE foo SET a=2, b=b || ' -> moved by session-1' WHERE a=1;
+step s2doreturn: INSERT INTO foo VALUES(1, 'session-2 doreturn') ON CONFLICT (a) DO RETURN RETURNING *; <waiting ...>
+step s3doreturn: INSERT INTO foo VALUES(2, 'session-3 doreturn'), (2, 'session-3 doreturn2') ON CONFLICT (a) DO RETURN RETURNING *; <waiting ...>
+step s1c: COMMIT;
+step s2doreturn: <... completed>
+a|b
+-+------------------
+1|session-2 doreturn
+(1 row)
+
+step s3doreturn: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s2c: COMMIT;
+step s3c: COMMIT;
+step s2select: SELECT * FROM foo ORDER BY a;
+a|b
+-+-----------------------------------
+1|session-2 doreturn
+2|initial tuple -> moved by session-1
+(2 rows)
+
+
+starting permutation: s2beginrr s3beginrr s1u s3doreturn s2doreturn s1c s3c s2c s2select
+step s2beginrr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s3beginrr: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s1u: UPDATE foo SET a=2, b=b || ' -> moved by session-1' WHERE a=1;
+step s3doreturn: INSERT INTO foo VALUES(2, 'session-3 doreturn'), (2, 'session-3 doreturn2') ON CONFLICT (a) DO RETURN RETURNING *; <waiting ...>
+step s2doreturn: INSERT INTO foo VALUES(1, 'session-2 doreturn') ON CONFLICT (a) DO RETURN RETURNING *; <waiting ...>
+step s1c: COMMIT;
+step s3doreturn: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s2doreturn: <... completed>
+a|b
+-+------------------
+1|session-2 doreturn
+(1 row)
+
+step s3c: COMMIT;
+step s2c: COMMIT;
+step s2select: SELECT * FROM foo ORDER BY a;
+a|b
+-+-----------------------------------
+1|session-2 doreturn
+2|initial tuple -> moved by session-1
+(2 rows)
+
+
+starting permutation: s2begins s3begins s1u s2doreturn s1c s2c s3doreturn s3c s2select
+step s2begins: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s3begins: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s1u: UPDATE foo SET a=2, b=b || ' -> moved by session-1' WHERE a=1;
+step s2doreturn: INSERT INTO foo VALUES(1, 'session-2 doreturn') ON CONFLICT (a) DO RETURN RETURNING *; <waiting ...>
+step s1c: COMMIT;
+step s2doreturn: <... completed>
+a|b
+-+------------------
+1|session-2 doreturn
+(1 row)
+
+step s2c: COMMIT;
+step s3doreturn: INSERT INTO foo VALUES(2, 'session-3 doreturn'), (2, 'session-3 doreturn2') ON CONFLICT (a) DO RETURN RETURNING *;
+a|b
+-+-----------------------------------
+2|initial tuple -> moved by session-1
+2|initial tuple -> moved by session-1
+(2 rows)
+
+step s3c: COMMIT;
+step s2select: SELECT * FROM foo ORDER BY a;
+a|b
+-+-----------------------------------
+1|session-2 doreturn
+2|initial tuple -> moved by session-1
+(2 rows)
+
+
+starting permutation: s2begins s3begins s1u s3doreturn s1c s3c s2doreturn s2c s2select
+step s2begins: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s3begins: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s1u: UPDATE foo SET a=2, b=b || ' -> moved by session-1' WHERE a=1;
+step s3doreturn: INSERT INTO foo VALUES(2, 'session-3 doreturn'), (2, 'session-3 doreturn2') ON CONFLICT (a) DO RETURN RETURNING *; <waiting ...>
+step s1c: COMMIT;
+step s3doreturn: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s3c: COMMIT;
+step s2doreturn: INSERT INTO foo VALUES(1, 'session-2 doreturn') ON CONFLICT (a) DO RETURN RETURNING *;
+a|b
+-+------------------
+1|session-2 doreturn
+(1 row)
+
+step s2c: COMMIT;
+step s2select: SELECT * FROM foo ORDER BY a;
+a|b
+-+-----------------------------------
+1|session-2 doreturn
+2|initial tuple -> moved by session-1
+(2 rows)
+
+
+starting permutation: s2begins s3begins s1u s2doreturn s3doreturn s1c s2c s3c s2select
+step s2begins: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s3begins: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s1u: UPDATE foo SET a=2, b=b || ' -> moved by session-1' WHERE a=1;
+step s2doreturn: INSERT INTO foo VALUES(1, 'session-2 doreturn') ON CONFLICT (a) DO RETURN RETURNING *; <waiting ...>
+step s3doreturn: INSERT INTO foo VALUES(2, 'session-3 doreturn'), (2, 'session-3 doreturn2') ON CONFLICT (a) DO RETURN RETURNING *; <waiting ...>
+step s1c: COMMIT;
+step s2doreturn: <... completed>
+a|b
+-+------------------
+1|session-2 doreturn
+(1 row)
+
+step s3doreturn: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s2c: COMMIT;
+step s3c: COMMIT;
+step s2select: SELECT * FROM foo ORDER BY a;
+a|b
+-+-----------------------------------
+1|session-2 doreturn
+2|initial tuple -> moved by session-1
+(2 rows)
+
+
+starting permutation: s2begins s3begins s1u s3doreturn s2doreturn s1c s3c s2c s2select
+step s2begins: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s3begins: BEGIN ISOLATION LEVEL SERIALIZABLE;
+step s1u: UPDATE foo SET a=2, b=b || ' -> moved by session-1' WHERE a=1;
+step s3doreturn: INSERT INTO foo VALUES(2, 'session-3 doreturn'), (2, 'session-3 doreturn2') ON CONFLICT (a) DO RETURN RETURNING *; <waiting ...>
+step s2doreturn: INSERT INTO foo VALUES(1, 'session-2 doreturn') ON CONFLICT (a) DO RETURN RETURNING *; <waiting ...>
+step s1c: COMMIT;
+step s3doreturn: <... completed>
+ERROR: could not serialize access due to concurrent update
+step s2doreturn: <... completed>
+a|b
+-+------------------
+1|session-2 doreturn
+(1 row)
+
+step s3c: COMMIT;
+step s2c: COMMIT;
+step s2select: SELECT * FROM foo ORDER BY a;
+a|b
+-+-----------------------------------
+1|session-2 doreturn
+2|initial tuple -> moved by session-1
+(2 rows)
+
diff --git a/src/test/isolation/specs/insert-conflict-do-nothing-2.spec b/src/test/isolation/specs/insert-conflict-do-nothing-2.spec
index 825b7d6490..1c84aefd99 100644
--- a/src/test/isolation/specs/insert-conflict-do-nothing-2.spec
+++ b/src/test/isolation/specs/insert-conflict-do-nothing-2.spec
@@ -15,6 +15,7 @@ session s1
step beginrr1 { BEGIN ISOLATION LEVEL REPEATABLE READ; }
step begins1 { BEGIN ISOLATION LEVEL SERIALIZABLE; }
step donothing1 { INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING; }
+step doreturn1 { INSERT INTO ints(key, val) VALUES(1, 'doreturn1') ON CONFLICT (key) DO RETURN RETURNING *; }
step c1 { COMMIT; }
step show { SELECT * FROM ints; }
@@ -22,6 +23,7 @@ session s2
step beginrr2 { BEGIN ISOLATION LEVEL REPEATABLE READ; }
step begins2 { BEGIN ISOLATION LEVEL SERIALIZABLE; }
step donothing2 { INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING; }
+step doreturn2 { INSERT INTO ints(key, val) VALUES(1, 'doreturn2'), (1, 'doreturn3') ON CONFLICT (key) DO RETURN RETURNING *; }
step c2 { COMMIT; }
permutation beginrr1 beginrr2 donothing1 c1 donothing2 c2 show
@@ -32,3 +34,12 @@ permutation begins1 begins2 donothing1 c1 donothing2 c2 show
permutation begins1 begins2 donothing2 c2 donothing1 c1 show
permutation begins1 begins2 donothing1 donothing2 c1 c2 show
permutation begins1 begins2 donothing2 donothing1 c2 c1 show
+
+permutation beginrr1 beginrr2 doreturn1 c1 doreturn2 c2 show
+permutation beginrr1 beginrr2 doreturn2 c2 doreturn1 c1 show
+permutation beginrr1 beginrr2 doreturn1 doreturn2 c1 c2 show
+permutation beginrr1 beginrr2 doreturn2 doreturn1 c2 c1 show
+permutation begins1 begins2 doreturn1 c1 doreturn2 c2 show
+permutation begins1 begins2 doreturn2 c2 doreturn1 c1 show
+permutation begins1 begins2 doreturn1 doreturn2 c1 c2 show
+permutation begins1 begins2 doreturn2 doreturn1 c2 c1 show
diff --git a/src/test/isolation/specs/insert-conflict-do-nothing.spec b/src/test/isolation/specs/insert-conflict-do-nothing.spec
index b0e6a37247..0efa04df21 100644
--- a/src/test/isolation/specs/insert-conflict-do-nothing.spec
+++ b/src/test/isolation/specs/insert-conflict-do-nothing.spec
@@ -22,6 +22,7 @@ setup
BEGIN ISOLATION LEVEL READ COMMITTED;
}
step donothing1 { INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING; }
+step doreturn1 { INSERT INTO ints(key, val) VALUES(1, 'doreturn1') ON CONFLICT (key) DO RETURN RETURNING *; }
step c1 { COMMIT; }
step a1 { ABORT; }
@@ -31,6 +32,7 @@ setup
BEGIN ISOLATION LEVEL READ COMMITTED;
}
step donothing2 { INSERT INTO ints(key, val) VALUES(1, 'donothing2') ON CONFLICT DO NOTHING; }
+step doreturn2 { INSERT INTO ints(key, val) VALUES(1, 'doreturn2') ON CONFLICT (key) DO RETURN RETURNING *; }
step select2 { SELECT * FROM ints; }
step c2 { COMMIT; }
@@ -38,3 +40,6 @@ step c2 { COMMIT; }
# should proceed with an insert or do nothing.
permutation donothing1 donothing2 c1 select2 c2
permutation donothing1 donothing2 a1 select2 c2
+
+permutation doreturn1 doreturn2 c1 select2 c2
+permutation doreturn1 doreturn2 a1 select2 c2
diff --git a/src/test/isolation/specs/partition-key-update-3.spec b/src/test/isolation/specs/partition-key-update-3.spec
index d2883e34a5..5d836d4e54 100644
--- a/src/test/isolation/specs/partition-key-update-3.spec
+++ b/src/test/isolation/specs/partition-key-update-3.spec
@@ -25,6 +25,7 @@ session s2
step s2beginrr { BEGIN ISOLATION LEVEL REPEATABLE READ; }
step s2begins { BEGIN ISOLATION LEVEL SERIALIZABLE; }
step s2donothing { INSERT INTO foo VALUES(1, 'session-2 donothing') ON CONFLICT DO NOTHING; }
+step s2doreturn { INSERT INTO foo VALUES(1, 'session-2 doreturn') ON CONFLICT (a) DO RETURN RETURNING *; }
step s2c { COMMIT; }
step s2select { SELECT * FROM foo ORDER BY a; }
@@ -32,6 +33,7 @@ session s3
step s3beginrr { BEGIN ISOLATION LEVEL REPEATABLE READ; }
step s3begins { BEGIN ISOLATION LEVEL SERIALIZABLE; }
step s3donothing { INSERT INTO foo VALUES(2, 'session-3 donothing'), (2, 'session-3 donothing2') ON CONFLICT DO NOTHING; }
+step s3doreturn { INSERT INTO foo VALUES(2, 'session-3 doreturn'), (2, 'session-3 doreturn2') ON CONFLICT (a) DO RETURN RETURNING *; }
step s3c { COMMIT; }
permutation s2beginrr s3beginrr s1u s2donothing s1c s2c s3donothing s3c s2select
@@ -42,3 +44,12 @@ permutation s2begins s3begins s1u s2donothing s1c s2c s3donothing s3c s2select
permutation s2begins s3begins s1u s3donothing s1c s3c s2donothing s2c s2select
permutation s2begins s3begins s1u s2donothing s3donothing s1c s2c s3c s2select
permutation s2begins s3begins s1u s3donothing s2donothing s1c s3c s2c s2select
+
+permutation s2beginrr s3beginrr s1u s2doreturn s1c s2c s3doreturn s3c s2select
+permutation s2beginrr s3beginrr s1u s3doreturn s1c s3c s2doreturn s2c s2select
+permutation s2beginrr s3beginrr s1u s2doreturn s3doreturn s1c s2c s3c s2select
+permutation s2beginrr s3beginrr s1u s3doreturn s2doreturn s1c s3c s2c s2select
+permutation s2begins s3begins s1u s2doreturn s1c s2c s3doreturn s3c s2select
+permutation s2begins s3begins s1u s3doreturn s1c s3c s2doreturn s2c s2select
+permutation s2begins s3begins s1u s2doreturn s3doreturn s1c s2c s3c s2select
+permutation s2begins s3begins s1u s3doreturn s2doreturn s1c s3c s2c s2select
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 66d8633e3e..2dda4baf6d 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -864,3 +864,28 @@ insert into parted_conflict values(0, 'cero', 1)
NOTICE: a = 0, b = cero, c = 2
drop table parted_conflict;
drop function parted_conflict_update_func();
+-- returning
+create table returning_conflicts (key int primary key, derived int generated always as (key + 1) stored);
+insert into returning_conflicts (key) values (1);
+-- Fails (no unique index inference specification, required for do update variant):
+insert into returning_conflicts (key) values (1) on conflict do return;
+ERROR: ON CONFLICT DO RETURN requires inference specification or constraint name
+LINE 1: insert into returning_conflicts (key) values (1) on conflict...
+ ^
+HINT: For example, ON CONFLICT (column_name).
+-- same as DO NOTHING
+insert into returning_conflicts (key) values (1) on conflict (key) do return;
+-- does not return conflicting rows
+insert into returning_conflicts (key) values (1) on conflict do nothing returning *;
+ key | derived
+-----+---------
+(0 rows)
+
+-- returns conflicting rows
+insert into returning_conflicts (key) values (1) on conflict (key) do return returning *;
+ key | derived
+-----+---------
+ 1 | 2
+(1 row)
+
+drop table returning_conflicts;
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 23d5778b82..51cc434231 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -580,3 +580,22 @@ insert into parted_conflict values(0, 'cero', 1)
drop table parted_conflict;
drop function parted_conflict_update_func();
+
+-- returning
+
+create table returning_conflicts (key int primary key, derived int generated always as (key + 1) stored);
+insert into returning_conflicts (key) values (1);
+
+-- Fails (no unique index inference specification, required for do update variant):
+insert into returning_conflicts (key) values (1) on conflict do return;
+
+-- same as DO NOTHING
+insert into returning_conflicts (key) values (1) on conflict (key) do return;
+
+-- does not return conflicting rows
+insert into returning_conflicts (key) values (1) on conflict do nothing returning *;
+
+-- returns conflicting rows
+insert into returning_conflicts (key) values (1) on conflict (key) do return returning *;
+
+drop table returning_conflicts;
--
2.37.3
Re: Add ON CONFLICT DO RETURN clause
On Sun, Sep 25, 2022 at 8:55 AM Wolfgang Walther
<walther@technowledgy.de> wrote:
The attached patch adds a DO RETURN clause to be able to do this:
INSERT INTO x (id) VALUES (1)
ON CONFLICT DO RETURN
RETURNING created_at;Much simpler. This will either insert or do nothing - but in both cases
return a row.
How can you tell which it was, though?
I don't see why this statement should ever perform steps for any row
that are equivalent to DO NOTHING processing -- it should at least
lock each and every affected row, if only to conclusively determine
that there really must be a conflict.
In general ON CONFLICT DO UPDATE allows the user to add a WHERE clause
to back out of updating a row based on an arbitrary predicate. DO
NOTHING has no such WHERE clause. So DO NOTHING quite literally does
nothing for any rows that had conflicts, unlike DO UPDATE, which will
at the very least lock the row (with or without an explicit WHERE
clause).
The READ COMMITTED behavior for DO NOTHING is a bit iffy, even
compared to DO UPDATE, but the advantages in bulk loading scenarios
can be decisive. Or at least they were before we had MERGE.
--
Peter Geoghegan
Re: Add ON CONFLICT DO RETURN clause
Peter Geoghegan:
On Sun, Sep 25, 2022 at 8:55 AM Wolfgang Walther
<walther@technowledgy.de> wrote:The attached patch adds a DO RETURN clause to be able to do this:
INSERT INTO x (id) VALUES (1)
ON CONFLICT DO RETURN
RETURNING created_at;Much simpler. This will either insert or do nothing - but in both cases
return a row.How can you tell which it was, though?
I guess I can't reliably. But isn't that the same in the ON UPDATE case?
In the use cases I had so far, I didn't need to know.
I don't see why this statement should ever perform steps for any row
that are equivalent to DO NOTHING processing -- it should at least
lock each and every affected row, if only to conclusively determine
that there really must be a conflict.In general ON CONFLICT DO UPDATE allows the user to add a WHERE clause
to back out of updating a row based on an arbitrary predicate. DO
NOTHING has no such WHERE clause. So DO NOTHING quite literally does
nothing for any rows that had conflicts, unlike DO UPDATE, which will
at the very least lock the row (with or without an explicit WHERE
clause).The READ COMMITTED behavior for DO NOTHING is a bit iffy, even
compared to DO UPDATE, but the advantages in bulk loading scenarios
can be decisive. Or at least they were before we had MERGE.
Agreed - it needs to lock the row. I don't think I fully understood what
"nothing" in DO NOTHING extended to.
I guess I want DO RETURN to behave more like a DO SELECT, so with the
same semantics as selecting the row?
Best
Wolfgang
Re: Add ON CONFLICT DO RETURN clause
Wolfgang Walther <walther@technowledgy.de> writes:
Peter Geoghegan:
On Sun, Sep 25, 2022 at 8:55 AM Wolfgang Walther
<walther@technowledgy.de> wrote:The attached patch adds a DO RETURN clause to be able to do this:
INSERT INTO x (id) VALUES (1)
ON CONFLICT DO RETURN
RETURNING created_at;Much simpler. This will either insert or do nothing - but in both cases
return a row.How can you tell which it was, though?
I guess I can't reliably. But isn't that the same in the ON UPDATE case?
In the use cases I had so far, I didn't need to know.
I don't see why this statement should ever perform steps for any row
that are equivalent to DO NOTHING processing -- it should at least
lock each and every affected row, if only to conclusively determine
that there really must be a conflict.
In general ON CONFLICT DO UPDATE allows the user to add a WHERE clause
to back out of updating a row based on an arbitrary predicate. DO
NOTHING has no such WHERE clause. So DO NOTHING quite literally does
nothing for any rows that had conflicts, unlike DO UPDATE, which will
at the very least lock the row (with or without an explicit WHERE
clause).
The READ COMMITTED behavior for DO NOTHING is a bit iffy, even
compared to DO UPDATE, but the advantages in bulk loading scenarios
can be decisive. Or at least they were before we had MERGE.Agreed - it needs to lock the row. I don't think I fully understood what
"nothing" in DO NOTHING extended to.I guess I want DO RETURN to behave more like a DO SELECT, so with the
same semantics as selecting the row?
There was a patch for ON CONFLICT DO SELECT submitted a while back, but
the author abandoned it. I hven't read either that patch that or yours,
so I don't know how they compare, but you might want to have a look at
it:
https://commitfest.postgresql.org/16/1241/
Best
Wolfgang
- ilmari