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
>From 83a0031ed2ded46cbf6fd130bd76680267db7a5e Mon Sep 17 00:00:00 2001
From: Wolfgang Walther <walt...@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
