On 22.08.2020 10:16, Konstantin Knizhnik wrote:
Hi hackers,
I am sorry for the question which may be already discussed multiple
times.
But I have not found answer for it neither in internet neither in
pgsql-hackers archieve.
UPSERT (INSERT ... IN CONFLICT...) clause was added to the Postgres a
long time ago.
As far as I remember there was long discussions about its syntax and
functionality.
But today I found that there is still no way to perform one of the
most frequently needed operation:
locate record by key and return its autogenerated ID or insert new
record if key is absent.
Something like this:
create table jsonb_schemas(id serial, schema bytea primary key);
create index on jsonb_schemas(id);
insert into jsonb_schemas (schema) values (?) on conflict(schema) do
nothing returning id;
But it doesn't work because in case of conflict no value is returned.
It is possible to do something like this:
with ins as (insert into jsonb_schemas (schema) values (obj_schema)
on conflict(schema) do nothing returning id) select coalesce((select
id from ins),(select id from jsonb_schemas where schema=obj_schema));
but it requires extra lookup.
Or perform update:
insert into jsonb_schemas (schema) values (?) on conflict(schema) do
update set schema=excluded.schema returning id;
But it is even worse because we have to perform useless update and
produce new version.
May be I missing something, but according to stackoverflow:
https://stackoverflow.com/questions/34708509/how-to-use-returning-with-on-conflict-in-postgresql
there is no better solution.
I wonder how it can happen that such popular use case ia not covered
by Postgresql UPSERT?
Are there some principle problems with it?
Why it is not possible to add one more on-conflict action: SELECT,
making it possible to return data when key is found?
Thanks in advance,
Konstantin
I'm sorry for been intrusive.
But can somebody familiar with Postgres upsert mechanism explain me why
current implementation doesn't support very popular use case:
locate record by some unique key and and return its primary
(autogenerated) key if found otherwise insert new tuple.
I have explained the possible workarounds of the problem above.
But all of them looks awful or inefficient.
What I am suggesting is just add ON CONFLICT DO SELECT clause:
insert into jsonb_schemas (schema) values ('one') on conflict(schema) do
select returning id;
I attached small patch with prototype implementation of this construction.
It seems to be very trivial. What's wring with it?
Are there some fundamental problems which I do not understand?
Below is small illustration of how this patch is working:
postgres=# create table jsonb_schemas(id serial, schema bytea primary key);
CREATE TABLE
postgres=# create index on jsonb_schemas(id);
CREATE INDEX
postgres=# insert into jsonb_schemas (schema) values ('some') on
conflict(schema) do nothing returning id;
id
----
1
(1 row)
INSERT 0 1
postgres=# insert into jsonb_schemas (schema) values ('some') on
conflict(schema) do nothing returning id;
id
----
(0 rows)
INSERT 0 0
postgres=# insert into jsonb_schemas (schema) values ('some') on
conflict(schema) do select returning id;
id
----
1
(1 row)
INSERT 0 1
Thanks in advance,
Konstantin
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index c98c9b5..8a22b8c 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3755,8 +3755,10 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
if (node->onConflictAction != ONCONFLICT_NONE)
{
ExplainPropertyText("Conflict Resolution",
- node->onConflictAction == ONCONFLICT_NOTHING ?
- "NOTHING" : "UPDATE",
+ node->onConflictAction == ONCONFLICT_NOTHING
+ ? "NOTHING"
+ : node->onConflictAction == ONCONFLICT_SELECT
+ ? "SELECT" : "UPDATE",
es);
/*
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 20a4c47..8e64061 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -545,6 +545,8 @@ ExecInsert(ModifyTableState *mtstate,
else
goto vlock;
}
+ /* committed conflict tuple found */
+
else
{
/*
@@ -558,11 +560,26 @@ ExecInsert(ModifyTableState *mtstate,
* type. As there's no conflicting usage of
* ExecGetReturningSlot() in the DO NOTHING case...
*/
- Assert(onconflict == ONCONFLICT_NOTHING);
+ Assert(onconflict == ONCONFLICT_NOTHING || onconflict == ONCONFLICT_SELECT);
ExecCheckTIDVisible(estate, resultRelInfo, &conflictTid,
ExecGetReturningSlot(estate, resultRelInfo));
InstrCountTuples2(&mtstate->ps, 1);
- return NULL;
+ if (onconflict == ONCONFLICT_SELECT && resultRelInfo->ri_projectReturning)
+ {
+ TupleTableSlot *existing = resultRelInfo->ri_onConflict->oc_Existing;
+ TM_FailureData tmfd;
+ TM_Result test = table_tuple_lock(resultRelInfo->ri_RelationDesc, &conflictTid,
+ estate->es_snapshot,
+ existing, estate->es_output_cid,
+ LockTupleShare, LockWaitBlock, 0,
+ &tmfd);
+ if (test == TM_Ok)
+ {
+ result = ExecProcessReturning(resultRelInfo, existing, planSlot);
+ }
+ ExecClearTuple(existing);
+ }
+ return result;
}
}
@@ -2542,7 +2559,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
* If needed, Initialize target list, projection and qual for ON CONFLICT
* DO UPDATE.
*/
- if (node->onConflictAction == ONCONFLICT_UPDATE)
+ if (node->onConflictAction == ONCONFLICT_UPDATE || node->onConflictAction == ONCONFLICT_SELECT)
{
ExprContext *econtext;
TupleDesc relationDesc;
@@ -2566,35 +2583,37 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
table_slot_create(resultRelInfo->ri_RelationDesc,
&mtstate->ps.state->es_tupleTable);
- /*
- * Create the tuple slot for the UPDATE SET projection. We want a slot
- * of the table's type here, because the slot will be used to insert
- * into the table, and for RETURNING processing - which may access
- * system attributes.
- */
- tupDesc = ExecTypeFromTL((List *) node->onConflictSet);
- resultRelInfo->ri_onConflict->oc_ProjSlot =
- ExecInitExtraTupleSlot(mtstate->ps.state, tupDesc,
- table_slot_callbacks(resultRelInfo->ri_RelationDesc));
-
- /* build UPDATE SET projection state */
- resultRelInfo->ri_onConflict->oc_ProjInfo =
- ExecBuildProjectionInfo(node->onConflictSet, econtext,
- resultRelInfo->ri_onConflict->oc_ProjSlot,
- &mtstate->ps,
- relationDesc);
-
- /* initialize state to evaluate the WHERE clause, if any */
- if (node->onConflictWhere)
+ if (node->onConflictAction == ONCONFLICT_UPDATE)
{
- ExprState *qualexpr;
+ /*
+ * Create the tuple slot for the UPDATE SET projection. We want a slot
+ * of the table's type here, because the slot will be used to insert
+ * into the table, and for RETURNING processing - which may access
+ * system attributes.
+ */
+ tupDesc = ExecTypeFromTL((List *) node->onConflictSet);
+ resultRelInfo->ri_onConflict->oc_ProjSlot =
+ ExecInitExtraTupleSlot(mtstate->ps.state, tupDesc,
+ table_slot_callbacks(resultRelInfo->ri_RelationDesc));
+
+ /* build UPDATE SET projection state */
+ resultRelInfo->ri_onConflict->oc_ProjInfo =
+ ExecBuildProjectionInfo(node->onConflictSet, econtext,
+ resultRelInfo->ri_onConflict->oc_ProjSlot,
+ &mtstate->ps,
+ relationDesc);
+
+ /* initialize state to evaluate the WHERE clause, if any */
+ if (node->onConflictWhere)
+ {
+ ExprState *qualexpr;
- qualexpr = ExecInitQual((List *) node->onConflictWhere,
+ qualexpr = ExecInitQual((List *) node->onConflictWhere,
&mtstate->ps);
- resultRelInfo->ri_onConflict->oc_WhereClause = qualexpr;
+ resultRelInfo->ri_onConflict->oc_WhereClause = qualexpr;
+ }
}
}
-
/*
* If we have any secondary relations in an UPDATE or DELETE, they need to
* be treated like non-locked relations in SELECT FOR UPDATE, ie, the
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index dbb47d4..1e815d1 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10874,6 +10874,16 @@ opt_on_conflict:
$$->whereClause = NULL;
$$->location = @1;
}
+ |
+ ON CONFLICT opt_conf_expr DO SELECT
+ {
+ $$ = makeNode(OnConflictClause);
+ $$->action = ONCONFLICT_SELECT;
+ $$->infer = $3;
+ $$->targetList = NIL;
+ $$->whereClause = NULL;
+ $$->location = @1;
+ }
| /*EMPTY*/
{
$$ = NULL;
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index fe777c3..e96f568 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3612,6 +3612,11 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
rt_entry_relation,
parsetree->resultRelation);
}
+ if (parsetree->onConflict &&
+ parsetree->onConflict->action == ONCONFLICT_SELECT)
+ {
+ parsetree->onConflict->onConflictSet =parsetree->targetList;
+ }
}
else if (event == CMD_UPDATE)
{
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 60dd80c..cd40985 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -6276,6 +6276,10 @@ get_insert_query_def(Query *query, deparse_context *context)
{
appendStringInfoString(buf, " DO NOTHING");
}
+ else if (confl->action == ONCONFLICT_SELECT)
+ {
+ appendStringInfoString(buf, " DO SELECT");
+ }
else
{
appendStringInfoString(buf, " DO UPDATE SET ");
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 381d84b..c23d3d2 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -823,7 +823,8 @@ typedef enum OnConflictAction
{
ONCONFLICT_NONE, /* No "ON CONFLICT" clause */
ONCONFLICT_NOTHING, /* ON CONFLICT ... DO NOTHING */
- ONCONFLICT_UPDATE /* ON CONFLICT ... DO UPDATE */
+ ONCONFLICT_UPDATE, /* ON CONFLICT ... DO UPDATE */
+ ONCONFLICT_SELECT /* ON CONFLICT ... DO SELECT */
} OnConflictAction;
/*
