8000 Support INSERT ... ON CONFLICT IGNORE · petergeoghegan/postgres@a529289 · GitHub
[go: up one dir, main page]
Skip to content

Commit a529289

Browse files
petergeogheganpetergeoghegan
committed
Support INSERT ... ON CONFLICT IGNORE
This non-standard INSERT clause allows DML statement authors to specify that in the event of each of any of the tuples being inserted duplicating an existing tuple in terms of a value or set of values constrained by a unique index, an alternative IGNORE path may be taken (the tuple slot proposed for insertion is skipped without raising an error). The implementation loops until either an insert occurs, or a conclusively committed conflicting tuple is determined to exist. This is implemented using a new infrastructure called "speculative insertion". (The approach to "Value locking" presenting here follows design postgres#2, as described on the value locking Postgres Wiki page). Users optionally specify a single unique index to take the alternative path on, which is inferred from a set of user-supplied column names (or expressions). Speculative (heap) insertions are WAL-logged in two steps: One record relates to an initial intent to insert, while a second minimal record simply confirms that that attempt was ultimately successful (i.e. no conflicts where detected when inserting into constraint-related indexes). In this revision, logical decoding does not rely on the presence of this second record to affirm that a speculative insertion succeeded, though; it relies on the *absence* on an (internal) "super deletion" record.
1 parent 410cbfd commit a529289

Some content is hidden

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

77 files changed

+3114
-235
lines changed

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2264,6 +2264,9 @@ JumbleQuery(pgssJumbleState *jstate, Query *query)
22642264
JumbleRangeTable(jstate, query->rtable);
22652265
JumbleExpr(jstate, (Node *) query->jointree);
22662266
JumbleExpr(jstate, (Node *) query->targetList);
2267+
APP_JUMB(query->specClause);
2268+
JumbleExpr(jstate, (Node *) query->arbiterElems);
2269+
JumbleExpr(jstate, query->arbiterWhere);
22672270
JumbleExpr(jstate, (Node *) query->returningList);
22682271
JumbleExpr(jstate, (Node *) query->groupClause);
22692272
JumbleExpr(jstate, query->havingQual);
@@ -2631,6 +2634,16 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
26312634
APP_JUMB(ce->cursor_param);
26322635
}
26332636
break;
2637+
case T_InferenceElem:
2638+
{
2639+
InferenceElem *ie = (InferenceElem *) node;
2640+
2641+
APP_JUMB(ie->infercollid);
2642+
APP_JUMB(ie->inferopfamily);
2643+
APP_JUMB(ie->inferopcinputtype);
2644+
JumbleExpr(jstate, ie->expr);
2645+
}
2646+
break;
26342647
case T_TargetEntry:
26352648
{
26362649
TargetEntry *tle = (TargetEntry *) node;

‎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 ignore,
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 (ignore)
896+
appendStringInfoString(buf, " ON CONFLICT IGNORE");
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: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2327,6 +2327,9 @@ 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 IGNORE; -- works
2331+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) IGNORE; -- unsupported
2332+
ERROR: postgres_fdw does not support ON CONFLICT unique index inference
23302333
INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive
23312334
ERROR: new row for relation "T 1" violates check constraint "c2positive"
23322335
DETAIL: Failing row contains (1111, -2, null, null, null, null, ft1 , null).

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 11 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 ignore = false;
11741175

11751176
initStringInfo(&sql);
11761177

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

1226+
if (root->parse->arbiterElems)
1227+
ereport(ERROR,
1228+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1229+
errmsg("postgres_fdw does not support ON CONFLICT unique index inference")));
1230+
else if (plan->spec == SPEC_IGNORE)
1231+
ignore = true;
1232+
else if (plan->spec != SPEC_NONE)
1233+
elog(ERROR, "unexpected speculative specification: %d", (int) plan->spec);
1234+
12251235
/*
12261236
* Construct the SQL command string.
12271237
*/
12281238
switch (operation)
12291239
{
12301240
case CMD_INSERT:
12311241
deparseInsertSql(&sql, root, resultRelation, rel,
1232-
targetAttrs, returningList,
1242+
targetAttrs, ignore, returningList,
12331243
&retrieved_attrs);
12341244
break;
12351245
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 ignore, List *returningList,
6464
List **retrieved_attrs);
6565
extern void deparseUpdateSql(StringInfo buf, PlannerInfo *root,
6666
Inde 57AE x rtindex, Relation rel,

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -372,6 +372,8 @@ 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 IGNORE; -- works
376+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) IGNORE; -- unsupported
375377
INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive
376378
UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive
377379

‎doc/src/sgml/ddl.sgml

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2442,6 +2442,10 @@ VALUES ('Albany', NULL, NULL, 'NY');
24422442
All check constraints and not-null constraints on a parent table are
24432443
automatically inherited by its children. Other types of constraints
24442444
(unique, primary key, and foreign key constraints) are not inherited.
2445+
Therefore, <command>INSERT</command> with <literal>ON CONFLICT</>
2446+
unique index inference considers only unique constraints/indexes
2447+
directly associated with the table inserted into (which can be an
2448+
inheritance parent or child).
24452449
</para>
24462450

24472451
<para>

‎doc/src/sgml/fdwhandler.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1014,6 +1014,13 @@ GetForeignServerByName(const char *name, bool missing_ok);
10141014
source provides.
10151015
</para>
10161016

1017+
<para>
1018+
<command>INSERT</> with an <literal>ON CONFLICT</> clause is not
1019+
supported with a unique index inference specification, since a
1020+
conflict arbitrating unique index cannot meaningfully be inferred
1021+
on a foreign table.
1022+
</para>
1023+
10171024
</sect1>
10181025

10191026
</chapter>

‎doc/src/sgml/keywords.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -853,6 +853,13 @@
853853
<entry></entry>
854854
<entry></entry>
855855
</row>
856+
<row>
857+
<entry><token>CONFLICT</token></entry>
858+
<entry>non-reserved</entry>
859+
<entry></entry>
860+
<entry></entry>
861+
<entry></entry>
862+
</row>
856863
<row>
857864
<entry><token>CONNECT</token></entry>
858865
<entry></entry>

‎doc/src/sgml/postgres-fdw.sgml

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -68,6 +68,12 @@
6868
in your user mapping must have privileges to do these things.)
6969
</para>
7070

71+
<para>
72+
<filename>postgres_fdw</> supports <command>INSERT</command>
73+
statements with an <literal>ON CONFLICT IGNORE</> clause, provided a
74+
unique index inference specification is omitted.
75+
</para>
76+
7177
<para>
7278
It is generally recommended that the columns of a foreign table be declared
7379
with exactly the same data types, and collations if applicable, as the

0 commit comments

Comments
 (0)
0