I have performed comparison of different ways of implementing UPSERT in
Postgres.
May be it will be interesting not only for me, so I share my results:
So first of all initialization step:
create table jsonb_schemas(id serial, schema bytea primary key);
create unique index on jsonb_schemas(id);
insert into jsonb_schemas (schema) values ('some') on
conflict(schema) do nothing returning id;
Then I test performance of getting ID of exitsed schema:
1. Use plpgsql script to avoid unneeded database modifications:
create function upsert(obj_schema bytea) returns integer as $$
declare
obj_id integer;
begin
select id from jsonb_schemas where schema=obj_schema into obj_id;
if obj_id is null then
insert into jsonb_schemas (schema) values (obj_schema) on
conflict(schema) do nothing returning id into obj_id;
if obj_id is null then
select id from jsonb_schemas where schema=obj_schema into obj_id;
end if;
end if;
return obj_id;
end;
$$ language plpgsql;
------------------------
upsert-plpgsql.sql:
select upsert('some');
------------------------
pgbench -n -T 100 -M prepared -f upsert-plpgsql.sql postgres
tps = 45092.241350
2. Use ON CONFLICT DO UPDATE:
upsert-update.sql:
insert into jsonb_schemas (schema) values ('some') on conflict(schema)
do update set schema='some' returning id;
------------------------
pgbench -n -T 100 -M prepared -f upsert-update.sql postgres
tps = 9222.344890
3. Use ON CONFLICT DO NOTHING + COALESCE:
upsert-coalecsce.sql:
with ins as (insert into jsonb_schemas (schema) values ('some') on
conflict(schema) do nothing returning id) select coalesce((select id
from ins),(select id from jsonb_schemas where schema='some'));
------------------------
pgbench -n -T 100 -M prepared -f upsert-coalesce.sql postgres
tps = 28929.353732
4. Use ON CONFLICT DO SELECT
upsert-select.sql:
insert into jsonb_schemas (schema) values ('some') on conflict(schema)
do select returning id;
------------------------
pgbench -n -T 100 -M prepared -f upsert-select.sql postgres
ps = 35788.362302
So, as you can see PLpgSQL version, which doesn't modify database if key
is found is signficantly faster than others.
And version which always do update is almost five times slower!
Proposed version of upsert with ON CONFLICT DO SELECT is slower than
PLpgSQL version (because it has to insert speculative tuple),
but faster than "user-unfriendly" version with COALESCE:
Upsert implementation
TPS
PLpgSQL
45092
ON CONFLICT DO UPDATE 9222
ON CONFLICT DO NOTHING 28929
ON CONFLICT DO SELECT 35788
Slightly modified version of my ON CONFLICT DO SELECT patch is attached
to this mail.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
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..07cab68 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,17 @@ 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;
+ if (table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc, &conflictTid, SnapshotAny, existing))
+ result = ExecProcessReturning(resultRelInfo, existing, planSlot);
+ }
+ return result;
}
}
@@ -2542,7 +2550,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 +2574,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;
/*
