Skip to content

Commit 168d580

Browse files
anarazelanarazel
committed
Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
The newly added ON CONFLICT clause allows to specify an alternative to raising a unique or exclusion constraint violation error when inserting. ON CONFLICT refers to constraints that can either be specified using a inference clause (by specifying the columns of a unique constraint) or by naming a unique or exclusion constraint. DO NOTHING avoids the constraint violation, without touching the pre-existing row. DO UPDATE SET ... [WHERE ...] updates the pre-existing tuple, and has access to both the tuple proposed for insertion and the existing tuple; the optional WHERE clause can be used to prevent an update from being executed. The UPDATE SET and WHERE clauses have access to the tuple proposed for insertion using the "magic" EXCLUDED alias, and to the pre-existing tuple using the table name or its alias. This feature is often referred to as upsert. This is implemented using a new infrastructure called "speculative insertion". It is an optimistic variant of regular insertion that first does a pre-check for existing tuples and then attempts an insert. If a violating tuple was inserted concurrently, the speculatively inserted tuple is deleted and a new attempt is made. If the pre-check finds a matching tuple the alternative DO NOTHING or DO UPDATE action is taken. If the insertion succeeds without detecting a conflict, the tuple is deemed inserted. To handle the possible ambiguity between the excluded alias and a table named excluded, and for convenience with long relation names, INSERT INTO now can alias its target table. Bumps catversion as stored rules change. Author: Peter Geoghegan, with significant contributions from Heikki Linnakangas and Andres Freund. Testing infrastructure by Jeff Janes. Reviewed-By: Heikki Linnakangas, Andres Freund, Robert Haas, Simon Riggs, Dean Rasheed, Stephen Frost and many others.
1 parent 2c8f483 commit 168d580

File tree

122 files changed

+6106
-435
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

122 files changed

