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

Commit d22a070

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 record affirms that that attempt was ultimately successful (i.e. no conflicts where detected when inserting into constraint-related indexes). Logical decoding decodes the second record that affirms the insert, reporting it as a regular insert change.
1 parent b4eb2d1 commit d22a070

Some content is hidden

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

89 files changed

+2838
-200
lines changed

‎contrib/pageinspect/heapfuncs.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -163,7 +163,10 @@ heap_page_items(PG_FUNCTION_ARGS)
163163
values[4] = UInt32GetDatum(HeapTupleHeaderGetRawXmin(tuphdr));
164164
values[5] = UInt32GetDatum(HeapTupleHeaderGetRawXmax(tuphdr));
165165
values[6] = UInt32GetDatum(HeapTupleHeaderGetRawCommandId(tuphdr)); /* shared with xvac */
166-
values[7] = PointerGetDatum(&tuphdr->t_ctid);
166+
if (!HeapTupleHeaderIsSpeculative(tuphdr))
167+
values[7] = PointerGetDatum(&tuphdr->t_tidstate.t_ctid);
168+
else
169+
values[7] = PointerGetDatum(&tuphdr->t_tidstate.t_token);
167170
values[8] = UInt32GetDatum(tuphdr->t_infomask2);
168171
values[9] = UInt32GetDatum(tuphdr->t_infomask);
169172
values[10] = UInt8GetDatum(tuphdr->t_hoff);

‎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: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2327,6 +2327,10 @@ 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: relation "ft1" is not an ordinary table
2333+
HINT: Only ordinary tables are accepted as targets when a unique index is inferred for ON CONFLICT.
23302334
INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive
23312335
ERROR: new row for relation "T 1" violates check constraint "c2positive"
23322336
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
Index 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

‎doc/src/sgml/ref/create_rule.sgml

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -136,7 +136,12 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS
136136
<para>
137137
The event is one of <literal>SELECT</literal>,
138138
<literal>INSERT</literal>, <literal>UPDATE</literal>, or
139-
<literal>DELETE</literal>.
139+
<literal>DELETE</literal>. Note that an
140+
<command>INSERT</command> containing an <literal>ON CONFLICT
141+
IGNORE</literal> clause cannot be used on tables that have
142+
either <literal>INSERT</literal> or <literal>UPDATE</literal>
143+
rules. Consider using an updatable view instead, which are
144+
supported with <literal>ON CONFLICT IGNORE</literal>.
140145
</para>
141146
</listitem>
142147
</varlistentry>

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -717,7 +717,10 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | 427E TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
717717
<literal>EXCLUDE</>, and
718718
<literal>REFERENCES</> (foreign key) constraints accept this
719719
clause. <literal>NOT NULL</> and <literal>CHECK</> constraints are not
720-
deferrable.
720+
deferrable. Note that constraints that were created with this
721+
clause cannot be used as arbiters of whether or not to take the
722+
alternative path with an <command>INSERT</command> statement
723+
that includes an <literal>ON CONFLICT</> clause.
721724
</para>
722725
</listitem>
723726
</varlistentry>

‎doc/src/sgml/ref/create_view.sgml

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -286,8 +286,9 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
286286
<para>
287287
Simple views are automatically updatable: the system will allow
288288
<command>INSERT</>, <command>UPDATE</> and <command>DELETE</> statements
289-
to be used on the view in the same way as on a regular table. A view is
290-
automatically updatable if it satisfies all of the following conditions:
289+
to be used on the view in the same way as on a regular table (aside from
290+
the limitations on ON CONFLICT noted below). A view is automatically
291+
updatable if it satisfies all of the following conditions:
291292

292293
<itemizedlist>
293294
<listitem>
@@ -383,6 +384,12 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
383384
not need any permissions on the underlying base relations (see
384385
<xref linkend="rules-privileges">).
385386
</para>
387+
<para>
388+
<command>INSERT</command> with an <literal>ON CONFLICT IGNORE</>
389+
clause is supported on updatable views (if an inference
390+
specification is provided, it must infer a unique index on the
391+
underlying base relation).
392+
</para>
386393
</refsect2>
387394
</refsect1>
388395

0 commit comments

Comments
 (0)
0