+6106
-435
lines changed

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2264,6 +2264,7 @@ JumbleQuery(pgssJumbleState *jstate, Query *query)
22642264
JumbleRangeTable(jstate, query->rtable);
22652265
JumbleExpr(jstate, (Node *) query->jointree);
22662266
JumbleExpr(jstate, (Node *) query->targetList);
2267+
JumbleExpr(jstate, (Node *) query->onConflict);
22672268
JumbleExpr(jstate, (Node *) query->returningList);
22682269
JumbleExpr(jstate, (Node *) query->groupClause);
22692270
JumbleExpr(jstate, query->havingQual);
@@ -2631,6 +2632,16 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
26312632
APP_JUMB(ce->cursor_param);
26322633
}
26332634
break;
2635+
case T_InferenceElem:
2636+
{
2637+
InferenceElem *ie = (InferenceElem *) node;
2638+
2639+
APP_JUMB(ie->infercollid);
2640+
APP_JUMB(ie->inferopfamily);
2641+
APP_JUMB(ie->inferopcinputtype);
2642+
JumbleExpr(jstate, ie->expr);
2643+
}
2644+
break;
26342645
case T_TargetEntry:
26352646
{
26362647
TargetEntry *tle = (TargetEntry *) node;
@@ -2667,6 +2678,20 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
26672678
JumbleExpr(jstate, from->quals);
26682679
}
26692680
break;
2681+
case T_OnConflictExpr:
2682+
{
2683+
OnConflictExpr *conf = (OnConflictExpr *) node;
2684+
2685+
APP_JUMB(conf->action);
2686+
JumbleExpr(jstate, (Node *) conf->arbiterElems);
2687+
JumbleExpr(jstate, conf->arbiterWhere);
2688+
JumbleExpr(jstate, (Node *) conf->onConflictSet);
2689+
JumbleExpr(jstate, conf->onConflictWhere);
2690+
APP_JUMB(conf->constraint);
2691+
APP_JUMB(conf->exclRelIndex);
2692+
JumbleExpr(jstate, (Node *) conf->exclRelTlist);
2693+
}
2694+
break;
26702695
case T_List:
26712696
foreach(temp, (List *) node)
26722697
{

‎contrib/postgres_fdw/deparse.c

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -847,8 +847,8 @@ appendWhereClause(StringInfo buf,
847847
void
848848
deparseInsertSql(StringInfo buf, PlannerInfo *root,
849849
Index rtindex, Relation rel,
850-
List *targetAttrs, List *returningList,
851-
List **retrieved_attrs)
850+
List *targetAttrs, bool doNothing,
851+
List *returningList, List **retrieved_attrs)
852852
{
853853
AttrNumber pindex;
854854
bool first;
@@ -892,6 +892,9 @@ deparseInsertSql(StringInfo buf, PlannerInfo *root,
892892
else
893893
appendStringInfoString(buf, " DEFAULT VALUES");
894894

895+
if (doNothing)
896+
appendStringInfoString(buf, " ON CONFLICT DO NOTHING");
897+
895898
deparseReturningList(buf, root, rtindex, rel,
896899
rel->trigdesc && rel->trigdesc->trig_insert_after_row,
897900
returningList, retrieved_attrs);

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2327,6 +2327,11 @@ INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key
23272327
ERROR: duplicate key value violates unique constraint "t1_pkey"
23282328
DETAIL: Key ("C 1")=(11) already exists.
23292329
CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
2330+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT DO NOTHING; -- works
2331+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO NOTHING; -- unsupported
2332+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
2333+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO UPDATE SET c3 = 'ffg'; -- unsupported
2334+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
23302335
INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive
23312336
ERROR: new row for relation "T 1" violates check constraint "c2positive"
23322337
DETAIL: Failing row contains (1111, -2, null, null, null, null, ft1 , null).

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1171,6 +1171,7 @@ postgresPlanForeignModify(PlannerInfo *root,
11711171
List *targetAttrs = NIL;
11721172
List *returningList = NIL;
11731173
List *retrieved_attrs = NIL;
1174+
bool doNothing = false;
11741175

11751176
initStringInfo(&sql);
11761177

@@ -1222,14 +1223,26 @@ postgresPlanForeignModify(PlannerInfo *root,
12221223
if (plan->returningLists)
12231224
returningList = (List *) list_nth(plan->returningLists, subplan_index);
12241225

1226+
/*
1227+
* ON CONFLICT DO UPDATE and DO NOTHING case with inference specification
1228+
* should have already been rejected in the optimizer, as presently there
1229+
* is no way to recognize an arbiter index on a foreign table. Only DO
1230+
* NOTHING is supported without an inference specification.
1231+
*/
1232+
if (plan->onConflictAction == ONCONFLICT_NOTHING)
1233+
doNothing = true;
1234+
else if (plan->onConflictAction != ONCONFLICT_NONE)
1235+
elog(ERROR, "unexpected ON CONFLICT specification: %d",
1236+
(int) plan->onConflictAction);
1237+
12251238
/*
12261239
* Construct the SQL command string.
12271240
*/
12281241
switch (operation)
12291242
{
12301243
case CMD_INSERT:
12311244
deparseInsertSql(&sql, root, resultRelation, rel,
1232-
targetAttrs, returningList,
1245+
targetAttrs, doNothing, returningList,
12331246
&retrieved_attrs);
12341247
break;
12351248
case CMD_UPDATE:

‎contrib/postgres_fdw/postgres_fdw.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -60,7 +60,7 @@ extern void appendWhereClause(StringInfo buf,
6060
List **params);
6161
extern void deparseInsertSql(StringInfo buf, PlannerInfo *root,
6262
Index rtindex, Relation rel,
63-
List *targetAttrs, List *returningList,
63+
List *targetAttrs, bool doNothing, List *returningList,
6464
List **retrieved_attrs);
6565
extern void deparseUpdateSql(StringInfo buf, PlannerInfo *root,
6666
Index rtindex, Relation rel,

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -372,6 +372,9 @@ UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *;
372372
ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0);
373373

374374
INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key
375+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT DO NOTHING; -- works
376+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO NOTHING; -- unsupported
377+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO UPDATE SET c3 = 'ffg'; -- unsupported
375378
INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive
376379
UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive
377380

‎contrib/test_decoding/expected/ddl.out

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -148,6 +148,24 @@ SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'inc
148148
COMMIT
149149
(9 rows)
150150

151+
-- ON CONFLICT DO UPDATE support
152+
BEGIN;
153+
INSERT INTO replication_example(id, somedata, somenum) SELECT i, i, i FROM generate_series(-15, 15) i
154+
ON CONFLICT (id) DO UPDATE SET somenum = excluded.somenum + 1;
155+
COMMIT;
156+
/* display results, but hide most of the output */
157+
SELECT count(*), min(data), max(data)
158+
FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1')
159+
GROUP BY substring(data, 1, 40)
160+
ORDER BY 1,2;
161+
count | min | max
162+
-------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------
163+
1 | BEGIN | BEGIN
164+
1 | COMMIT | COMMIT
165+
15 | table public.replication_example: UPDATE: id[integer]:10 somedata[integer]:4 somenum[integer]:11 zaphod1[integer]:null zaphod2[integer]:null | table public.replication_example: UPDATE: id[integer]:9 somedata[integer]:3 somenum[integer]:10 zaphod1[integer]:null zaphod2[integer]:null
166+
16 | table public.replication_example: INSERT: id[integer]:0 somedata[integer]:0 somenum[integer]:0 zaphod1[integer]:null zaphod2[integer]:null | table public.replication_example: INSERT: id[integer]:-9 somedata[integer]:-9 somenum[integer]:-9 zaphod1[integer]:null zaphod2[integer]:null
167+
(4 rows)
168+
151169
-- hide changes bc of oid visible in full table rewrites
152170
CREATE TABLE tr_unique(id2 serial unique NOT NULL, data int);
153171
INSERT INTO tr_unique(data) VALUES(10);
@@ -196,6 +214,22 @@ ORDER BY 1,2;
196214
20467 | table public.tr_etoomuch: DELETE: id[integer]:1 | table public.tr_etoomuch: UPDATE: id[integer]:9999 data[integer]:-9999
197215
(3 rows)
198216

217+
-- check that a large, spooled, upsert works
218+
INSERT INTO tr_etoomuch (id, data)
219+
SELECT g.i, -g.i FROM generate_series(8000, 12000) g(i)
220+
ON CONFLICT(id) DO UPDATE SET data = EXCLUDED.data;
221+
SELECT substring(data, 1, 29), count(*)
222+
FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1')
223+
GROUP BY 1
224+
ORDER BY min(location - '0/0');
225+
substring | count
226+
-------------------------------+-------
227+
BEGIN | 1
228+
table public.tr_etoomuch: UPD | 2235
229+
table public.tr_etoomuch: INS | 1766
230+
COMMIT | 1
231+
(4 rows)
232+
199233
/*
200234
* check whether we decode subtransactions correctly in relation with each
201235
* other

‎contrib/test_decoding/expected/toast.out

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,10 @@ INSERT INTO xpto (toasted_col2) SELECT repeat(string_agg(to_char(g.i, 'FM0000'),
2323
-- update of existing column
2424
UPDATE xpto SET toasted_col1 = (SELECT string_agg(g.i::text, '') FROM generate_series(1, 2000) g(i)) WHERE id = 1;
2525
UPDATE xpto SET rand1 = 123.456 WHERE id = 1;
26+
-- updating external via INSERT ... ON CONFLICT DO UPDATE
27+
INSERT INTO xpto(id, toasted_col2) VALUES (2, 'toasted2-upsert')
28+
ON CONFLICT (id)
29+
DO UPDATE SET toasted_col2 = EXCLUDED.toasted_col2 || xpto.toasted_col2;
2630
DELETE FROM xpto WHERE id = 1;
2731
DROP TABLE IF EXISTS toasted_key;
2832
NOTICE: table "toasted_key" does not exist, skipping
@@ -64,6 +68,9 @@ SELECT substr(data, 1, 200) FROM pg_logical_slot_get_changes('regression_slot',
6468
table public.xpto: UPDATE: id[integer]:1 toasted_col1[text]:unchanged-toast-datum rand1[double precision]:123.456 toasted_col2[text]:unchanged-toast-datum rand2[double precision]:1578
6569
COMMIT
6670
BEGIN
71+
table public.xpto: UPDATE: id[integer]:2 toasted_col1[text]:null rand1[double precision]:3077 toasted_col2[text]:'toasted2-upsert00010002000300040005000600070008000900100011001200130014001500160017001
72+
COMMIT
73+
BEGIN
6774
table public.xpto: DELETE: id[integer]:1
6875
COMMIT
6976
BEGIN
@@ -283,7 +290,7 @@ SELECT substr(data, 1, 200) FROM pg_logical_slot_get_changes('regression_slot',
283290
table public.toasted_copy: INSERT: id[integer]:202 data[text]:'untoasted199'
284291
table public.toasted_copy: INSERT: id[integer]:203 data[text]:'untoasted200'
285292
COMMIT
286-
(232 rows)
293+
(235 rows)
287294

288295
SELECT pg_drop_replication_slot('regression_slot');
289296
pg_drop_replication_slot

‎contrib/test_decoding/sql/ddl.sql

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -84,6 +84,18 @@ COMMIT;
8484
-- show changes
8585
SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
8686

87+
-- ON CONFLICT DO UPDATE support
88+
BEGIN;
89+
INSERT INTO replication_example(id, somedata, somenum) SELECT i, i, i FROM generate_series(-15, 15) i
90+
ON CONFLICT (id) DO UPDATE SET somenum = excluded.somenum + 1;
91+
COMMIT;
92+
93+
/* display results, but hide most of the output */
94+
SELECT count(*), min(data), max(data)
95+
FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1')
96+
GROUP BY substring(data, 1, 40)
97+
ORDER BY 1,2;
98+
8799
-- hide changes bc of oid visible in full table rewrites
88100
CREATE TABLE tr_unique(id2 serial unique NOT NULL, data int);
89101
INSERT INTO tr_unique(data) VALUES(10);
@@ -114,6 +126,16 @@ FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids',
114126
GROUP BY substring(data, 1, 24)
115127
ORDER BY 1,2;
116128

129+
-- check that a large, spooled, upsert works
130+
INSERT INTO tr_etoomuch (id, data)
131+
SELECT g.i, -g.i FROM generate_series(8000, 12000) g(i)
132+
ON CONFLICT(id) DO UPDATE SET data = EXCLUDED.data;
133+
134+
SELECT substring(data, 1, 29), count(*)
135+
FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1')
136+
GROUP BY 1
137+
ORDER BY min(location - '0/0');
138+
117139
/*
118140
* check whether we decode subtransactions correctly in relation with each
119141
* other

‎contrib/test_decoding/sql/toast.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,11 @@ UPDATE xpto SET toasted_col1 = (SELECT string_agg(g.i::text, '') FROM generate_s
2525

2626
UPDATE xpto SET rand1 = 123.456 WHERE id = 1;
2727

28+
-- updating external via INSERT ... ON CONFLICT DO UPDATE
29+
INSERT INTO xpto(id, toasted_col2) VALUES (2, 'toasted2-upsert')
30+
ON CONFLICT (id)
31+
DO UPDATE SET toasted_col2 = EXCLUDED.toasted_col2 || xpto.toasted_col2;
32+
2833
DELETE FROM xpto WHERE id = 1;
2934

3035
DROP TABLE IF EXISTS toasted_key;

0 commit comments

Comments
 (0)