From c5929a90c07e855e66714489eded24f185d3ab8c Mon Sep 17 00:00:00 2001 From: Peter Geoghegan Date: Tue, 3 Mar 2015 19:44:42 -0800 Subject: [PATCH 1/4] 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 #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). To support logical decoding, transaction commit reassembly now consolidates speculative insertion related changes. Using a "peek-ahead", it is determined if a speculative insertion went on to be super deleted. If that is not the case, then it is reported as an ordinary insertion to decoding plugins. --- contrib/pg_stat_statements/pg_stat_statements.c | 3 + contrib/postgres_fdw/deparse.c | 7 +- contrib/postgres_fdw/expected/postgres_fdw.out | 4 + contrib/postgres_fdw/postgres_fdw.c | 12 +- contrib/postgres_fdw/postgres_fdw.h | 2 +- contrib/postgres_fdw/sql/postgres_fdw.sql | 2 + doc/src/sgml/ddl.sgml | 18 ++ doc/src/sgml/fdwhandler.sgml | 5 + doc/src/sgml/keywords.sgml | 7 + doc/src/sgml/postgres-fdw.sgml | 6 + doc/src/sgml/ref/create_rule.sgml | 7 +- doc/src/sgml/ref/create_table.sgml | 5 +- doc/src/sgml/ref/create_view.sgml | 22 +- doc/src/sgml/ref/insert.sgml | 180 +++++++++++- doc/src/sgml/ref/set_constraints.sgml | 5 +- src/backend/access/heap/heapam.c | 110 +++++-- src/backend/access/nbtree/nbtinsert.c | 32 ++- src/backend/catalog/index.c | 59 +++- src/backend/catalog/indexing.c | 2 +- src/backend/commands/constraint.c | 7 +- src/backend/commands/copy.c | 7 +- src/backend/commands/explain.c | 9 + src/backend/executor/README | 73 +++++ src/backend/executor/execMain.c | 16 +- src/backend/executor/execUtils.c | 319 ++++++++++++++++++--- src/backend/executor/nodeLockRows.c | 9 +- src/backend/executor/nodeModifyTable.c | 156 +++++++++- src/backend/nodes/copyfuncs.c | 36 +++ src/backend/nodes/equalfuncs.c | 30 ++ src/backend/nodes/nodeFuncs.c | 31 ++ src/backend/nodes/outfuncs.c | 5 + src/backend/nodes/readfuncs.c | 3 + src/backend/optimizer/path/indxpath.c | 66 +++++ src/backend/optimizer/plan/createplan.c | 13 +- src/backend/optimizer/plan/planner.c | 2 + src/backend/optimizer/util/plancat.c | 219 ++++++++++++++ src/backend/parser/analyze.c | 29 +- src/backend/parser/gram.y | 48 +++- src/backend/parser/parse_clause.c | 160 +++++++++++ src/backend/replication/logical/decode.c | 11 +- src/backend/replication/logical/reorderbuffer.c | 83 +++++- src/backend/rewrite/rewriteHandler.c | 25 +- src/backend/storage/lmgr/lmgr.c | 88 ++++++ src/backend/utils/adt/lockfuncs.c | 1 + src/backend/utils/time/tqual.c | 45 ++- src/include/access/heapam.h | 3 +- src/include/access/heapam_xlog.h | 2 + src/include/access/htup_details.h | 29 ++ src/include/catalog/index.h | 2 + src/include/executor/executor.h | 19 +- src/include/nodes/execnodes.h | 8 + src/include/nodes/nodes.h | 14 + src/include/nodes/parsenodes.h | 35 ++- src/include/nodes/plannodes.h | 2 + src/include/optimizer/paths.h | 2 + src/include/optimizer/plancat.h | 2 + src/include/optimizer/planmain.h | 2 +- src/include/parser/kwlist.h | 2 + src/include/parser/parse_clause.h | 2 + src/include/replication/reorderbuffer.h | 11 +- src/include/storage/lmgr.h | 5 + src/include/storage/lock.h | 10 + src/include/utils/snapshot.h | 11 + .../isolation/expected/insert-conflict-ignore.out | 23 ++ src/test/isolation/isolation_schedule | 1 + .../isolation/specs/insert-conflict-ignore.spec | 41 +++ src/test/regress/expected/insert_conflict.out | 55 ++++ src/test/regress/expected/rules.out | 9 + src/test/regress/expected/updatable_views.out | 4 + src/test/regress/parallel_schedule | 1 + src/test/regress/serial_schedule | 1 + src/test/regress/sql/insert_conflict.sql | 65 +++++ src/test/regress/sql/rules.sql | 6 + src/test/regress/sql/updatable_views.sql | 2 + 74 files changed, 2215 insertions(+), 133 deletions(-) create mode 100644 src/test/isolation/expected/insert-conflict-ignore.out create mode 100644 src/test/isolation/specs/insert-conflict-ignore.spec create mode 100644 src/test/regress/expected/insert_conflict.out create mode 100644 src/test/regress/sql/insert_conflict.sql diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 95616b3..46f5189 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -2198,6 +2198,9 @@ JumbleQuery(pgssJumbleState *jstate, Query *query) JumbleRangeTable(jstate, query->rtable); JumbleExpr(jstate, (Node *) query->jointree); JumbleExpr(jstate, (Node *) query->targetList); + APP_JUMB(query->specClause); + JumbleExpr(jstate, (Node *) query->arbiterExpr); + JumbleExpr(jstate, query->arbiterWhere); JumbleExpr(jstate, (Node *) query->returningList); JumbleExpr(jstate, (Node *) query->groupClause); JumbleExpr(jstate, query->havingQual); diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 59cb053..ca51586 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -847,8 +847,8 @@ appendWhereClause(StringInfo buf, void deparseInsertSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - List *targetAttrs, List *returningList, - List **retrieved_attrs) + List *targetAttrs, bool ignore, + List *returningList, List **retrieved_attrs) { AttrNumber pindex; bool first; @@ -892,6 +892,9 @@ deparseInsertSql(StringInfo buf, PlannerInfo *root, else appendStringInfoString(buf, " DEFAULT VALUES"); + if (ignore) + appendStringInfoString(buf, " ON CONFLICT IGNORE"); + deparseReturningList(buf, root, rtindex, rel, rel->trigdesc && rel->trigdesc->trig_insert_after_row, returningList, retrieved_attrs); diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 4207fb3..47bb1ca 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2327,6 +2327,10 @@ INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key ERROR: duplicate key value violates unique constraint "t1_pkey" DETAIL: Key ("C 1")=(11) already exists. 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) +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT IGNORE; -- works +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) IGNORE; -- unsupported +ERROR: relation "ft1" is not an ordinary table +HINT: Only ordinary tables are accepted as targets when a unique index is inferred for ON CONFLICT. INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive ERROR: new row for relation "T 1" violates check constraint "c2positive" DETAIL: Failing row contains (1111, -2, null, null, null, null, ft1 , null). diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 478e124..895274e 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -1171,6 +1171,7 @@ postgresPlanForeignModify(PlannerInfo *root, List *targetAttrs = NIL; List *returningList = NIL; List *retrieved_attrs = NIL; + bool ignore = false; initStringInfo(&sql); @@ -1222,6 +1223,15 @@ postgresPlanForeignModify(PlannerInfo *root, if (plan->returningLists) returningList = (List *) list_nth(plan->returningLists, subplan_index); + if (root->parse->arbiterExpr) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("postgres_fdw does not support ON CONFLICT unique index inference"))); + else if (plan->spec == SPEC_IGNORE) + ignore = true; + else if (plan->spec != SPEC_NONE) + elog(ERROR, "unexpected speculative specification: %d", (int) plan->spec); + /* * Construct the SQL command string. */ @@ -1229,7 +1239,7 @@ postgresPlanForeignModify(PlannerInfo *root, { case CMD_INSERT: deparseInsertSql(&sql, root, resultRelation, rel, - targetAttrs, returningList, + targetAttrs, ignore, returningList, &retrieved_attrs); break; case CMD_UPDATE: diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h index 950c6f7..3763a57 100644 --- a/contrib/postgres_fdw/postgres_fdw.h +++ b/contrib/postgres_fdw/postgres_fdw.h @@ -60,7 +60,7 @@ extern void appendWhereClause(StringInfo buf, List **params); extern void deparseInsertSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - List *targetAttrs, List *returningList, + List *targetAttrs, bool ignore, List *returningList, List **retrieved_attrs); extern void deparseUpdateSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 4a23457..aba373e 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -372,6 +372,8 @@ UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *; ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0); INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT IGNORE; -- works +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) IGNORE; -- unsupported INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 1c56f16..2b05e1d 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -2439,9 +2439,27 @@ VALUES ('Albany', NULL, NULL, 'NY'); + There is limited inheritance support for INSERT + commands with ON CONFLICT IGNORE clauses. Tables with + children are not generally accepted as targets. One notable + exception is that such tables are accepted as targets for + INSERT commands with ON CONFLICT + IGNORE clauses, provided a unique index inference clause was + omitted (which implies that there is no concern about + which unique index any would-be conflict might arise + from). However, tables that happen to be inheritance children are + accepted as targets for all variants of INSERT + with ON CONFLICT IGNORE. + + + All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited. + Therefore, INSERT with ON CONFLICT + unique index inference considers only unique constraints/indexes + directly associated with the child + table. diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml index c1daa4b..0853078 100644 --- a/doc/src/sgml/fdwhandler.sgml +++ b/doc/src/sgml/fdwhandler.sgml @@ -1014,6 +1014,11 @@ GetForeignServerByName(const char *name, bool missing_ok); source provides. + + INSERT with an ON CONFLICT clause is not + supported with a unique index inference specification. + + diff --git a/doc/src/sgml/keywords.sgml b/doc/src/sgml/keywords.sgml index b0dfd5f..ea58211 100644 --- a/doc/src/sgml/keywords.sgml +++ b/doc/src/sgml/keywords.sgml @@ -854,6 +854,13 @@ + CONFLICT + non-reserved + + + + + CONNECT reserved diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml index 43adb61..81d4441 100644 --- a/doc/src/sgml/postgres-fdw.sgml +++ b/doc/src/sgml/postgres-fdw.sgml @@ -69,6 +69,12 @@ + postgres_fdw supports INSERT + statements with an ON CONFLICT IGNORE clause, provided a + unique index inference specification is omitted. + + + It is generally recommended that the columns of a foreign table be declared with exactly the same data types, and collations if applicable, as the referenced columns of the remote table. Although postgres_fdw diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index 677766a..045f5d1 100644 --- a/doc/src/sgml/ref/create_rule.sgml +++ b/doc/src/sgml/ref/create_rule.sgml @@ -136,7 +136,12 @@ CREATE [ OR REPLACE ] RULE name AS The event is one of SELECT, INSERT, UPDATE, or - DELETE. + DELETE. Note that an + INSERT containing an ON CONFLICT + IGNORE clause cannot be used on tables that have + either INSERT or UPDATE + rules. Consider using an updatable view instead, which have + limited support for ON CONFLICT IGNORE. diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 324d593..7f30fb1 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -717,7 +717,10 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not - deferrable. + deferrable. Note that constraints that were created with this + clause cannot be used as arbiters of whether or not to take the + alternative path with an INSERT statement + that includes an ON CONFLICT clause. diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 5dadab1..b1b4b02 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -286,8 +286,9 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; Simple views are automatically updatable: the system will allow INSERT, UPDATE and DELETE statements - to be used on the view in the same way as on a regular table. A view is - automatically updatable if it satisfies all of the following conditions: + to be used on the view in the same way as on a regular table (aside from + the limitations on ON CONFLICT noted below). A view is automatically + updatable if it satisfies all of the following conditions: @@ -383,6 +384,23 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; not need any permissions on the underlying base relations (see ). + + INSERT with an ON CONFLICT clause + is only supported on updatable views under specific circumstances. + If a unique index inference specifictation is + provided, then updatable views are not supported. For example: + + + +-- Unsupported: +INSERT INTO my_updatable_view(key, val) VALUES(1, 'bar') ON CONFLICT (key) + IGNORE; + +-- Supported (note the omission of "key" column): +INSERT INTO my_updatable_view(key, val) VALUES(1, 'baz') ON CONFLICT + IGNORE; + + diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index a3cccb9..f9d1436 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -24,6 +24,7 @@ PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ ( column_name [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } + [ ON CONFLICT [ ( { column_name_index | ( expression_index ) } [, ...] [ WHERE index_condition ] ) ] IGNORE] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] @@ -59,6 +60,102 @@ INSERT INTO table_name [ ( + The optional ON CONFLICT clause specifies a path to + take as an alternative to raising a conflict related error. The + alternative path is considered individually for each row proposed + for insertion; it is taken (or not taken) once per row. + ON CONFLICT IGNORE simply avoids inserting any + individual row when it is determined that a conflict related error + would otherwise need to be raised. + + + + ON CONFLICT IGNORE optionally accepts a + unique index inference specification, which + consists of one or more column_name_index columns and/or + expression_index + expressions on columns, appearing between parenthesis. These are + used to infer a single unique index to limit pre-checking for + conflicts to (if no appropriate index is available, an error is + raised). A subset of the table to limit the check for conflicts to + can optionally also be specified using index_condition. Note that any + available unique index must only cover at least that subset in + order to be arbitrate taking the alternative path; it need not + match exactly, and so a non-partial unique index that otherwise + matches is applicable. Note that omitting the specification + indicates a total indifference to where any conflict could occur, + which isn't always appropriate. At times, it may be desirable for + ON CONFLICT IGNORE to not suppress + a conflict related error associated with an index where that isn't + explicitly anticipated. + + + + Columns and/or expressions appearing in a unique index inference + specification must match all the columns/expressions of some + existing unique index on table_name - there can be no + columns/expressions from the unique index that do not appear in the + inference specification, nor can there be any columns/expressions + appearing in the inference specification that do not appear in the + unique index definition. However, the order of the + columns/expressions in the index definition, or whether or not the + index definition specified NULLS FIRST or + NULLS LAST, or the internal sort order of each column + (whether DESC or ASC were specified) are + all irrelevant. Deferred unique constraints are not supported as + arbiters of whether an alternative ON CONFLICT path + should be taken. + + + + The definition of a conflict for the purposes of ON + CONFLICT is somewhat subtle, although the exact definition is + seldom of great interest. A conflict is either a unique violation + from a unique constraint (or unique index), or an exclusion + violation from an exclusion constraint. Only unique indexes can be + inferred with a unique index inference specification. In contrast + to the rules around certain other SQL clauses, like the + DISTINCT clause, the definition of a duplicate + (a conflict) is based on whatever unique indexes happen to be + defined on columns on the table. This means that if a user-defined + type has multiple sort orders, and the "equals" operator of any of + those available sort orders happens to be inconsistent (which goes + against an unenforced convention of + PostgreSQL), the exact behavior depends + on the choice of operator class when the unique index was created + initially, and not any other consideration such as the default + operator class for the type of each indexed column. If there are + multiple unique indexes available that seem like equally suitable + candidates, but with inconsistent definitions of "equals", then the + system chooses whatever it estimates to be the cheapest one to use + as an arbiter of taking the alternative + UPDATE/IGNORE path. + + + + The optional index_condition can be used to + allow the inference specification to infer that a partial unique + index can be used. Any unique index that otherwise satisfies the + inference specification, while also covering at least all the rows + in the table covered by index_condition may be used. It is + recommended that the partial index predicate of the unique index + intended to be used as the arbiter of taking the alternative path + be matched exactly, but this is not required. Note that an error + will be raised if an arbiter unique index is chosen that does not + cover the tuple or tuples ultimately proposed for insertion. + However, an overly specific index_condition does not imply that + arbitrating conflicts will be limited to the subset of rows covered + by the inferred unique index corresponding to index_condition. + + + The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted. This is primarily useful for obtaining values that were supplied by @@ -127,6 +224,49 @@ INSERT INTO table_name [ ( + column_name_index + + + The name of a table_name column (with several + columns potentially named). These are used to infer a + particular unique index defined on table_name. This requires + ON CONFLICT UPDATE and ON CONFLICT + IGNORE to assume that all expected sources of uniqueness + violations originate within the columns/rows constrained by the + unique index. When this is omitted, (which is forbidden with + the ON CONFLICT UPDATE variant), the system checks + for sources of uniqueness violations ahead of time in all unique + indexes. Otherwise, only a single specified unique index is + checked ahead of time, and uniqueness violation errors can + appear for conflicts originating in any other unique index. If + a unique index cannot be inferred, an error is raised. + + + + + + expression_index + + + Equivalent to column_name_index, but used to + infer a particular expressional index instead. + + + + + + index_condition + + + Used to allow inference of partial unique indexes. + + + + + DEFAULT VALUES @@ -171,8 +311,9 @@ INSERT INTO table_name [ ( An expression to be computed and returned by the INSERT - command after each row is inserted. The expression can use any - column names of the table named by table_name. + command after each row is inserted (not updated). The + expression can use any column names of the table named by + table_name. Write * to return all columns of the inserted row(s). @@ -311,7 +452,37 @@ WITH upd AS ( RETURNING * ) INSERT INTO employees_log SELECT *, current_timestamp FROM upd; - + + + + Insert a distributor, or do nothing for rows proposed for insertion + when an existing, excluded row (a row with a matching constrained + column or columns after before row insert triggers fire) exists. + Example assumes a unique index has been defined that constrains + values appearing in the did column (although + since the IGNORE variant was used, the specification of + columns to infer a unique index from is not mandatory): + + INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH') + ON CONFLICT (did) IGNORE + + + + Insert new distributor if possible; otherwise + IGNORE. Example assumes a unique index has been + defined that constrains values appearing in the + did column on a subset of rows where the + is_active boolean column evaluates to + true: + + -- This statement could infer a partial unique index on did + -- with a predicate of WHERE is_active, but it could also + -- just use a regular unique constraint on did if that was + -- all that was available. + INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design') + ON CONFLICT (did WHERE is_active) IGNORE + + @@ -321,7 +492,8 @@ INSERT INTO employees_log SELECT *, current_timestamp FROM upd; INSERT conforms to the SQL standard, except that the RETURNING clause is a PostgreSQL extension, as is the ability - to use WITH with INSERT. + to use WITH with INSERT, and the ability to + specify an alternative path with ON CONFLICT. Also, the case in which a column name list is omitted, but not all the columns are filled from the VALUES clause or query, diff --git a/doc/src/sgml/ref/set_constraints.sgml b/doc/src/sgml/ref/set_constraints.sgml index 7c31871..ba2b5ba 100644 --- a/doc/src/sgml/ref/set_constraints.sgml +++ b/doc/src/sgml/ref/set_constraints.sgml @@ -69,7 +69,10 @@ SET CONSTRAINTS { ALL | name [, ... Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE - constraints are affected by this setting. + constraints are affected by this setting. Note that constraints + that are DEFERRED cannot be used as arbiters by + the ON CONFLICT clause that INSERT + supports. NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index cb6f8a3..5152e08 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -1948,9 +1948,17 @@ heap_get_latest_tid(Relation relation, *tid = ctid; /* + * Should not have followed a t_ctid chain and found a speculative + * tuple that way + */ + Assert(!HeapTupleHeaderIsSpeculative(tp.t_data) || + !TransactionIdIsValid(priorXmax)); + + /* * If there's a valid t_ctid link, follow it, else we're done. */ if ((tp.t_data->t_infomask & HEAP_XMAX_INVALID) || + HeapTupleHeaderIsSpeculative(tp.t_data) || HeapTupleHeaderIsOnlyLocked(tp.t_data) || ItemPointerEquals(&tp.t_self, &tp.t_data->t_ctid)) { @@ -2156,7 +2164,11 @@ heap_insert(Relation relation, HeapTuple tup, CommandId cid, } xlrec.offnum = ItemPointerGetOffsetNumber(&heaptup->t_self); - xlrec.flags = all_visible_cleared ? XLOG_HEAP_ALL_VISIBLE_CLEARED : 0; + xlrec.flags = 0; + if (all_visible_cleared) + xlrec.flags |= XLOG_HEAP_ALL_VISIBLE_CLEARED; + if (options & HEAP_INSERT_SPECULATIVE) + xlrec.flags |= XLOG_HEAP_SPECULATIVE_TUPLE; Assert(ItemPointerGetBlockNumber(&heaptup->t_self) == BufferGetBlockNumber(buffer)); /* @@ -2262,6 +2274,10 @@ heap_prepare_insert(Relation relation, HeapTuple tup, TransactionId xid, tup->t_data->t_infomask2 &= ~(HEAP2_XACT_MASK); tup->t_data->t_infomask |= HEAP_XMAX_INVALID; HeapTupleHeaderSetXmin(tup->t_data, xid); + + if (options & HEAP_INSERT_SPECULATIVE) + HeapTupleHeaderSetSpeculative(tup->t_data); + if (options & HEAP_INSERT_FROZEN) HeapTupleHeaderSetXminFrozen(tup->t_data); @@ -2616,11 +2632,17 @@ xmax_infomask_changed(uint16 new_infomask, uint16 old_infomask) * (the last only for HeapTupleSelfUpdated, since we * cannot obtain cmax from a combocid generated by another transaction). * See comments for struct HeapUpdateFailureData for additional info. + * + * If 'speculative' is true, caller requires that we "super-delete" a tuple we + * just inserted in the same command. Instead of the normal visibility checks, + * we check that the tuple was inserted by the current transaction and given + * command id. Also, instead of setting its xmax, we set xmin to invalid, + * making it immediately appear as dead to everyone. */ HTSU_Result heap_delete(Relation relation, ItemPointer tid, CommandId cid, Snapshot crosscheck, bool wait, - HeapUpdateFailureData *hufd) + HeapUpdateFailureData *hufd, bool speculative) { HTSU_Result result; TransactionId xid = GetCurrentTransactionId(); @@ -2678,7 +2700,18 @@ heap_delete(Relation relation, ItemPointer tid, tp.t_self = *tid; l1: - result = HeapTupleSatisfiesUpdate(&tp, cid, buffer); + if (!speculative) + { + result = HeapTupleSatisfiesUpdate(&tp, cid, buffer); + } + else + { + if (tp.t_data->t_choice.t_heap.t_xmin != xid || + tp.t_data->t_choice.t_heap.t_field3.t_cid != cid) + elog(ERROR, "attempted to super-delete a tuple from other CID"); + result = HeapTupleMayBeUpdated; + } + if (result == HeapTupleInvisible) { @@ -2823,12 +2856,19 @@ l1: * using our own TransactionId below, since some other backend could * incorporate our XID into a MultiXact immediately afterwards.) */ - MultiXactIdSetOldestMember(); + if (!speculative) + { + MultiXactIdSetOldestMember(); - compute_new_xmax_infomask(HeapTupleHeaderGetRawXmax(tp.t_data), - tp.t_data->t_infomask, tp.t_data->t_infomask2, - xid, LockTupleExclusive, true, - &new_xmax, &new_infomask, &new_infomask2); + compute_new_xmax_infomask(HeapTupleHeaderGetRawXmax(tp.t_data), + tp.t_data->t_infomask, tp.t_data->t_infomask2, + xid, LockTupleExclusive, true, + &new_xmax, &new_infomask, &new_infomask2); + } + else + { + new_xmax = new_infomask = new_infomask2 = 0; + } START_CRIT_SECTION(); @@ -2855,8 +2895,23 @@ l1: tp.t_data->t_infomask |= new_infomask; tp.t_data->t_infomask2 |= new_infomask2; HeapTupleHeaderClearHotUpdated(tp.t_data); - HeapTupleHeaderSetXmax(tp.t_data, new_xmax); - HeapTupleHeaderSetCmax(tp.t_data, cid, iscombo); + /* + * When killing a speculatively-inserted tuple, we set xmin to invalid + * instead of setting xmax, to make the tuple clearly invisible to + * everyone. In particular, we want HeapTupleSatisfiesDirty() to regard + * the tuple as dead, so that another backend inserting a duplicate key + * value won't unnecessarily wait for our transaction to finish. + */ + if (!speculative) + { + HeapTupleHeaderSetXmax(tp.t_data, new_xmax); + HeapTupleHeaderSetCmax(tp.t_data, cid, iscombo); + } + else + { + HeapTupleHeaderSetXmin(tp.t_data, InvalidTransactionId); + } + /* Make sure there is no forward chain link in t_ctid */ tp.t_data->t_ctid = tp.t_self; @@ -2872,7 +2927,11 @@ l1: if (RelationIsAccessibleInLogicalDecoding(relation)) log_heap_new_cid(relation, &tp); - xlrec.flags = all_visible_cleared ? XLOG_HEAP_ALL_VISIBLE_CLEARED : 0; + xlrec.flags = 0; + if (all_visible_cleared) + xlrec.flags |= XLOG_HEAP_ALL_VISIBLE_CLEARED; + if (speculative) + xlrec.flags |= XLOG_HEAP_SPECULATIVE_TUPLE; xlrec.infobits_set = compute_infobits(tp.t_data->t_infomask, tp.t_data->t_infomask2); xlrec.offnum = ItemPointerGetOffsetNumber(&tp.t_self); @@ -2977,7 +3036,7 @@ simple_heap_delete(Relation relation, ItemPointer tid) result = heap_delete(relation, tid, GetCurrentCommandId(true), InvalidSnapshot, true /* wait for commit */ , - &hufd); + &hufd, false); switch (result) { case HeapTupleSelfUpdated: @@ -3686,6 +3745,7 @@ l2: /* Clear obsolete visibility flags ... */ oldtup.t_data->t_infomask &= ~(HEAP_XMAX_BITS | HEAP_MOVED); oldtup.t_data->t_infomask2 &= ~HEAP_KEYS_UPDATED; + oldtup.t_data->t_infomask2 &= ~HEAP_SPECULATIVE; /* ... and store info about transaction updating this tuple */ Assert(TransactionIdIsValid(xmax_old_tuple)); HeapTupleHeaderSetXmax(oldtup.t_data, xmax_old_tuple); @@ -4070,14 +4130,16 @@ get_mxact_status_for_lock(LockTupleMode mode, bool is_update) * * Function result may be: * HeapTupleMayBeUpdated: lock was successfully acquired + * HeapTupleInvisible: lock failed because tuple instantaneously invisible * HeapTupleSelfUpdated: lock failed because tuple updated by self * HeapTupleUpdated: lock failed because tuple updated by other xact * HeapTupleWouldBlock: lock couldn't be acquired and wait_policy is skip * - * In the failure cases, the routine fills *hufd with the tuple's t_ctid, - * t_xmax (resolving a possible MultiXact, if necessary), and t_cmax - * (the last only for HeapTupleSelfUpdated, since we - * cannot obtain cmax from a combocid generated by another transaction). + * In the failure cases other than HeapTupleInvisible, the routine fills + * *hufd with the tuple's t_ctid, t_xmax (resolving a possible MultiXact, + * if necessary), and t_cmax (the last only for HeapTupleSelfUpdated, + * since we cannot obtain cmax from a combocid generated by another + * transaction). * See comments for struct HeapUpdateFailureData for additional info. * * See README.tuplock for a thorough explanation of this mechanism. @@ -4115,8 +4177,15 @@ l3: if (result == HeapTupleInvisible) { - UnlockReleaseBuffer(*buffer); - elog(ERROR, "attempted to lock invisible tuple"); + LockBuffer(*buffer, BUFFER_LOCK_UNLOCK); + + /* + * This is possible, but only when locking a tuple for speculative + * insertion. We return this value here rather than throwing an error + * in order to give that case the opportunity to throw a more specific + * error. + */ + return HeapTupleInvisible; } else if (result == HeapTupleBeingUpdated) { @@ -7326,7 +7395,10 @@ heap_xlog_delete(XLogReaderState *record) HeapTupleHeaderClearHotUpdated(htup); fix_infomask_from_infobits(xlrec->infobits_set, &htup->t_infomask, &htup->t_infomask2); - HeapTupleHeaderSetXmax(htup, xlrec->xmax); + if (!(xlrec->flags & XLOG_HEAP_SPECULATIVE_TUPLE)) + HeapTupleHeaderSetXmax(htup, xlrec->xmax); + else + HeapTupleHeaderSetXmin(htup, InvalidTransactionId); HeapTupleHeaderSetCmax(htup, FirstCommandId, false); /* Mark the page as a candidate for pruning */ diff --git a/src/backend/access/nbtree/nbtinsert.c b/src/backend/access/nbtree/nbtinsert.c index 932c6f7..1a4e18d 100644 --- a/src/backend/access/nbtree/nbtinsert.c +++ b/src/backend/access/nbtree/nbtinsert.c @@ -51,7 +51,8 @@ static Buffer _bt_newroot(Relation rel, Buffer lbuf, Buffer rbuf); static TransactionId _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel, Buffer buf, OffsetNumber offset, ScanKey itup_scankey, - IndexUniqueCheck checkUnique, bool *is_unique); + IndexUniqueCheck checkUnique, bool *is_unique, + uint32 *speculativeToken); static void _bt_findinsertloc(Relation rel, Buffer *bufptr, OffsetNumber *offsetptr, @@ -159,17 +160,27 @@ top: */ if (checkUnique != UNIQUE_CHECK_NO) { - TransactionId xwait; + TransactionId xwait; + uint32 speculativeToken; offset = _bt_binsrch(rel, buf, natts, itup_scankey, false); xwait = _bt_check_unique(rel, itup, heapRel, buf, offset, itup_scankey, - checkUnique, &is_unique); + checkUnique, &is_unique, &speculativeToken); if (TransactionIdIsValid(xwait)) { /* Have to wait for the other guy ... */ _bt_relbuf(rel, buf); - XactLockTableWait(xwait, rel, &itup->t_tid, XLTW_InsertIndex); + /* + * If it's a speculative insertion, wait for it to finish (ie. + * to go ahead with the insertion, or kill the tuple). Otherwise + * wait for the transaction to finish as usual. + */ + if (speculativeToken) + SpeculativeInsertionWait(xwait, speculativeToken); + else + XactLockTableWait(xwait, rel, &itup->t_tid, XLTW_InsertIndex); + /* start over... */ _bt_freestack(stack); goto top; @@ -211,9 +222,12 @@ top: * also point to end-of-page, which means that the first tuple to check * is the first tuple on the next page. * - * Returns InvalidTransactionId if there is no conflict, else an xact ID - * we must wait for to see if it commits a conflicting tuple. If an actual - * conflict is detected, no return --- just ereport(). + * Returns InvalidTransactionId if there is no conflict, else an xact ID we + * must wait for to see if it commits a conflicting tuple. If an actual + * conflict is detected, no return --- just ereport(). If an xact ID is + * returned, and the conflicting tuple still has a speculative insertion in + * progress, *speculativeToken is set to non-zero, and the caller can wait for + * the verdict on the insertion using SpeculativeInsertionWait(). * * However, if checkUnique == UNIQUE_CHECK_PARTIAL, we always return * InvalidTransactionId because we don't want to wait. In this case we @@ -223,7 +237,8 @@ top: static TransactionId _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel, Buffer buf, OffsetNumber offset, ScanKey itup_scankey, - IndexUniqueCheck checkUnique, bool *is_unique) + IndexUniqueCheck checkUnique, bool *is_unique, + uint32 *speculativeToken) { TupleDesc itupdesc = RelationGetDescr(rel); int natts = rel->rd_rel->relnatts; @@ -340,6 +355,7 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel, if (nbuf != InvalidBuffer) _bt_relbuf(rel, nbuf); /* Tell _bt_doinsert to wait... */ + *speculativeToken = SnapshotDirty.speculativeToken; return xwait; } diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index f85ed93..e986d7e 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1662,6 +1662,10 @@ BuildIndexInfo(Relation index) /* other info */ ii->ii_Unique = indexStruct->indisunique; ii->ii_ReadyForInserts = IndexIsReady(indexStruct); + /* assume not doing speculative insertion for now */ + ii->ii_UniqueOps = NULL; + ii->ii_UniqueProcs = NULL; + ii->ii_UniqueStrats = NULL; /* initialize index-build state to default */ ii->ii_Concurrent = false; @@ -1671,6 +1675,53 @@ BuildIndexInfo(Relation index) } /* ---------------- + * AddUniqueSpeculative + * Append extra state to IndexInfo record + * + * For unique indexes, we usually don't want to add info to the IndexInfo for + * checking uniqueness, since the B-Tree AM handles that directly. However, in + * the case of speculative insertion, external support is required. + * + * Do this processing here rather than in BuildIndexInfo() to save the common + * non-speculative cases the overhead they'd otherwise incur. + * ---------------- + */ +void +AddUniqueSpeculative(Relation index, IndexInfo *ii) +{ + int ncols = index->rd_rel->relnatts; + int i; + + /* + * fetch info for checking unique indexes + */ + Assert(ii->ii_Unique); + + if (index->rd_rel->relam != BTREE_AM_OID) + elog(ERROR, "unexpected non-btree speculative unique index"); + + ii->ii_UniqueOps = (Oid *) palloc(sizeof(Oid) * ncols); + ii->ii_UniqueProcs = (Oid *) palloc(sizeof(Oid) * ncols); + ii->ii_UniqueStrats = (uint16 *) palloc(sizeof(uint16) * ncols); + + /* + * We have to look up the operator's strategy number. This + * provides a cross-check that the operator does match the index. + */ + /* We need the func OIDs and strategy numbers too */ + for (i = 0; i < ncols; i++) + { + ii->ii_UniqueStrats[i] = BTEqualStrategyNumber; + ii->ii_UniqueOps[i] = + get_opfamily_member(index->rd_opfamily[i], + index->rd_opcintype[i], + index->rd_opcintype[i], + ii->ii_UniqueStrats[i]); + ii->ii_UniqueProcs[i] = get_opcode(ii->ii_UniqueOps[i]); + } +} + +/* ---------------- * FormIndexDatum * Construct values[] and isnull[] arrays for a new index tuple. * @@ -2606,10 +2657,10 @@ IndexCheckExclusion(Relation heapRelation, /* * Check that this tuple has no conflicts. */ - check_exclusion_constraint(heapRelation, - indexRelation, indexInfo, - &(heapTuple->t_self), values, isnull, - estate, true, false); + check_exclusion_or_unique_constraint(heapRelation, indexRelation, + indexInfo, &(heapTuple->t_self), + values, isnull, estate, true, + false, true, NULL); } heap_endscan(scan); diff --git a/src/backend/catalog/indexing.c b/src/backend/catalog/indexing.c index fe123ad..0231084 100644 --- a/src/backend/catalog/indexing.c +++ b/src/backend/catalog/indexing.c @@ -46,7 +46,7 @@ CatalogOpenIndexes(Relation heapRel) resultRelInfo->ri_RelationDesc = heapRel; resultRelInfo->ri_TrigDesc = NULL; /* we don't fire triggers */ - ExecOpenIndices(resultRelInfo); + ExecOpenIndices(resultRelInfo, false); return resultRelInfo; } diff --git a/src/backend/commands/constraint.c b/src/backend/commands/constraint.c index 561d8fa..d5ab12f 100644 --- a/src/backend/commands/constraint.c +++ b/src/backend/commands/constraint.c @@ -170,9 +170,10 @@ unique_key_recheck(PG_FUNCTION_ARGS) * For exclusion constraints we just do the normal check, but now it's * okay to throw error. */ - check_exclusion_constraint(trigdata->tg_relation, indexRel, indexInfo, - &(new_row->t_self), values, isnull, - estate, false, false); + check_exclusion_or_unique_constraint(trigdata->tg_relation, indexRel, + indexInfo, &(new_row->t_self), + values, isnull, estate, false, + false, true, NULL); } /* diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 92ff632..f85bd60 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -2283,7 +2283,7 @@ CopyFrom(CopyState cstate) 1, /* dummy rangetable index */ 0); - ExecOpenIndices(resultRelInfo); + ExecOpenIndices(resultRelInfo, false); estate->es_result_relations = resultRelInfo; estate->es_num_result_relations = 1; @@ -2438,7 +2438,8 @@ CopyFrom(CopyState cstate) if (resultRelInfo->ri_NumIndices > 0) recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self), - estate); + estate, false, + InvalidOid); /* AFTER ROW INSERT Triggers */ ExecARInsertTriggers(estate, resultRelInfo, tuple, @@ -2552,7 +2553,7 @@ CopyFromInsertBatch(CopyState cstate, EState *estate, CommandId mycid, ExecStoreTuple(bufferedTuples[i], myslot, InvalidBuffer, false); recheckIndexes = ExecInsertIndexTuples(myslot, &(bufferedTuples[i]->t_self), - estate); + estate, false, InvalidOid); ExecARInsertTriggers(estate, resultRelInfo, bufferedTuples[i], recheckIndexes); diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 315a528..d88c7fa 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -2196,6 +2196,15 @@ static void ExplainModifyTarget(ModifyTable *plan, ExplainState *es) { ExplainTargetRel((Plan *) plan, plan->nominalRelation, es); + + if (plan->arbiterIndex != InvalidOid) + { + char *indexname = get_rel_name(plan->arbiterIndex); + + /* nothing to do for text format explains */ + if (es->format != EXPLAIN_FORMAT_TEXT && indexname != NULL) + ExplainPropertyText("Arbiter Index", indexname, es); + } } /* diff --git a/src/backend/executor/README b/src/backend/executor/README index 8afa1e3..862c312 100644 --- a/src/backend/executor/README +++ b/src/backend/executor/README @@ -200,3 +200,76 @@ is no explicit prohibition on SRFs in UPDATE, but the net effect will be that only the first result row of an SRF counts, because all subsequent rows will result in attempts to re-update an already updated target row. This is historical behavior and seems not worth changing.) + +Speculative insertion +--------------------- + +Speculative insertion is a process that the executor manages for the benefit of +INSERT...ON CONFLICT IGNORE. Supported indexes include nbtree unique +indexes (nbtree is currently the only amcanunique index access method), or +exclusion constraint indexes (exclusion constraints are considered a +generalization of unique constraints). + +The primary user-visible goal for INSERT ... ON CONFLICT is to guarantee either +an insert, or a conclusive determination that an insert cannot go ahead (due to +a conclusively committed/visible conflict). A would-be conflict (and the +associated index) are the arbiters of whether or not the alternative (IGNORE) +path is taken. The implementation more or less tries to insert until one or +the other of those two outcomes is reached. There are some non-obvious hazards +involved that are carefully avoided. These hazards relate to concurrent +activity causing conflicts for the implementation, which must be handled. + +The index is the authoritative source of truth for whether there is or is not a +conflict, for unique index enforcement in general, and for speculative +insertion in particular. The heap must still be considered, though, not least +since it alone has authoritative visibility information. Through looping, we +hope to overcome the disconnect between the heap and the arbiter index. We +must lock the row, and then verify that there is no conflict. Only then do we +UPDATE. Theoretically, some individual session could loop forever, although +under high concurrency one session always proceeds. + +The first step in the loop is to perform a pre-check. The indexes are scanned +for existing conflicting values. At this point, we may have to wait until the +end of another xact (or xact's promise token -- more on that later), iff it +isn't immediately conclusive that there is or is not a conflict (when we finish +the pre-check, there is a conclusion about there either being or +not being a conflict). + +The second step (skipped when a conflict is found) is to insert a heap tuple +and related index tuples opportunistically. This uses the same mechanism as +deferred unique indexes, and so we never wait for a possibly conflicting xact +to commit or abort (unlike with conventional unique index insertion) -- we +simply detect a possible conflict. + +When opportunistically inserting during the second step, we are not logically +inserting a tuple as such. Rather, the process is somewhat similar to the +conventional unique index insertion steps taken within the nbtree AM, where we +must briefly lock the *value* being inserted: in that codepath, the value +proposed for insertion is for an instant locked *in the abstract*, by way of a +buffer lock on "the first leaf page the value could be on". Then, having +established the right to physically insert, do so (or throw an error). For +speculative insertion, if no conflict occurs during the insertion (which is +usually the case, since it was just determined in the first step that there was +no conflict), then we're done. Otherwise, we must restart (and likely find the +same conflict tuple during the first step of the new iteration). But a +counter-intuitive step must be taken first (which is what makes this whole +dance similar to conventional nbtree "value locking"). + +We must "super delete" the tuple when the opportunistic insertion finds a +conflict. This means that it immediately becomes invisible to all snapshot +types, and immediately becomes reclaimable by VACUUM. Other backends +(speculative inserters or ordinary inserters) know to not wait on our +transaction end when they encounter an optimistically inserted "promise tuple". +Rather, they wait on a corresponding promise token lock, which we hold only for +as long as opportunistically inserting. We release the lock when done +opportunistically inserting (and after "super deleting", if that proved +necessary), releasing our waiters (who will ordinarily re-find our promise +tuple as a bona fide tuple, or occasionally will find that they can insert +after all). It's important that other xacts not wait on the end of our xact +until we've established that we've successfully and conclusively inserted +logically (or established that there was an insertion conflict, and cleaned up +after it by "super deleting"). Otherwise, concurrent speculative inserters +could be involved in "unprincipled deadlocks": deadlocks where there is no +user-visible mutual dependency, and yet an implementation related mutual +dependency is unexpectedly introduced. The user might be left with no +reasonable way of avoiding these deadlocks, which would not be okay. diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index ad7e207..0be893d 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -2093,7 +2093,7 @@ EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, * the latest version of the row was deleted, so we need do * nothing. (Should be safe to examine xmin without getting * buffer's content lock, since xmin never changes in an existing - * tuple.) + * non-promise tuple.) */ if (!TransactionIdEquals(HeapTupleHeaderGetXmin(tuple.t_data), priorXmax)) @@ -2174,11 +2174,12 @@ EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, * case, so as to avoid the "Halloween problem" of * repeated update attempts. In the latter case it might * be sensible to fetch the updated tuple instead, but - * doing so would require changing heap_lock_tuple as well - * as heap_update and heap_delete to not complain about - * updating "invisible" tuples, which seems pretty scary. - * So for now, treat the tuple as deleted and do not - * process. + * doing so would require changing heap_update and + * heap_delete to not complain about updating "invisible" + * tuples, which seems pretty scary (heap_lock_tuple will + * not complain, but few callers expect HeapTupleInvisible, + * and we're not one of them). So for now, treat the tuple + * as deleted and do not process. */ ReleaseBuffer(buffer); return NULL; @@ -2193,6 +2194,9 @@ EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, ereport(ERROR, (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE), errmsg("could not serialize access due to concurrent update"))); + + /* Should not encounter speculative tuple on recheck */ + Assert(!HeapTupleHeaderIsSpeculative(tuple.t_data)); if (!ItemPointerEquals(&hufd.ctid, &tuple.t_self)) { /* it was updated, so look at the updated version */ diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c index 022041b..0ab018b 100644 --- a/src/backend/executor/execUtils.c +++ b/src/backend/executor/execUtils.c @@ -44,6 +44,7 @@ #include "access/relscan.h" #include "access/transam.h" +#include "access/xact.h" #include "catalog/index.h" #include "executor/execdebug.h" #include "nodes/nodeFuncs.h" @@ -885,7 +886,7 @@ ExecCloseScanRelation(Relation scanrel) * ---------------------------------------------------------------- */ void -ExecOpenIndices(ResultRelInfo *resultRelInfo) +ExecOpenIndices(ResultRelInfo *resultRelInfo, bool speculative) { Relation resultRelation = resultRelInfo->ri_RelationDesc; List *indexoidlist; @@ -938,6 +939,13 @@ ExecOpenIndices(ResultRelInfo *resultRelInfo) /* extract index key information from the index's pg_index info */ ii = BuildIndexInfo(indexDesc); + /* + * Iff the indexes are to be used for speculative insertion, add extra + * information required by unique index entries + */ + if (speculative && ii->ii_Unique) + AddUniqueSpeculative(indexDesc, ii); + relationDescs[i] = indexDesc; indexInfoArray[i] = ii; i++; @@ -990,7 +998,8 @@ ExecCloseIndices(ResultRelInfo *resultRelInfo) * * This returns a list of index OIDs for any unique or exclusion * constraints that are deferred and that had - * potential (unconfirmed) conflicts. + * potential (unconfirmed) conflicts. (if noDupErr == true, the + * same is done for non-deferred constraints) * * CAUTION: this must not be called for a HOT update. * We can't defend against that here for lack of info. @@ -1000,7 +1009,9 @@ ExecCloseIndices(ResultRelInfo *resultRelInfo) List * ExecInsertIndexTuples(TupleTableSlot *slot, ItemPointer tupleid, - EState *estate) + EState *estate, + bool noDupErr, + Oid arbiterIdx) { List *result = NIL; ResultRelInfo *resultRelInfo; @@ -1070,7 +1081,17 @@ ExecInsertIndexTuples(TupleTableSlot *slot, /* Skip this index-update if the predicate isn't satisfied */ if (!ExecQual(predicate, econtext, false)) + { + if (arbiterIdx == indexRelation->rd_index->indexrelid) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("partial arbiter unique index has predicate that does not cover tuple proposed for insertion"), + errdetail("ON CONFLICT inference clause implies that the tuple proposed for insertion must be covered by predicate for partial index \"%s\".", + RelationGetRelationName(indexRelation)), + errtableconstraint(heapRelation, + RelationGetRelationName(indexRelation)))); continue; + } } /* @@ -1092,9 +1113,16 @@ ExecInsertIndexTuples(TupleTableSlot *slot, * For a deferrable unique index, we tell the index AM to just detect * possible non-uniqueness, and we add the index OID to the result * list if further checking is needed. + * + * For a speculative insertion (used by INSERT ... ON CONFLICT), just + * detect possible non-uniqueness, and tell the caller if it failed. */ if (!indexRelation->rd_index->indisunique) checkUnique = UNIQUE_CHECK_NO; + else if (noDupErr && arbiterIdx == InvalidOid) + checkUnique = UNIQUE_CHECK_PARTIAL; + else if (noDupErr && arbiterIdx == indexRelation->rd_index->indexrelid) + checkUnique = UNIQUE_CHECK_PARTIAL; else if (indexRelation->rd_index->indimmediate) checkUnique = UNIQUE_CHECK_YES; else @@ -1112,8 +1140,11 @@ ExecInsertIndexTuples(TupleTableSlot *slot, * If the index has an associated exclusion constraint, check that. * This is simpler than the process for uniqueness checks since we * always insert first and then check. If the constraint is deferred, - * we check now anyway, but don't throw error on violation; instead - * we'll queue a recheck event. + * we check now anyway, but don't throw error on violation or wait for + * a conclusive outcome from a concurrent insertion; instead we'll + * queue a recheck event. Similarly, noDupErr callers (speculative + * inserters) will recheck later, and wait for a conclusive outcome + * then. * * An index for an exclusion constraint can't also be UNIQUE (not an * essential property, we just don't allow it in the grammar), so no @@ -1121,13 +1152,15 @@ ExecInsertIndexTuples(TupleTableSlot *slot, */ if (indexInfo->ii_ExclusionOps != NULL) { - bool errorOK = !indexRelation->rd_index->indimmediate; + bool violationOK = (!indexRelation->rd_index->indimmediate || + noDupErr); satisfiesConstraint = - check_exclusion_constraint(heapRelation, - indexRelation, indexInfo, - tupleid, values, isnull, - estate, false, errorOK); + check_exclusion_or_unique_constraint(heapRelation, + indexRelation, indexInfo, + tupleid, values, isnull, + estate, false, + violationOK, false, NULL); } if ((checkUnique == UNIQUE_CHECK_PARTIAL || @@ -1135,7 +1168,7 @@ ExecInsertIndexTuples(TupleTableSlot *slot, !satisfiesConstraint) { /* - * The tuple potentially violates the uniqueness or exclusion + * The tuple potentially violates the unique index or exclusion * constraint, so make a note of the index so that we can re-check * it later. */ @@ -1146,18 +1179,154 @@ ExecInsertIndexTuples(TupleTableSlot *slot, return result; } +/* ---------------------------------------------------------------- + * ExecCheckIndexConstraints + * + * This routine checks if a tuple violates any unique or + * exclusion constraints. If no conflict, returns true. + * Otherwise returns false, and the TID of the conflicting + * tuple is returned in *conflictTid + * + * Note that this doesn't lock the values in any way, so it's + * possible that a conflicting tuple is inserted immediately + * after this returns, and a later insert with the same values + * still conflicts. But this can be used for a pre-check before + * insertion. + * ---------------------------------------------------------------- + */ +bool +ExecCheckIndexConstraints(TupleTableSlot *slot, + EState *estate, ItemPointer conflictTid, + Oid arbiterIdx) +{ + ResultRelInfo *resultRelInfo; + int i; + int numIndices; + RelationPtr relationDescs; + Relation heapRelation; + IndexInfo **indexInfoArray; + ExprContext *econtext; + Datum values[INDEX_MAX_KEYS]; + bool isnull[INDEX_MAX_KEYS]; + ItemPointerData invalidItemPtr; + bool checkedIndex = false; + + ItemPointerSetInvalid(conflictTid); + ItemPointerSetInvalid(&invalidItemPtr); + + /* + * Get information from the result relation info structure. + */ + resultRelInfo = estate->es_result_relation_info; + numIndices = resultRelInfo->ri_NumIndices; + relationDescs = resultRelInfo->ri_IndexRelationDescs; + indexInfoArray = resultRelInfo->ri_IndexRelationInfo; + heapRelation = resultRelInfo->ri_RelationDesc; + + /* + * We will use the EState's per-tuple context for evaluating predicates + * and index expressions (creating it if it's not already there). + */ + econtext = GetPerTupleExprContext(estate); + + /* Arrange for econtext's scan tuple to be the tuple under test */ + econtext->ecxt_scantuple = slot; + + /* + * for each index, form and insert the index tuple + */ + for (i = 0; i < numIndices; i++) + { + Relation indexRelation = relationDescs[i]; + IndexInfo *indexInfo; + bool satisfiesConstraint; + + if (indexRelation == NULL) + continue; + + indexInfo = indexInfoArray[i]; + + if (!indexInfo->ii_Unique && !indexInfo->ii_ExclusionOps) + continue; + + /* If the index is marked as read-only, ignore it */ + if (!indexInfo->ii_ReadyForInserts) + continue; + + /* When specific arbiter index requested, only examine it */ + if (arbiterIdx != InvalidOid && + arbiterIdx != indexRelation->rd_index->indexrelid) + continue; + + checkedIndex = true; + + /* Check for partial index */ + if (indexInfo->ii_Predicate != NIL) + { + List *predicate; + + /* + * If predicate state not set up yet, create it (in the estate's + * per-query context) + */ + predicate = indexInfo->ii_PredicateState; + if (predicate == NIL) + { + predicate = (List *) + ExecPrepareExpr((Expr *) indexInfo->ii_Predicate, + estate); + indexInfo->ii_PredicateState = predicate; + } + + /* Skip this index-update if the predicate isn't satisfied */ + if (!ExecQual(predicate, econtext, false)) + continue; + } + + /* + * FormIndexDatum fills in its values and isnull parameters with the + * appropriate values for the column(s) of the index. + */ + FormIndexDatum(indexInfo, + slot, + estate, + values, + isnull); + + satisfiesConstraint = + check_exclusion_or_unique_constraint(heapRelation, indexRelation, + indexInfo, &invalidItemPtr, + values, isnull, estate, false, + true, true, conflictTid); + if (!satisfiesConstraint) + return false; + + /* If this was a user-specified arbiter index, we're done */ + if (arbiterIdx == indexRelation->rd_index->indexrelid) + break; + } + + if (arbiterIdx != InvalidOid && !checkedIndex) + elog(ERROR, "unexpected failure to find arbiter unique index"); + + return true; +} + /* - * Check for violation of an exclusion constraint + * Check for violation of an exclusion or unique constraint * * heap: the table containing the new tuple * index: the index supporting the exclusion constraint * indexInfo: info about the index, including the exclusion properties - * tupleid: heap TID of the new tuple we have just inserted + * tupleid: heap TID of the new tuple we have just inserted (invalid if we + * haven't inserted a new tuple yet) * values, isnull: the *index* column values computed for the new tuple * estate: an EState we can do evaluation in * newIndex: if true, we are trying to build a new index (this affects * only the wording of error messages) * errorOK: if true, don't throw error for violation + * wait: if true, wait for conflicting transaction to finish, even if !errorOK + * conflictTid: if not-NULL, the TID of conflicting tuple is returned here. * * Returns true if OK, false if actual or potential violation * @@ -1167,16 +1336,25 @@ ExecInsertIndexTuples(TupleTableSlot *slot, * is convenient for deferred exclusion checks; we need not bother queuing * a deferred event if there is definitely no conflict at insertion time. * - * When errorOK is false, we'll throw error on violation, so a false result + * When violationOK is false, we'll throw error on violation, so a false result * is impossible. + * + * Note: The indexam is normally responsible for checking unique constraints, + * so this normally only needs to be used for exclusion constraints. But this + * function is also called when doing a "pre-check" for conflicts, for the + * benefit of speculative insertion. Caller may request that conflict TID be + * set, to take further steps. */ bool -check_exclusion_constraint(Relation heap, Relation index, IndexInfo *indexInfo, - ItemPointer tupleid, Datum *values, bool *isnull, - EState *estate, bool newIndex, bool errorOK) +check_exclusion_or_unique_constraint(Relation heap, Relation index, + IndexInfo *indexInfo, ItemPointer tupleid, + Datum *values, bool *isnull, + EState *estate, bool newIndex, + bool violationOK, bool wait, + ItemPointer conflictTid) { - Oid *constr_procs = indexInfo->ii_ExclusionProcs; - uint16 *constr_strats = indexInfo->ii_ExclusionStrats; + Oid *constr_procs; + uint16 *constr_strats; Oid *index_collations = index->rd_indcollation; int index_natts = index->rd_index->indnatts; IndexScanDesc index_scan; @@ -1190,6 +1368,17 @@ check_exclusion_constraint(Relation heap, Relation index, IndexInfo *indexInfo, TupleTableSlot *existing_slot; TupleTableSlot *save_scantuple; + if (indexInfo->ii_ExclusionOps) + { + constr_procs = indexInfo->ii_ExclusionProcs; + constr_strats = indexInfo->ii_ExclusionStrats; + } + else + { + constr_procs = indexInfo->ii_UniqueProcs; + constr_strats = indexInfo->ii_UniqueStrats; + } + /* * If any of the input values are NULL, the constraint check is assumed to * pass (i.e., we assume the operators are strict). @@ -1254,7 +1443,8 @@ retry: /* * Ignore the entry for the tuple we're trying to check. */ - if (ItemPointerEquals(tupleid, &tup->t_self)) + if (ItemPointerIsValid(tupleid) && + ItemPointerEquals(tupleid, &tup->t_self)) { if (found_self) /* should not happen */ elog(ERROR, "found self tuple multiple times in index \"%s\"", @@ -1284,17 +1474,6 @@ retry: } /* - * At this point we have either a conflict or a potential conflict. If - * we're not supposed to raise error, just return the fact of the - * potential conflict without waiting to see if it's real. - */ - if (errorOK) - { - conflict = true; - break; - } - - /* * If an in-progress transaction is affecting the visibility of this * tuple, we need to wait for it to complete and then recheck. For * simplicity we do rechecking by just restarting the whole scan --- @@ -1305,18 +1484,87 @@ retry: xwait = TransactionIdIsValid(DirtySnapshot.xmin) ? DirtySnapshot.xmin : DirtySnapshot.xmax; + /* + * At this point we have either a conflict or a potential conflict. If + * we're not supposed to raise error, just return the fact of the + * potential conflict without waiting to see if it's real. + */ + if (violationOK && !wait) + { + /* + * For unique indexes, detecting conflict is coupled with physical + * index tuple insertion, so we won't be called for recheck + */ + Assert(!indexInfo->ii_Unique); + + conflict = true; + if (conflictTid) + *conflictTid = tup->t_self; + + /* + * Livelock insurance. + * + * When doing a speculative insertion pre-check, we cannot have an + * "unprincipled deadlock" with another session, fundamentally + * because there is no possible mutual dependency, since we only + * hold a lock on our token, without attempting to lock anything + * else (maybe this is not the first iteration, but no matter; + * we'll have super deleted and released insertion token lock if + * so, and all locks needed are already held. Also, our XID lock + * is irrelevant.) + * + * In the second phase, where there is a re-check for conflicts, + * we can't deadlock either (we never lock another thing, since we + * don't wait in that phase). However, a theoretical livelock + * hazard exists: Two sessions could each see each other's + * conflicting tuple, and each could go and delete, retrying + * forever. + * + * To break the mutual dependency, we may wait on the other xact + * here over our caller's request to not do so (in the second + * phase). This does not imply the risk of unprincipled deadlocks + * either, because if we end up unexpectedly waiting, the other + * session will super delete its own tuple *before* releasing its + * token lock and freeing us, and without attempting to wait on us + * to release our token lock. We'll take another iteration here, + * after waiting on the other session's token, not find a conflict + * this time, and then proceed (assuming we're the oldest XID). + * + * N.B.: Unprincipled deadlocks are still theoretically possible + * with non-speculative insertion with exclusion constraints, but + * this seems inconsequential, since an error was inevitable for + * one of the sessions anyway. We only worry about speculative + * insertion's problems, since they're likely with idiomatic + * usage. + */ + if (TransactionIdPrecedes(xwait, GetCurrentTransactionId())) + break; /* go and super delete/restart speculative insertion */ + } + if (TransactionIdIsValid(xwait)) { ctid_wait = tup->t_data->t_ctid; index_endscan(index_scan); - XactLockTableWait(xwait, heap, &ctid_wait, - XLTW_RecheckExclusionConstr); + if (DirtySnapshot.speculativeToken) + SpeculativeInsertionWait(DirtySnapshot.xmin, + DirtySnapshot.speculativeToken); + else + XactLockTableWait(xwait, heap, &ctid_wait, + XLTW_RecheckExclusionConstr); goto retry; } /* - * We have a definite conflict. Report it. + * We have a definite conflict. Return it to caller, or report it. */ + if (violationOK) + { + conflict = true; + if (conflictTid) + *conflictTid = tup->t_self; + break; + } + error_new = BuildIndexValueDescription(index, values, isnull); error_existing = BuildIndexValueDescription(index, existing_values, existing_isnull); @@ -1352,6 +1600,9 @@ retry: * However, it is possible to define exclusion constraints for which that * wouldn't be true --- for instance, if the operator is <>. So we no * longer complain if found_self is still false. + * + * It would also not be true in the pre-check mode, when we haven't + * inserted a tuple yet. */ econtext->ecxt_scantuple = save_scantuple; diff --git a/src/backend/executor/nodeLockRows.c b/src/backend/executor/nodeLockRows.c index bb6df47..56da0f5 100644 --- a/src/backend/executor/nodeLockRows.c +++ b/src/backend/executor/nodeLockRows.c @@ -152,10 +152,11 @@ lnext: * case, so as to avoid the "Halloween problem" of repeated * update attempts. In the latter case it might be sensible * to fetch the updated tuple instead, but doing so would - * require changing heap_lock_tuple as well as heap_update and - * heap_delete to not complain about updating "invisible" - * tuples, which seems pretty scary. So for now, treat the - * tuple as deleted and do not process. + * require changing heap_update and heap_delete to not complain + * about updating "invisible" tuples, which seems pretty scary + * (heap_lock_tuple will not complain, but few callers expect + * HeapTupleInvisible, and we're not one of them). So for now, + * treat the tuple as deleted and do not process. */ goto lnext; diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index f96fb24..29b5b77 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -46,6 +46,7 @@ #include "miscadmin.h" #include "nodes/nodeFuncs.h" #include "storage/bufmgr.h" +#include "storage/lmgr.h" #include "utils/builtins.h" #include "utils/memutils.h" #include "utils/rel.h" @@ -151,6 +152,35 @@ ExecProcessReturning(ProjectionInfo *projectReturning, return ExecProject(projectReturning, NULL); } +/* + * ExecCheckHeapTupleVisible -- verify heap tuple is visible + * + * It would not be consistent with guarantees of the higher isolation levels to + * proceed with avoiding insertion (taking speculative insertion's alternative + * path) on the basis of another tuple that is not visible. Check for the need + * to raise a serialization failure, and do so as necessary. + */ +static void +ExecCheckHeapTupleVisible(EState *estate, + ResultRelInfo *relinfo, + ItemPointer tid) +{ + Relation rel = relinfo->ri_RelationDesc; + Buffer buffer; + HeapTupleData tuple; + + if (!IsolationUsesXactSnapshot()) + return; + + tuple.t_self = *tid; + if (!heap_fetch(rel, estate->es_snapshot, &tuple, &buffer, false, NULL)) + ereport(ERROR, + (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE), + errmsg("could not serialize access due to concurrent insert or update dictating alternative ON CONFLICT path"))); + + ReleaseBuffer(buffer); +} + /* ---------------------------------------------------------------- * ExecInsert * @@ -163,6 +193,8 @@ ExecProcessReturning(ProjectionInfo *projectReturning, static TupleTableSlot * ExecInsert(TupleTableSlot *slot, TupleTableSlot *planSlot, + Oid arbiterIndex, + SpecCmd spec, EState *estate, bool canSetTag) { @@ -246,6 +278,8 @@ ExecInsert(TupleTableSlot *slot, } else { + ItemPointerData conflictTid; + /* * Constraints might reference the tableoid column, so initialize * t_tableOid before evaluating them. @@ -259,20 +293,124 @@ ExecInsert(TupleTableSlot *slot, ExecConstraints(resultRelInfo, slot, estate); /* + * If we are performing speculative insertion, do a non-conclusive + * check for conflicts. + * + * See the executor README for a full discussion of speculative + * insertion. + */ +vlock: + if (spec != SPEC_NONE && resultRelInfo->ri_NumIndices > 0) + { + uint32 specToken; + + /* + * No need to check if running in bootstrap mode, since ON + * CONFLICT with system catalogs forbidden generally. + * + * Check if it's required to proceed with the second phase + * ("insertion proper") of speculative insertion in respect of the + * slot. If insertion ultimately does not proceed, no firing of + * AFTER ROW INSERT triggers occurs. + * + * We don't suppress the effects (or, perhaps, side-effects) of + * BEFORE ROW INSERT triggers. This isn't ideal, but then we + * cannot proceed with even considering uniqueness violations + * until these triggers fire on the one hand, but on the other + * hand they have the ability to execute arbitrary user-defined + * code which may perform operations entirely outside the system's + * ability to nullify. + */ + if (!ExecCheckIndexConstraints(slot, estate, &conflictTid, + arbiterIndex)) + { + /* + * For the SPEC_IGNORE case, it's still often necessary to + * verify that the tuple is visible to the executor's MVCC + * snapshot. + */ + if (spec == SPEC_IGNORE) + ExecCheckHeapTupleVisible(estate, resultRelInfo, &conflictTid); + + /* + * The IGNORE path projects no tuples + */ + return NULL; + } + + /* + * Before we start insertion proper, acquire our "promise tuple + * insertion lock". Others can use that (rather than an XID lock, + * which is appropriate only for non-promise tuples) to wait for us + * to decide if we're going to go ahead with the insertion. + */ + specToken = SpeculativeInsertionLockAcquire(GetCurrentTransactionId()); + ItemPointerSetBlockNumber(&tuple->t_data->t_ctid, specToken); + } + + /* * insert the tuple * * Note: heap_insert returns the tid (location) of the new tuple in * the t_self field. */ newId = heap_insert(resultRelationDesc, tuple, - estate->es_output_cid, 0, NULL); + estate->es_output_cid, + spec != SPEC_NONE ? HEAP_INSERT_SPECULATIVE : 0, + NULL); /* * insert index entries for tuple */ if (resultRelInfo->ri_NumIndices > 0) + { recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self), - estate); + estate, spec != SPEC_NONE, + arbiterIndex); + + if (spec != SPEC_NONE) + { + HeapUpdateFailureData hufd; + + /* + * Consider possible race: concurrent insertion conflicts with + * our speculative heap tuple. Must then "super-delete" the + * heap tuple and retry from the start. + * + * This is occasionally necessary so that "unprincipled + * deadlocks" are avoided; now that a conflict was found, + * other sessions should not wait on our speculative token, + * and they certainly shouldn't treat our + * speculatively-inserted heap tuple as an ordinary tuple that + * it must wait on the outcome of our xact to UPDATE/DELETE. + * This makes heap tuples behave as conceptual "value locks" + * of short duration, distinct from ordinary tuples that other + * xacts must wait on xmin-xact-end of in the event of a + * possible unique/exclusion violation (the violation that + * arbitrates taking the alternative path). + */ + if (recheckIndexes) + heap_delete(resultRelationDesc, &(tuple->t_self), + estate->es_output_cid, InvalidSnapshot, false, + &hufd, true); + + /* + * Release speculative insertion lock. Iff there was no + * insertion conflict, and the tuple was therefore not super + * deleted, this effectively make the promise tuple an ordinary + * tuple + */ + SpeculativeInsertionLockRelease(GetCurrentTransactionId()); + + if (recheckIndexes) + { + list_free(recheckIndexes); + goto vlock; + } + + /* since there was no insertion conflict, we're done */ + } + } } if (canSetTag) @@ -399,7 +537,8 @@ ldelete:; estate->es_output_cid, estate->es_crosscheck_snapshot, true /* wait for commit */ , - &hufd); + &hufd, + false); switch (result) { case HeapTupleSelfUpdated: @@ -768,7 +907,7 @@ lreplace:; */ if (resultRelInfo->ri_NumIndices > 0 && !HeapTupleIsHeapOnly(tuple)) recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self), - estate); + estate, false, InvalidOid); } if (canSetTag) @@ -852,6 +991,7 @@ ExecModifyTable(ModifyTableState *node) { EState *estate = node->ps.state; CmdType operation = node->operation; + SpecCmd spec = node->spec; ResultRelInfo *saved_resultRelInfo; ResultRelInfo *resultRelInfo; PlanState *subplanstate; @@ -1022,7 +1162,8 @@ ExecModifyTable(ModifyTableState *node) switch (operation) { case CMD_INSERT: - slot = ExecInsert(slot, planSlot, estate, node->canSetTag); + slot = ExecInsert(slot, planSlot, node->arbiterIndex, spec, + estate, node->canSetTag); break; case CMD_UPDATE: slot = ExecUpdate(tupleid, oldtuple, slot, planSlot, @@ -1097,6 +1238,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) mtstate->resultRelInfo = estate->es_result_relations + node->resultRelIndex; mtstate->mt_arowmarks = (List **) palloc0(sizeof(List *) * nplans); mtstate->mt_nplans = nplans; + mtstate->spec = node->spec; /* set up epqstate with dummy subplan data for the moment */ EvalPlanQualInit(&mtstate->mt_epqstate, estate, NULL, NIL, node->epqParam); @@ -1135,7 +1277,9 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) if (resultRelInfo->ri_RelationDesc->rd_rel->relhasindex && operation != CMD_DELETE && resultRelInfo->ri_IndexRelationDescs == NULL) - ExecOpenIndices(resultRelInfo); + ExecOpenIndices(resultRelInfo, mtstate->spec != SPEC_NONE); + + mtstate->arbiterIndex = node->arbiterIndex; /* Now init the plan for this result rel */ estate->es_result_relation_info = resultRelInfo; diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 029761e..f8a1514 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -180,6 +180,8 @@ _copyModifyTable(const ModifyTable *from) COPY_NODE_FIELD(resultRelations); COPY_SCALAR_FIELD(resultRelIndex); COPY_NODE_FIELD(plans); + COPY_SCALAR_FIELD(spec); + COPY_SCALAR_FIELD(arbiterIndex); COPY_NODE_FIELD(withCheckOptionLists); COPY_NODE_FIELD(returningLists); COPY_NODE_FIELD(fdwPrivLists); @@ -2128,6 +2130,30 @@ _copyWithClause(const WithClause *from) return newnode; } +static InferClause * +_copyInferClause(const InferClause *from) +{ + InferClause *newnode = makeNode(InferClause); + + COPY_NODE_FIELD(indexElems); + COPY_NODE_FIELD(whereClause); + COPY_LOCATION_FIELD(location); + + return newnode; +} + +static ConflictClause * +_copyConflictClause(const ConflictClause *from) +{ + ConflictClause *newnode = makeNode(ConflictClause); + + COPY_SCALAR_FIELD(specclause); + COPY_NODE_FIELD(infer); + COPY_LOCATION_FIELD(location); + + return newnode; +} + static CommonTableExpr * _copyCommonTableExpr(const CommonTableExpr *from) { @@ -2545,6 +2571,9 @@ _copyQuery(const Query *from) COPY_NODE_FIELD(jointree); COPY_NODE_FIELD(targetList); COPY_NODE_FIELD(withCheckOptions); + COPY_SCALAR_FIELD(specClause); + COPY_NODE_FIELD(arbiterExpr); + COPY_NODE_FIELD(arbiterWhere); COPY_NODE_FIELD(returningList); COPY_NODE_FIELD(groupClause); COPY_NODE_FIELD(havingQual); @@ -2568,6 +2597,7 @@ _copyInsertStmt(const InsertStmt *from) COPY_NODE_FIELD(relation); COPY_NODE_FIELD(cols); COPY_NODE_FIELD(selectStmt); + COPY_NODE_FIELD(confClause); COPY_NODE_FIELD(returningList); COPY_NODE_FIELD(withClause); @@ -4729,6 +4759,12 @@ copyObject(const void *from) case T_WithClause: retval = _copyWithClause(from); break; + case T_InferClause: + retval = _copyInferClause(from); + break; + case T_ConflictClause: + retval = _copyConflictClause(from); + break; case T_CommonTableExpr: retval = _copyCommonTableExpr(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 190e50a..c86bafc 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -868,6 +868,9 @@ _equalQuery(const Query *a, const Query *b) COMPARE_NODE_FIELD(jointree); COMPARE_NODE_FIELD(targetList); COMPARE_NODE_FIELD(withCheckOptions); + COMPARE_SCALAR_FIELD(specClause); + COMPARE_NODE_FIELD(arbiterExpr); + COMPARE_NODE_FIELD(arbiterWhere); COMPARE_NODE_FIELD(returningList); COMPARE_NODE_FIELD(groupClause); COMPARE_NODE_FIELD(havingQual); @@ -889,6 +892,7 @@ _equalInsertStmt(const InsertStmt *a, const InsertStmt *b) COMPARE_NODE_FIELD(relation); COMPARE_NODE_FIELD(cols); COMPARE_NODE_FIELD(selectStmt); + COMPARE_NODE_FIELD(confClause); COMPARE_NODE_FIELD(returningList); COMPARE_NODE_FIELD(withClause); @@ -2420,6 +2424,26 @@ _equalWithClause(const WithClause *a, const WithClause *b) } static bool +_equalInferClause(const InferClause *a, const InferClause *b) +{ + COMPARE_NODE_FIELD(indexElems); + COMPARE_NODE_FIELD(whereClause); + COMPARE_LOCATION_FIELD(location); + + return true; +} + +static bool +_equalConflictClause(const ConflictClause *a, const ConflictClause *b) +{ + COMPARE_SCALAR_FIELD(specclause); + COMPARE_NODE_FIELD(infer); + COMPARE_LOCATION_FIELD(location); + + return true; +} + +static bool _equalCommonTableExpr(const CommonTableExpr *a, const CommonTableExpr *b) { COMPARE_STRING_FIELD(ctename); @@ -3152,6 +3176,12 @@ equal(const void *a, const void *b) case T_WithClause: retval = _equalWithClause(a, b); break; + case T_InferClause: + retval = _equalInferClause(a, b); + break; + case T_ConflictClause: + retval = _equalConflictClause(a, b); + break; case T_CommonTableExpr: retval = _equalCommonTableExpr(a, b); break; diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index d6f1f5b..44efc95 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -1484,6 +1484,12 @@ exprLocation(const Node *expr) case T_WithClause: loc = ((const WithClause *) expr)->location; break; + case T_InferClause: + loc = ((const InferClause *) expr)->location; + break; + case T_ConflictClause: + loc = ((const ConflictClause *) expr)->location; + break; case T_CommonTableExpr: loc = ((const CommonTableExpr *) expr)->location; break; @@ -1968,6 +1974,10 @@ query_tree_walker(Query *query, return true; if (walker((Node *) query->withCheckOptions, context)) return true; + if (walker((Node *) query->arbiterExpr, context)) + return true; + if (walker(query->arbiterWhere, context)) + return true; if (walker((Node *) query->returningList, context)) return true; if (walker((Node *) query->jointree, context)) @@ -2709,6 +2719,8 @@ query_tree_mutator(Query *query, MUTATE(query->targetList, query->targetList, List *); MUTATE(query->withCheckOptions, query->withCheckOptions, List *); + MUTATE(query->arbiterExpr, query->arbiterExpr, List *); + MUTATE(query->arbiterWhere, query->arbiterWhere, Node *); MUTATE(query->returningList, query->returningList, List *); MUTATE(query->jointree, query->jointree, FromExpr *); MUTATE(query->setOperations, query->setOperations, Node *); @@ -2978,6 +2990,8 @@ raw_expression_tree_walker(Node *node, return true; if (walker(stmt->selectStmt, context)) return true; + if (walker(stmt->confClause, context)) + return true; if (walker(stmt->returningList, context)) return true; if (walker(stmt->withClause, context)) @@ -3217,6 +3231,23 @@ raw_expression_tree_walker(Node *node, break; case T_WithClause: return walker(((WithClause *) node)->ctes, context); + + case T_InferClause: + { + InferClause *stmt = (InferClause *) node; + + if (walker(stmt->indexElems, context)) + return true; + if (walker(stmt->whereClause, context)) + return true; + } + case T_ConflictClause: + { + ConflictClause *stmt = (ConflictClause *) node; + + if (walker(stmt->infer, context)) + return true; + } case T_CommonTableExpr: return walker(((CommonTableExpr *) node)->ctequery, context); default: diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 385b289..61b98c7 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -332,6 +332,8 @@ _outModifyTable(StringInfo str, const ModifyTable *node) WRITE_NODE_FIELD(resultRelations); WRITE_INT_FIELD(resultRelIndex); WRITE_NODE_FIELD(plans); + WRITE_ENUM_FIELD(spec, SpecType); + WRITE_OID_FIELD(arbiterIndex); WRITE_NODE_FIELD(withCheckOptionLists); WRITE_NODE_FIELD(returningLists); WRITE_NODE_FIELD(fdwPrivLists); @@ -2314,6 +2316,9 @@ _outQuery(StringInfo str, const Query *node) WRITE_NODE_FIELD(jointree); WRITE_NODE_FIELD(targetList); WRITE_NODE_FIELD(withCheckOptions); + WRITE_ENUM_FIELD(specClause, SpecType); + WRITE_NODE_FIELD(arbiterExpr); + WRITE_NODE_FIELD(arbiterWhere); WRITE_NODE_FIELD(returningList); WRITE_NODE_FIELD(groupClause); WRITE_NODE_FIELD(havingQual); diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 563209c..e492ef6 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -214,6 +214,9 @@ _readQuery(void) READ_NODE_FIELD(jointree); READ_NODE_FIELD(targetList); READ_NODE_FIELD(withCheckOptions); + READ_ENUM_FIELD(specClause, SpecCmd); + READ_NODE_FIELD(arbiterExpr); + READ_NODE_FIELD(arbiterWhere); READ_NODE_FIELD(returningList); READ_NODE_FIELD(groupClause); READ_NODE_FIELD(havingQual); diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index 49ab366..d7ea575 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -4113,3 +4113,69 @@ string_to_const(const char *str, Oid datatype) return makeConst(datatype, -1, collation, constlen, conval, false, false); } + +/* + * plan_speculative_use_index + * Use the planner to decide speculative insertion arbiter index + * + * Among indexes on target of INSERT ... ON CONFLICT, decide which index to use + * to arbitrate taking alternative path. This should be called infrequently in + * practice, because its unusual for more than one index to be available that + * can satisfy a user-specified unique index inference specification. + * + * Note: caller had better already hold some type of lock on the table. + */ +Oid +plan_speculative_use_index(PlannerInfo *root, int widthTarget, List *indexList) +{ + IndexOptInfo *indexInfo; + RelOptInfo *rel; + IndexPath *cheapest; + IndexPath *indexScanPath; + ListCell *lc; + + /* Set up RTE/RelOptInfo arrays if needed */ + if (!root->simple_rel_array) + setup_simple_rel_arrays(root); + + /* Build RelOptInfo */ + rel = build_simple_rel(root, root->parse->resultRelation, RELOPT_BASEREL); + + /* + * Rather than doing all the pushups that would be needed to use + * set_baserel_size_estimates, just do a quick hack for rows. Caller + * provides width. + */ + rel->rows = rel->tuples; + rel->width = widthTarget; + + root->total_table_pages = rel->pages; + + /* Locate cheapest IndexOptInfo for the target index */ + cheapest = NULL; + + foreach(lc, rel->indexlist) + { + indexInfo = (IndexOptInfo *) lfirst(lc); + + if (!list_member_oid(indexList, indexInfo->indexoid)) + continue; + + /* Estimate the cost of index scan */ + indexScanPath = create_index_path(root, indexInfo, + NIL, NIL, NIL, NIL, NIL, + ForwardScanDirection, false, + NULL, 1.0); + + if (!cheapest || compare_fractional_path_costs(&cheapest->path, + &indexScanPath->path, + DEFAULT_RANGE_INEQ_SEL) > 0) + cheapest = indexScanPath; + + } + + if (cheapest) + return cheapest->indexinfo->indexoid; + + return InvalidOid; +} diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index cb69c03..47fe29c 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -4815,7 +4815,7 @@ make_modifytable(PlannerInfo *root, Index nominalRelation, List *resultRelations, List *subplans, List *withCheckOptionLists, List *returningLists, - List *rowMarks, int epqParam) + List *rowMarks, SpecCmd spec, int epqParam) { ModifyTable *node = makeNode(ModifyTable); Plan *plan = &node->plan; @@ -4865,6 +4865,8 @@ make_modifytable(PlannerInfo *root, node->resultRelations = resultRelations; node->resultRelIndex = -1; /* will be set correctly in setrefs.c */ node->plans = subplans; + node->spec = spec; + node->arbiterIndex = InvalidOid; node->withCheckOptionLists = withCheckOptionLists; node->returningLists = returningLists; node->rowMarks = rowMarks; @@ -4917,6 +4919,15 @@ make_modifytable(PlannerInfo *root, } node->fdwPrivLists = fdw_private_list; + /* + * If a set of unique index inference expressions was provided (for + * INSERT...ON CONFLICT), then infer appropriate unique index (or throw an + * error if none is available). It's possible that there will be a costing + * step in the event of having to choose between multiple alternatives. + */ + if (root->parse->arbiterExpr) + node->arbiterIndex = infer_unique_index(root); + return node; } diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 876a87f..6931e1e 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -612,6 +612,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse, withCheckOptionLists, returningLists, rowMarks, + parse->specClause, SS_assign_special_param(root)); } } @@ -1072,6 +1073,7 @@ inheritance_planner(PlannerInfo *root) withCheckOptionLists, returningLists, rowMarks, + parse->specClause, SS_assign_special_param(root)); } diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 313a5c1..a179b0c 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -31,6 +31,7 @@ #include "nodes/makefuncs.h" #include "optimizer/clauses.h" #include "optimizer/cost.h" +#include "optimizer/paths.h" #include "optimizer/plancat.h" #include "optimizer/predtest.h" #include "optimizer/prep.h" @@ -394,6 +395,224 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, } /* + * infer_unique_index - + * Retrieves unique index to arbitrate speculative insertion. + * + * Uses user-supplied inference clause expressions and predicate to match a + * unique index from those defined and ready on the heap relation (target). An + * exact match is required on columns/expressions (although they can appear in + * any order). However, the predicate given by the user need only restrict + * insertion to a subset of some part of the table covered by some particular + * unique index (in particular, a partial unique index) in order to be + * inferred. + * + * The implementation does not consider which B-Tree operator class any + * particular available unique index uses. In particular, there is no system + * dependency on the default operator class for the purposes of inference. + * This should be okay, since by convention non-default opclasses only + * introduce alternative sort orders, not alternative notions of equality + * (there are only trivial known exceptions to this convention, where "equals" + * operator of a type's opclasses do not match across opclasses, exceptions + * that exist precisely to discourage user code from using the divergent + * opclass). Even if we assume that a type could usefully have multiple + * alternative concepts of equality, surely the definition actually implied by + * the operator class of actually indexed attributes is pertinent. However, + * this is a bit of a wart, because strictly speaking there is leeway for a + * query to be interpreted in deference to available unique indexes, and + * indexes are traditionally only an implementation detail. It hardly seems + * worth it to waste cycles on this corner case, though. + * + * This logic somewhat mirrors get_relation_info(). This process is not + * deferred to a get_relation_info() call while planning because there may not + * be any such call. + */ +Oid +infer_unique_index(PlannerInfo *root) +{ + Query *parse = root->parse; + Relation relation; + Oid relationObjectId; + Bitmapset *plainAttrs = NULL; + List *candidates = NIL; + ListCell *l; + List *indexList; + + Assert(parse->specClause == SPEC_IGNORE); + + /* + * We need not lock the relation since it was already locked, either by + * the rewriter or when expand_inherited_rtentry() added it to the query's + * rangetable. + */ + relationObjectId = rt_fetch(parse->resultRelation, parse->rtable)->relid; + + relation = heap_open(relationObjectId, NoLock); + + /* + * Match expressions appearing in clause (if any) with index definition + */ + foreach(l, parse->arbiterExpr) + { + Expr *elem; + Var *var; + int attno; + + elem = (Expr *) lfirst(l); + + /* + * Parse analysis of inference elements performs full parse analysis + * of Vars, even for non-expression indexes (in contrast with utility + * command related use of IndexElem). However, indexes are cataloged + * with simple attribute numbers for non-expression indexes. + * Therefore, we must build a compatible bms representation here. + */ + if (!IsA(elem, Var)) + continue; + + var = (Var *) elem; + attno = var->varattno; + + if (attno < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("system columns may not appear in unique index inference specification"))); + else if (attno == 0) + elog(ERROR, "whole row unique index inference specifications are not valid"); + + plainAttrs = bms_add_member(plainAttrs, attno); + } + + indexList = RelationGetIndexList(relation); + + /* + * Using that representation, iterate through the list of indexes on the + * target relation to try and find a match + */ + foreach(l, indexList) + { + Oid indexoid = lfirst_oid(l); + Relation idxRel; + Form_pg_index idxForm; + Bitmapset *indexedPlainAttrs = NULL; + List *idxExprs; + List *predExprs; + List *whereExplicit; + AttrNumber natt; + ListCell *e; + + /* + * Extract info from the relation descriptor for the index. We know + * that this is a target, so get lock type it is known will ultimately + * be required by the executor. + * + * Let executor complain about !indimmediate case directly. + */ + idxRel = index_open(indexoid, RowExclusiveLock); + idxForm = idxRel->rd_index; + + if (!idxForm->indisunique || + !IndexIsValid(idxForm)) + goto next; + + /* + * If the index is valid, but cannot yet be used, ignore it. See + * src/backend/access/heap/README.HOT for discussion. + */ + if (idxForm->indcheckxmin && + !TransactionIdPrecedes(HeapTupleHeaderGetXmin(idxRel->rd_indextuple->t_data), + TransactionXmin)) + goto next; + + /* Check in detail if the clause attributes/expressions match */ + for (natt = 0; natt < idxForm->indnatts; natt++) + { + int attno = idxRel->rd_index->indkey.values[natt]; + + if (attno < 0) + elog(ERROR, "system column in index"); + + if (attno != 0) + indexedPlainAttrs = bms_add_member(indexedPlainAttrs, attno); + } + + /* + * Since expressions were made unique during parse analysis, it's + * evident that we cannot proceed with this index if the number of + * attributes (plain or expression) does not match exactly. This + * precludes support for unique indexes created with redundantly + * referenced columns (which are not forbidden by CREATE INDEX), but + * this seems inconsequential. + */ + if (list_length(parse->arbiterExpr) != idxForm->indnatts) + goto next; + + idxExprs = RelationGetIndexExpressions(idxRel); + + /* + * Match expressions appearing in clause (if any) with index + * definition + */ + foreach(e, parse->arbiterExpr) + { + Expr *elem = (Expr *) lfirst(e); + + /* Plain Vars were already separately accounted for */ + if (IsA(elem, Var)) + continue; + + if (!list_member(idxExprs, elem)) + goto next; + } + + /* Non-expression attributes (if any) must match */ + if (!bms_equal(indexedPlainAttrs, plainAttrs)) + goto next; + + /* + * Any user-supplied ON CONFLICT unique index inference WHERE clause + * need only be implied by the cataloged index definitions predicate + */ + predExprs = RelationGetIndexPredicate(idxRel); + whereExplicit = make_ands_implicit((Expr *) parse->arbiterWhere); + + if (!predicate_implied_by(predExprs, whereExplicit)) + goto next; + + candidates = lappend_oid(candidates, idxForm->indexrelid); +next: + index_close(idxRel, NoLock); + } + + list_free(indexList); + heap_close(relation, NoLock); + + /* + * In the common case where there is only a single candidate unique index, + * there is clearly no point in building index paths to determine which is + * cheapest + */ + if (list_length(candidates) == 1) + { + return linitial_oid(candidates); + } + else if (candidates == NIL) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT"))); + } + else + { + int width = get_relation_data_width(relationObjectId, NULL); + + /* Deduce the least expensive unique index through costing */ + return plan_speculative_use_index(root, width, candidates); + } + + return InvalidOid; /* keep compiler quiet */ +} + +/* * estimate_rel_size - estimate # pages and # tuples in a table or index * * We also estimate the fraction of the pages that are marked all-visible in diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 4a5a520..a49d980 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -387,6 +387,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) /* done building the range table and jointree */ qry->rtable = pstate->p_rtable; qry->jointree = makeFromExpr(pstate->p_joinlist, qual); + qry->specClause = SPEC_NONE; qry->hasSubLinks = pstate->p_hasSubLinks; qry->hasWindowFuncs = pstate->p_hasWindowFuncs; @@ -408,6 +409,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) { Query *qry = makeNode(Query); SelectStmt *selectStmt = (SelectStmt *) stmt->selectStmt; + SpecCmd spec = stmt->confClause ? stmt->confClause->specclause : SPEC_NONE; List *exprList = NIL; bool isGeneralSelect; List *sub_rtable; @@ -741,12 +743,13 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) } /* - * If we have a RETURNING clause, we need to add the target relation to - * the query namespace before processing it, so that Var references in - * RETURNING will work. Also, remove any namespace entries added in a - * sub-SELECT or VALUES list. + * If we have a RETURNING clause, or there are attributes used as the + * condition on which to take an alternative ON CONFLICT path, we need to + * add the target relation to the query namespace before processing it, so + * that Var references in RETURNING/the alternative path key will work. + * Also, remove any namespace entries added in a sub-SELECT or VALUES list. */ - if (stmt->returningList) + if (stmt->returningList || stmt->confClause) { pstate->p_namespace = NIL; addRTEtoQuery(pstate, pstate->p_target_rangetblentry, @@ -758,9 +761,22 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) /* done building the range table and jointree */ qry->rtable = pstate->p_rtable; qry->jointree = makeFromExpr(pstate->p_joinlist, NULL); - + qry->specClause = spec; qry->hasSubLinks = pstate->p_hasSubLinks; + if (stmt->confClause) + { + /* + * Perform parse analysis of arbiter columns/expressions. These are + * later used to infer a unique index which arbitrates whether or not + * to take the alternative ON CONFLICT path (i.e. whether or not to + * INSERT or take the alternative path in respect of each slot proposed + * for insertion). + */ + transformConflictClause(pstate, stmt->confClause, &qry->arbiterExpr, + &qry->arbiterWhere); + } + assign_query_collations(pstate, qry); return qry; @@ -1006,6 +1022,7 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt) qry->rtable = pstate->p_rtable; qry->jointree = makeFromExpr(pstate->p_joinlist, qual); + qry->specClause = SPEC_NONE; qry->hasSubLinks = pstate->p_hasSubLinks; qry->hasWindowFuncs = pstate->p_hasWindowFuncs; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 873ca79..5079562 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -217,6 +217,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); RangeVar *range; IntoClause *into; WithClause *with; + InferClause *infer; + ConflictClause *conf; A_Indices *aind; ResTarget *target; struct PrivTarget *privtarget; @@ -416,6 +418,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type SeqOptElem %type insert_rest +%type opt_conf_expr +%type opt_on_conflict %type generic_set set_rest set_rest_more generic_reset reset_rest SetResetClause FunctionSetResetClause @@ -555,8 +559,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); CACHE CALLED CASCADE CASCADED CASE CAST CATALOG_P CHAIN CHAR_P CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT - COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS - CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE + COMMITTED CONCURRENTLY CONFIGURATION CONFLICT CONNECTION CONSTRAINT + CONSTRAINTS CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE CROSS CSV CURRENT_P CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE @@ -576,7 +580,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); HANDLER HAVING HEADER_P HOLD HOUR_P - IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P + IDENTITY_P IF_P IGNORE_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION @@ -657,6 +661,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %nonassoc IS ISNULL NOTNULL /* IS sets precedence for IS NULL, etc */ %nonassoc '<' '>' '=' LESS_EQUALS GREATER_EQUALS NOT_EQUALS %nonassoc BETWEEN IN_P LIKE ILIKE SIMILAR NOT_LA +%nonassoc DISTINCT +%nonassoc ON %nonassoc ESCAPE /* ESCAPE must be just above LIKE/ILIKE/SIMILAR */ %nonassoc OVERLAPS %left POSTFIXOP /* dummy for postfix Op rules */ @@ -9349,10 +9355,12 @@ DeallocateStmt: DEALLOCATE name *****************************************************************************/ InsertStmt: - opt_with_clause INSERT INTO qualified_name insert_rest returning_clause + opt_with_clause INSERT INTO qualified_name insert_rest + opt_on_conflict returning_clause { $5->relation = $4; - $5->returningList = $6; + $5->confClause = $6; + $5->returningList = $7; $5->withClause = $1; $$ = (Node *) $5; } @@ -9397,6 +9405,34 @@ insert_column_item: } ; +opt_on_conflict: + ON CONFLICT opt_conf_expr IGNORE_P + { + $$ = makeNode(ConflictClause); + $$->specclause = SPEC_IGNORE; + $$->infer = $3; + $$->location = @1; + } + | /*EMPTY*/ + { + $$ = NULL; + } + ; + +opt_conf_expr: + '(' index_params where_clause ')' + { + $$ = makeNode(InferClause); + $$->indexElems = $2; + $$->whereClause = $3; + $$->location = @1; + } + | /*EMPTY*/ + { + $$ = NULL; + } + ; + returning_clause: RETURNING target_list { $$ = $2; } | /* EMPTY */ { $$ = NIL; } @@ -13280,6 +13316,7 @@ unreserved_keyword: | COMMIT | COMMITTED | CONFIGURATION + | CONFLICT | CONNECTION | CONSTRAINTS | CONTENT_P @@ -13339,6 +13376,7 @@ unreserved_keyword: | HOUR_P | IDENTITY_P | IF_P + | IGNORE_P | IMMEDIATE | IMMUTABLE | IMPLICIT_P diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 8d90b50..029288b 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -16,6 +16,7 @@ #include "postgres.h" #include "access/heapam.h" +#include "catalog/catalog.h" #include "catalog/heap.h" #include "catalog/pg_type.h" #include "commands/defrem.h" @@ -75,6 +76,8 @@ static TargetEntry *findTargetlistEntrySQL99(ParseState *pstate, Node *node, List **tlist, ParseExprKind exprKind); static int get_matching_location(int sortgroupref, List *sortgrouprefs, List *exprs); +static List *resolve_unique_index_expr(ParseState *pstate, InferClause * infer, + Relation heapRel); static List *addTargetToGroupList(ParseState *pstate, TargetEntry *tle, List *grouplist, List *targetlist, int location, bool resolveUnknown); @@ -2167,6 +2170,163 @@ get_matching_location(int sortgroupref, List *sortgrouprefs, List *exprs) } /* + * resolve_unique_index_expr + * Infer a unique index from a list of indexElems, for ON + * CONFLICT clause + * + * Perform parse analysis of expressions and columns appearing within ON + * CONFLICT clause. During planning, the returned list of expressions is used + * to infer which unique index to use. + */ +static List * +resolve_unique_index_expr(ParseState *pstate, InferClause *infer, + Relation heapRel) +{ + List *clauseexprs = NIL; + ListCell *l; + + if (heapRel->rd_rel->relkind != RELKIND_RELATION) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("relation \"%s\" is not an ordinary table", + RelationGetRelationName(heapRel)), + errhint("Only ordinary tables are accepted as targets when a unique index is inferred for ON CONFLICT."))); + + if (heapRel->rd_rel->relhassubclass) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("relation \"%s\" has inheritance children", + RelationGetRelationName(heapRel)), + errhint("Only heap relations without inheritance children are accepted as targets when a unique index is inferred for ON CONFLICT."))); + + foreach(l, infer->indexElems) + { + IndexElem *ielem = (IndexElem *) lfirst(l); + Node *trans; + + /* + * Raw grammar re-uses CREATE INDEX infrastructure for unique index + * inference clause, and so will accept opclasses by name and so on. + * Reject these here explicitly. + */ + if (ielem->ordering != SORTBY_DEFAULT || + ielem->nulls_ordering != SORTBY_NULLS_DEFAULT) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("ON CONFLICT does not accept ordering or NULLS FIRST/LAST specifications"), + errhint("These factors do not affect uniqueness of indexed datums."), + parser_errposition(pstate, + exprLocation((Node *) infer)))); + + if (ielem->collation != NIL) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("ON CONFLICT collation specification is unnecessary"), + errhint("Collations do not affect uniqueness of collatable datums."), + parser_errposition(pstate, + exprLocation((Node *) infer)))); + + if (ielem->opclass != NIL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("ON CONFLICT cannot accept non-default operator class specifications"), + parser_errposition(pstate, + exprLocation((Node *) infer)))); + + if (!ielem->expr) + { + /* Simple index attribute */ + ColumnRef *n; + + /* + * Grammar won't have built raw expression for us in event of plain + * column reference. Create one directly, and perform expression + * transformation, which seems better principled than simply + * propagating catalog-style simple attribute numbers. For + * example, it means the Var is marked for SELECT privileges, which + * speculative insertion requires. Planner expects this, and + * performs its own normalization for the purposes of matching + * against pg_index. + */ + n = makeNode(ColumnRef); + n->fields = list_make1(makeString(ielem->name)); + /* Location is approximately that of inference specification */ + n->location = infer->location; + trans = (Node *) n; + } + else + { + /* Do parse transformation of the raw expression */ + trans = (Node *) ielem->expr; + } + + /* + * transformExpr() should have already rejected subqueries, + * aggregates, and window functions, based on the EXPR_KIND_ for an + * index expression. Expressions returning sets won't have been + * rejected, but don't bother doing so here; there should be no + * available expression unique index to match any such expression + * against anyway. + */ + trans = transformExpr(pstate, trans, EXPR_KIND_INDEX_EXPRESSION); + /* Save in list of transformed expressions */ + clauseexprs = list_append_unique(clauseexprs, trans); + } + + return clauseexprs; +} + +/* + * transformConflictClauseExpr - + * transform expressions of ON CONFLICT. + * + * Transformed expressions used to infer one unique index relation to serve as + * an ON CONFLICT arbiter. Partial unique indexes may be inferred using WHERE + * clause from inference specification clause. + */ +void +transformConflictClause(ParseState *pstate, ConflictClause *confClause, + List **arbiterExpr, Node **arbiterWhere) +{ + InferClause *infer = confClause->infer; + + /* This obviates the need for historic snapshot support */ + if (IsCatalogRelation(pstate->p_target_relation)) + elog(ERROR, "ON CONFLICT not supported with catalog relations"); + + /* + * If there is no inference clause, this might be an updatable view, which + * are supported by ON CONFLICT IGNORE (without columns/ expressions + * specified to infer a unique index from). It might also be a relation + * with inheritance children, which would also make proceeding with + * inference fail. + */ + if (infer) + { + *arbiterExpr = resolve_unique_index_expr(pstate, infer, + pstate->p_target_relation); + + /* + * Handling inference WHERE clause (for partial unique index + * inference) + */ + if (infer->whereClause) + *arbiterWhere = transformExpr(pstate, infer->whereClause, + EXPR_KIND_INDEX_PREDICATE); + } + + /* + * It's convenient to form a list of expressions based on the + * representation used by CREATE INDEX, since the same restrictions are + * appropriate (on subqueries and so on). However, from here on, the + * handling of those expressions is identical to ordinary optimizable + * statements. In particular, assign_query_collations() can be trusted to + * do the right thing with the post parse analysis query tree inference + * clause representation. + */ +} + +/* * addTargetToSortList * If the given targetlist entry isn't already in the SortGroupClause * list, add it to the end of the list, using the given sort ordering diff --git a/src/backend/replication/logical/decode.c b/src/backend/replication/logical/decode.c index eb7293f..e2c1a87 100644 --- a/src/backend/replication/logical/decode.c +++ b/src/backend/replication/logical/decode.c @@ -538,7 +538,11 @@ DecodeInsert(LogicalDecodingContext *ctx, XLogRecordBuffer *buf) return; change = ReorderBufferGetChange(ctx->reorder); - change->action = REORDER_BUFFER_CHANGE_INSERT; + if (!(xlrec->flags & XLOG_HEAP_SPECULATIVE_TUPLE)) + change->action = REORDER_BUFFER_CHANGE_INSERT; + else + change->action = REORDER_BUFFER_CHANGE_INTERNAL_INSERT; + memcpy(&change->data.tp.relnode, &target_node, sizeof(RelFileNode)); if (xlrec->flags & XLOG_HEAP_CONTAINS_NEW_TUPLE) @@ -629,7 +633,10 @@ DecodeDelete(LogicalDecodingContext *ctx, XLogRecordBuffer *buf) return; change = ReorderBufferGetChange(ctx->reorder); - change->action = REORDER_BUFFER_CHANGE_DELETE; + if (!(xlrec->flags & XLOG_HEAP_SPECULATIVE_TUPLE)) + change->action = REORDER_BUFFER_CHANGE_DELETE; + else + change->action = REORDER_BUFFER_CHANGE_INTERNAL_DELETE; memcpy(&change->data.tp.relnode, &target_node, sizeof(RelFileNode)); diff --git a/src/backend/replication/logical/reorderbuffer.c b/src/backend/replication/logical/reorderbuffer.c index 20bb3b7..e8ad2ee 100644 --- a/src/backend/replication/logical/reorderbuffer.c +++ b/src/backend/replication/logical/reorderbuffer.c @@ -401,6 +401,8 @@ ReorderBufferReturnChange(ReorderBuffer *rb, ReorderBufferChange *change) case REORDER_BUFFER_CHANGE_INSERT: case REORDER_BUFFER_CHANGE_UPDATE: case REORDER_BUFFER_CHANGE_DELETE: + case REORDER_BUFFER_CHANGE_INTERNAL_INSERT: + case REORDER_BUFFER_CHANGE_INTERNAL_DELETE: if (change->data.tp.newtuple) { ReorderBufferReturnTupleBuf(rb, change->data.tp.newtuple); @@ -1314,6 +1316,7 @@ ReorderBufferCommit(ReorderBuffer *rb, TransactionId xid, PG_TRY(); { ReorderBufferChange *change; + ReorderBufferChange *peekchange = NULL; if (using_subtxn) BeginInternalSubTransaction("replay"); @@ -1323,16 +1326,26 @@ ReorderBufferCommit(ReorderBuffer *rb, TransactionId xid, rb->begin(rb, txn); iterstate = ReorderBufferIterTXNInit(rb, txn); - while ((change = ReorderBufferIterTXNNext(rb, iterstate)) != NULL) + while ((change = peekchange ? peekchange : + ReorderBufferIterTXNNext(rb, iterstate)) != NULL) { Relation relation = NULL; Oid reloid; + /* Forget about previous peek ahead */ + if (peekchange) + peekchange = NULL; + else + Assert(change->action != + REORDER_BUFFER_CHANGE_INTERNAL_DELETE); + switch (change->action) { case REORDER_BUFFER_CHANGE_INSERT: case REORDER_BUFFER_CHANGE_UPDATE: case REORDER_BUFFER_CHANGE_DELETE: + case REORDER_BUFFER_CHANGE_INTERNAL_INSERT: + case REORDER_BUFFER_CHANGE_INTERNAL_DELETE: Assert(snapshot_now); reloid = RelidByRelfilenode(change->data.tp.relnode.spcNode, @@ -1374,7 +1387,65 @@ ReorderBufferCommit(ReorderBuffer *rb, TransactionId xid, else if (!IsToastRelation(relation)) { ReorderBufferToastReplace(rb, txn, relation, change); - rb->apply_change(rb, txn, relation, change); + + /* + * Kludge: Speculative insertion occasionally + * makes use of "super deletion" -- an + * implementation defined delete of a speculatively + * inserted tuple. Neither the super deletion, nor + * the insertion (which must be the prior record + * type) are included in the final assembly when + * the tuple was super-deleted. Otherwise, an + * ordinary insertion is assembled. + */ + if (change->action == REORDER_BUFFER_CHANGE_INTERNAL_INSERT) + { + /* + * Need to ensure the memory used by promise + * tuple isn't freed till we're done verifying + * that there is no super deletion that + * immediately follows. Otherwise it could get + * freed/reused while restoring spooled data + * from disk. + */ + dlist_delete(&change->node); + peekchange = ReorderBufferIterTXNNext(rb, iterstate); + if (!peekchange || peekchange->action != + REORDER_BUFFER_CHANGE_INTERNAL_DELETE) + { + /* Report as proper insert to client */ + change->action = REORDER_BUFFER_CHANGE_INSERT; + rb->apply_change(rb, txn, relation, + change); + } + else if (peekchange) + { + Assert(RelFileNodeEquals(change->data.tp.relnode, + peekchange->data.tp.relnode)); + } + + ReorderBufferReturnChange(rb, change); + } + else if (change->action == + REORDER_BUFFER_CHANGE_INTERNAL_DELETE) + { + /* + * The REORDER_BUFFER_CHANGE_INTERNAL_INSERT + * case makes an assumption that + * REORDER_BUFFER_CHANGE_INTERNAL_DELETE + * changes immediately follows reliably iff a + * speculatively inserted tuple was actually + * super-deleted. + */ + } + else + { + /* + * Handle non-speculative insertion related + * changes + */ + rb->apply_change(rb, txn, relation, change); + } /* * Only clear reassembled toast chunks if we're @@ -2003,6 +2074,10 @@ ReorderBufferSerializeChange(ReorderBuffer *rb, ReorderBufferTXN *txn, case REORDER_BUFFER_CHANGE_UPDATE: /* fall through */ case REORDER_BUFFER_CHANGE_DELETE: + /* fall through */ + case REORDER_BUFFER_CHANGE_INTERNAL_INSERT: + /* fall through */ + case REORDER_BUFFER_CHANGE_INTERNAL_DELETE: { char *data; ReorderBufferTupleBuf *oldtup, @@ -2258,6 +2333,10 @@ ReorderBufferRestoreChange(ReorderBuffer *rb, ReorderBufferTXN *txn, case REORDER_BUFFER_CHANGE_UPDATE: /* fall through */ case REORDER_BUFFER_CHANGE_DELETE: + /* fall through */ + case REORDER_BUFFER_CHANGE_INTERNAL_INSERT: + /* fall through */ + case REORDER_BUFFER_CHANGE_INTERNAL_DELETE: if (change->data.tp.newtuple) { Size len = offsetof(ReorderBufferTupleBuf, t_data) + diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 9d2c280..40458a0 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -66,7 +66,7 @@ static void markQueryForLocking(Query *qry, Node *jtnode, LockClauseStrength strength, LockWaitPolicy waitPolicy, bool pushedDown); static List *matchLocks(CmdType event, RuleLock *rulelocks, - int varno, Query *parsetree); + int varno, Query *parsetree, bool *hasUpdate); static Query *fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown); static bool view_has_instead_trigger(Relation view, CmdType event); @@ -1288,7 +1288,8 @@ static List * matchLocks(CmdType event, RuleLock *rulelocks, int varno, - Query *parsetree) + Query *parsetree, + bool *hasUpdate) { List *matching_locks = NIL; int nlocks; @@ -1309,6 +1310,9 @@ matchLocks(CmdType event, { RewriteRule *oneLock = rulelocks->rules[i]; + if (oneLock->event == CMD_UPDATE) + *hasUpdate = true; + /* * Suppress ON INSERT/UPDATE/DELETE rules that are disabled or * configured to not fire during the current sessions replication @@ -2953,6 +2957,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events) CmdType event = parsetree->commandType; bool instead = false; bool returning = false; + bool updatableview = false; Query *qual_product = NULL; List *rewritten = NIL; ListCell *lc1; @@ -3035,6 +3040,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events) Relation rt_entry_relation; List *locks; List *product_queries; + bool hasUpdate = false; result_relation = parsetree->resultRelation; Assert(result_relation != 0); @@ -3103,7 +3109,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events) * Collect and apply the appropriate rules. */ locks = matchLocks(event, rt_entry_relation->rd_rules, - result_relation, parsetree); + result_relation, parsetree, &hasUpdate); product_queries = fireRules(parsetree, result_relation, @@ -3152,6 +3158,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events) */ instead = true; returning = true; + updatableview = true; } /* @@ -3232,6 +3239,18 @@ RewriteQuery(Query *parsetree, List *rewrite_events) } } + /* + * Updatable views are supported on a limited basis by ON CONFLICT + * IGNORE (if there is no unique index inference required, speculative + * insertion proceeds). + */ + if (parsetree->specClause != SPEC_NONE && + (product_queries != NIL || hasUpdate) && + !updatableview) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("INSERT with ON CONFLICT clause may not target relation with INSERT or UPDATE rules"))); + heap_close(rt_entry_relation, NoLock); } diff --git a/src/backend/storage/lmgr/lmgr.c b/src/backend/storage/lmgr/lmgr.c index d13a167..65da516 100644 --- a/src/backend/storage/lmgr/lmgr.c +++ b/src/backend/storage/lmgr/lmgr.c @@ -576,6 +576,88 @@ ConditionalXactLockTableWait(TransactionId xid) } /* + * Per-backend final disambiguator of an attempt to insert speculatively. + * + * This may wraparound, but since it is only a final disambiguator (speculative + * waiters also check TID and relfilenode), this is deemed to be acceptable. + * There is only a theoretical, vanishingly small chance of a backend + * spuriously considering that it must wait on another backend's + * end-of-speculative insertion (call to SpeculativeInsertionLockRelease()) + * when that isn't strictly necessary, and even this is likely to be + * inconsequential. At worst, unprincipled deadlocks are not entirely + * eliminated in extreme corner cases. + */ +static uint32 speculativeInsertionToken = 0; + +/* + * SpeculativeInsertionLockAcquire + * + * Insert a lock showing that the given transaction ID is inserting a tuple, + * but hasn't yet decided whether it's going to keep it. The lock can then be + * used to wait for the decision to go ahead with the insertion, or aborting + * it. + * + * The token is used to distinguish multiple insertions by the same + * transaction. It is returned to caller. + */ +uint32 +SpeculativeInsertionLockAcquire(TransactionId xid) +{ + LOCKTAG tag; + + speculativeInsertionToken++; + + /* + * A zero speculative insertion lock indicates no token is held; Don't + * allow the token to overflow to zero + */ + if (speculativeInsertionToken == 0) + speculativeInsertionToken = 1; + + SET_LOCKTAG_SPECULATIVE_INSERTION(tag, xid, speculativeInsertionToken); + + (void) LockAcquire(&tag, ExclusiveLock, false, false); + + return speculativeInsertionToken; +} + +/* + * SpeculativeInsertionLockRelease + * + * Delete the lock showing that the given transaction is speculatively + * inserting a tuple. + */ +void +SpeculativeInsertionLockRelease(TransactionId xid) +{ + LOCKTAG tag; + + SET_LOCKTAG_SPECULATIVE_INSERTION(tag, xid, speculativeInsertionToken); + + LockRelease(&tag, ExclusiveLock, false); +} + +/* + * SpeculativeInsertionWait + * + * Wait for the specified transaction to finish or abort the insertion of a + * tuple. + */ +void +SpeculativeInsertionWait(TransactionId xid, uint32 token) +{ + LOCKTAG tag; + + SET_LOCKTAG_SPECULATIVE_INSERTION(tag, xid, token); + + Assert(TransactionIdIsValid(xid)); + Assert(token != 0); + + (void) LockAcquire(&tag, ShareLock, false, false); + LockRelease(&tag, ShareLock, false); +} + +/* * XactLockTableWaitErrorContextCb * Error context callback for transaction lock waits. */ @@ -873,6 +955,12 @@ DescribeLockTag(StringInfo buf, const LOCKTAG *tag) tag->locktag_field1, tag->locktag_field2); break; + case LOCKTAG_PROMISE_TUPLE: + appendStringInfo(buf, + _("promise tuple with token %u of transaction %u"), + tag->locktag_field2, + tag->locktag_field1); + break; case LOCKTAG_OBJECT: appendStringInfo(buf, _("object %u of class %u of database %u"), diff --git a/src/backend/utils/adt/lockfuncs.c b/src/backend/utils/adt/lockfuncs.c index a1967b69..1056e71 100644 --- a/src/backend/utils/adt/lockfuncs.c +++ b/src/backend/utils/adt/lockfuncs.c @@ -28,6 +28,7 @@ static const char *const LockTagTypeNames[] = { "tuple", "transactionid", "virtualxid", + "promise tuple", "object", "userlock", "advisory" diff --git a/src/backend/utils/time/tqual.c b/src/backend/utils/time/tqual.c index 777f55c..1375adc 100644 --- a/src/backend/utils/time/tqual.c +++ b/src/backend/utils/time/tqual.c @@ -262,6 +262,9 @@ HeapTupleSatisfiesSelf(HeapTuple htup, Snapshot snapshot, Buffer buffer) } } + if (HeapTupleHeaderSuperDeleted(tuple)) + return false; + /* by here, the inserting transaction has committed */ if (tuple->t_infomask & HEAP_XMAX_INVALID) /* xid invalid or aborted */ @@ -360,6 +363,7 @@ HeapTupleSatisfiesToast(HeapTuple htup, Snapshot snapshot, Assert(ItemPointerIsValid(&htup->t_self)); Assert(htup->t_tableOid != InvalidOid); + Assert(!HeapTupleHeaderSuperDeleted(tuple)); if (!HeapTupleHeaderXminCommitted(tuple)) { @@ -446,6 +450,7 @@ HeapTupleSatisfiesUpdate(HeapTuple htup, CommandId curcid, Assert(ItemPointerIsValid(&htup->t_self)); Assert(htup->t_tableOid != InvalidOid); + Assert(!HeapTupleHeaderSuperDeleted(tuple)); if (!HeapTupleHeaderXminCommitted(tuple)) { @@ -726,6 +731,7 @@ HeapTupleSatisfiesDirty(HeapTuple htup, Snapshot snapshot, Assert(htup->t_tableOid != InvalidOid); snapshot->xmin = snapshot->xmax = InvalidTransactionId; + snapshot->speculativeToken = 0; if (!HeapTupleHeaderXminCommitted(tuple)) { @@ -807,6 +813,25 @@ HeapTupleSatisfiesDirty(HeapTuple htup, Snapshot snapshot, } else if (TransactionIdIsInProgress(HeapTupleHeaderGetRawXmin(tuple))) { + RelFileNode rnode; + ForkNumber forkno; + BlockNumber blockno; + + BufferGetTag(buffer, &rnode, &forkno, &blockno); + + /* tuples can only be in the main fork */ + Assert(forkno == MAIN_FORKNUM); + Assert(blockno == ItemPointerGetBlockNumber(&htup->t_self)); + + /* + * Set speculative token. Caller can worry about xmax, since it + * requires a conclusively locked row version, and a concurrent + * update to this tuple is a conflict of its purposes. + */ + if (HeapTupleHeaderIsSpeculative(tuple)) + snapshot->speculativeToken = + BlockIdGetBlockNumber(&tuple->t_ctid.ip_blkid); + snapshot->xmin = HeapTupleHeaderGetRawXmin(tuple); /* XXX shouldn't we fall through to look at xmax? */ return true; /* in insertion by other */ @@ -823,6 +848,9 @@ HeapTupleSatisfiesDirty(HeapTuple htup, Snapshot snapshot, } } + if (HeapTupleHeaderSuperDeleted(tuple)) + return false; + /* by here, the inserting transaction has committed */ if (tuple->t_infomask & HEAP_XMAX_INVALID) /* xid invalid or aborted */ @@ -1022,6 +1050,9 @@ HeapTupleSatisfiesMVCC(HeapTuple htup, Snapshot snapshot, } } + if (HeapTupleHeaderSuperDeleted(tuple)) + return false; + /* * By here, the inserting transaction has committed - have to check * when... @@ -1218,6 +1249,9 @@ HeapTupleSatisfiesVacuum(HeapTuple htup, TransactionId OldestXmin, */ } + if (HeapTupleHeaderSuperDeleted(tuple)) + return HEAPTUPLE_DEAD; + /* * Okay, the inserter committed, so it was good at some point. Now what * about the deleting transaction? @@ -1406,7 +1440,10 @@ HeapTupleIsSurelyDead(HeapTuple htup, TransactionId OldestXmin) if (!(tuple->t_infomask & HEAP_XMAX_COMMITTED)) return false; - /* Deleter committed, so tuple is dead if the XID is old enough. */ + /* + * Deleter committed, so tuple is dead if the XID is old enough. This + * handles super deleted tuples correctly. + */ return TransactionIdPrecedes(HeapTupleHeaderGetRawXmax(tuple), OldestXmin); } @@ -1539,6 +1576,8 @@ HeapTupleHeaderIsOnlyLocked(HeapTupleHeader tuple) { TransactionId xmax; + Assert(!HeapTupleHeaderSuperDeleted(tuple)); + /* if there's no valid Xmax, then there's obviously no update either */ if (tuple->t_infomask & HEAP_XMAX_INVALID) return true; @@ -1596,6 +1635,9 @@ TransactionIdInArray(TransactionId xid, TransactionId *xip, Size num) * We don't need to support HEAP_MOVED_(IN|OFF) for now because we only support * reading catalog pages which couldn't have been created in an older version. * + * We don't support speculative insertion into catalogs, and so there are no + * checks for super deleted tuples. + * * We don't set any hint bits in here as it seems unlikely to be beneficial as * those should already be set by normal access and it seems to be too * dangerous to do so as the semantics of doing so during timetravel are more @@ -1611,6 +1653,7 @@ HeapTupleSatisfiesHistoricMVCC(HeapTuple htup, Snapshot snapshot, Assert(ItemPointerIsValid(&htup->t_self)); Assert(htup->t_tableOid != InvalidOid); + Assert(!HeapTupleHeaderSuperDeleted(tuple)); /* inserting transaction aborted */ if (HeapTupleHeaderXminInvalid(tuple)) diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h index 888cce7..64e316c 100644 --- a/src/include/access/heapam.h +++ b/src/include/access/heapam.h @@ -28,6 +28,7 @@ #define HEAP_INSERT_SKIP_WAL 0x0001 #define HEAP_INSERT_SKIP_FSM 0x0002 #define HEAP_INSERT_FROZEN 0x0004 +#define HEAP_INSERT_SPECULATIVE 0x0008 typedef struct BulkInsertStateData *BulkInsertState; @@ -141,7 +142,7 @@ extern void heap_multi_insert(Relation relation, HeapTuple *tuples, int ntuples, CommandId cid, int options, BulkInsertState bistate); extern HTSU_Result heap_delete(Relation relation, ItemPointer tid, CommandId cid, Snapshot crosscheck, bool wait, - HeapUpdateFailureData *hufd); + HeapUpdateFailureData *hufd, bool speculative); extern HTSU_Result heap_update(Relation relation, ItemPointer otid, HeapTuple newtup, CommandId cid, Snapshot crosscheck, bool wait, diff --git a/src/include/access/heapam_xlog.h b/src/include/access/heapam_xlog.h index f0f89de..568ec2c 100644 --- a/src/include/access/heapam_xlog.h +++ b/src/include/access/heapam_xlog.h @@ -73,6 +73,8 @@ #define XLOG_HEAP_SUFFIX_FROM_OLD (1<<6) /* last xl_heap_multi_insert record for one heap_multi_insert() call */ #define XLOG_HEAP_LAST_MULTI_INSERT (1<<7) +/* reuse xl_heap_multi_insert-only bit for xl_heap_insert and xl_heap_delete */ +#define XLOG_HEAP_SPECULATIVE_TUPLE XLOG_HEAP_LAST_MULTI_INSERT /* convenience macro for checking whether any form of old tuple was logged */ #define XLOG_HEAP_CONTAINS_OLD \ diff --git a/src/include/access/htup_details.h b/src/include/access/htup_details.h index 0a673cd..95bc510 100644 --- a/src/include/access/htup_details.h +++ b/src/include/access/htup_details.h @@ -96,6 +96,11 @@ * unrelated tuple stored into a slot recently freed by VACUUM. If either * check fails, one may assume that there is no live descendant version. * + * t_ctid is sometimes used to store a speculative token, for speculative + * inserters. Code paths that follow t_ctid chains must also consider that the + * apparently pointed to t_ctid is in fact such a token, that should similarly + * not be followed. + * * Following the fixed header fields, the nulls bitmap is stored (beginning * at t_bits). The bitmap is *not* stored if t_infomask shows that there * are no nulls in the tuple. If an OID field is present (as indicated by @@ -230,6 +235,7 @@ struct HeapTupleHeaderData * modified, or tuple deleted */ #define HEAP_HOT_UPDATED 0x4000 /* tuple was HOT-updated */ #define HEAP_ONLY_TUPLE 0x8000 /* this is heap-only tuple */ +#define HEAP_SPECULATIVE 0x10000 /* this is speculative tuple */ #define HEAP2_XACT_MASK 0xE000 /* visibility-related bits */ @@ -307,6 +313,18 @@ struct HeapTupleHeaderData ) /* + * Was tuple "super deleted" following unsuccessful speculative insertion (i.e. + * conflict was detected at insertion time)? Is is not sufficient to set + * HEAP_XMIN_INVALID to super delete because it is only a hint, and because it + * interacts with transaction commit status. Speculative insertion decouples + * visibility from transaction duration for one special purpose. + */ +#define HeapTupleHeaderSuperDeleted(tup) \ +( \ + (!TransactionIdIsValid(HeapTupleHeaderGetRawXmin(tup))) \ +) + +/* * HeapTupleHeaderGetRawXmax gets you the raw Xmax field. To find out the Xid * that updated a tuple, you might need to resolve the MultiXactId if certain * bits are set. HeapTupleHeaderGetUpdateXid checks those bits and takes care @@ -455,6 +473,17 @@ do { \ (tup)->t_infomask2 &= ~HEAP_ONLY_TUPLE \ ) +#define HeapTupleHeaderSetSpeculative(tup) \ +( \ + AssertMacro(!HeapTupleHeaderXminInvalid(tup)), \ + (tup)->t_infomask2 |= HEAP_SPECULATIVE \ +) + +#define HeapTupleHeaderIsSpeculative(tup) \ +( \ + (tup)->t_infomask2 & HEAP_SPECULATIVE \ +) + #define HeapTupleHeaderHasMatch(tup) \ ( \ (tup)->t_infomask2 & HEAP_TUPLE_HAS_MATCH \ diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index e7cc7a0..42c10d4 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -80,6 +80,8 @@ extern void index_drop(Oid indexId, bool concurrent); extern IndexInfo *BuildIndexInfo(Relation index); +extern void AddUniqueSpeculative(Relation index, IndexInfo *ii); + extern void FormIndexDatum(IndexInfo *indexInfo, TupleTableSlot *slot, EState *estate, diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index c1e7477..8100bd8 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -351,16 +351,19 @@ extern bool ExecRelationIsTargetRelation(EState *estate, Index scanrelid); extern Relation ExecOpenScanRelation(EState *estate, Index scanrelid, int eflags); extern void ExecCloseScanRelation(Relation scanrel); -extern void ExecOpenIndices(ResultRelInfo *resultRelInfo); +extern void ExecOpenIndices(ResultRelInfo *resultRelInfo, bool speculative); extern void ExecCloseIndices(ResultRelInfo *resultRelInfo); extern List *ExecInsertIndexTuples(TupleTableSlot *slot, ItemPointer tupleid, - EState *estate); -extern bool check_exclusion_constraint(Relation heap, Relation index, - IndexInfo *indexInfo, - ItemPointer tupleid, - Datum *values, bool *isnull, - EState *estate, - bool newIndex, bool errorOK); + EState *estate, bool noDupErr, Oid arbiterIdx); +extern bool ExecCheckIndexConstraints(TupleTableSlot *slot, EState *estate, + ItemPointer conflictTid, Oid arbiterIdx); +extern bool check_exclusion_or_unique_constraint(Relation heap, Relation index, + IndexInfo *indexInfo, + ItemPointer tupleid, + Datum *values, bool *isnull, + EState *estate, + bool newIndex, bool errorOK, + bool wait, ItemPointer conflictTid); extern void RegisterExprContextCallback(ExprContext *econtext, ExprContextCallbackFunction function, diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index ac75f86..e61a43a 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -41,6 +41,9 @@ * ExclusionOps Per-column exclusion operators, or NULL if none * ExclusionProcs Underlying function OIDs for ExclusionOps * ExclusionStrats Opclass strategy numbers for ExclusionOps + * UniqueOps Theses are like Exclusion*, but for unique indexes + * UniqueProcs + * UniqueStrats * Unique is it a unique index? * ReadyForInserts is it valid for inserts? * Concurrent are we doing a concurrent index build? @@ -62,6 +65,9 @@ typedef struct IndexInfo Oid *ii_ExclusionOps; /* array with one entry per column */ Oid *ii_ExclusionProcs; /* array with one entry per column */ uint16 *ii_ExclusionStrats; /* array with one entry per column */ + Oid *ii_UniqueOps; /* array with one entry per column */ + Oid *ii_UniqueProcs; /* array with one entry per column */ + uint16 *ii_UniqueStrats; /* array with one entry per column */ bool ii_Unique; bool ii_ReadyForInserts; bool ii_Concurrent; @@ -1092,6 +1098,8 @@ typedef struct ModifyTableState int mt_whichplan; /* which one is being executed (0..n-1) */ ResultRelInfo *resultRelInfo; /* per-subplan target relations */ List **mt_arowmarks; /* per-subplan ExecAuxRowMark lists */ + SpecCmd spec; /* reason for speculative insertion */ + Oid arbiterIndex; /* unique index to arbitrate taking alt path */ EPQState mt_epqstate; /* for evaluating EvalPlanQual rechecks */ bool fireBSTriggers; /* do we need to fire stmt triggers? */ } ModifyTableState; diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 38469ef..5b348fa 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -412,6 +412,8 @@ typedef enum NodeTag T_RowMarkClause, T_XmlSerialize, T_WithClause, + T_InferClause, + T_ConflictClause, T_CommonTableExpr, T_RoleSpec, @@ -625,4 +627,16 @@ typedef enum JoinType (1 << JOIN_RIGHT) | \ (1 << JOIN_ANTI))) != 0) +/* + * SpecCmd - + * "Speculative insertion" clause + * + * This is needed in both parsenodes.h and plannodes.h, so put it here... + */ +typedef enum +{ + SPEC_NONE, /* Not involved in speculative insertion */ + SPEC_IGNORE /* INSERT of "ON CONFLICT IGNORE" */ +} SpecCmd; + #endif /* NODES_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index ec0d0ea..75e33ea 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -132,6 +132,10 @@ typedef struct Query List *withCheckOptions; /* a list of WithCheckOption's */ + SpecCmd specClause; /* speculative insertion clause */ + List *arbiterExpr; /* Unique index arbiter exprs */ + Node *arbiterWhere; /* Unique index arbiter WHERE clause */ + List *returningList; /* return-values list (of TargetEntry) */ List *groupClause; /* a list of SortGroupClause's */ @@ -591,7 +595,7 @@ typedef enum TableLikeOption } TableLikeOption; /* - * IndexElem - index parameters (used in CREATE INDEX) + * IndexElem - index parameters (used in CREATE INDEX, and in ON CONFLICT) * * For a plain index attribute, 'name' is the name of the table column to * index, and 'expr' is NULL. For an index expression, 'name' is NULL and @@ -1015,6 +1019,34 @@ typedef struct WithClause } WithClause; /* + * InferClause - + * ON CONFLICT unique index inference clause + * + * Note: InferClause does not propagate into the Query representation. + */ +typedef struct InferClause +{ + NodeTag type; + List *indexElems; /* IndexElems to infer unique index */ + Node *whereClause; /* qualification (partial-index predicate) */ + int location; /* token location, or -1 if unknown */ +} InferClause; + +/* + * ConflictClause - + * representation of ON CONFLICT clause + * + * Note: ConflictClause does not propagate into the Query representation. + */ +typedef struct ConflictClause +{ + NodeTag type; + SpecCmd specclause; /* Variant specified */ + InferClause *infer; /* Optional index inference clause */ + int location; /* token location, or -1 if unknown */ +} ConflictClause; + +/* * CommonTableExpr - * representation of WITH list element * @@ -1064,6 +1096,7 @@ typedef struct InsertStmt RangeVar *relation; /* relation to insert into */ List *cols; /* optional: names of the target columns */ Node *selectStmt; /* the source SELECT/VALUES, or NULL */ + ConflictClause *confClause; /* ON CONFLICT clause */ List *returningList; /* list of expressions to return */ WithClause *withClause; /* WITH clause */ } InsertStmt; diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 21cbfa8..66027a3 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -178,6 +178,8 @@ typedef struct ModifyTable List *resultRelations; /* integer list of RT indexes */ int resultRelIndex; /* index of first resultRel in plan's list */ List *plans; /* plan(s) producing source data */ + SpecCmd spec; /* speculative insertion specification */ + Oid arbiterIndex; /* Oid of ON CONFLICT arbiter index */ List *withCheckOptionLists; /* per-target-table WCO lists */ List *returningLists; /* per-target-table RETURNING tlists */ List *fdwPrivLists; /* per-target-table FDW private data lists */ diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index 6cad92e..b0fec30 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -64,6 +64,8 @@ extern Expr *adjust_rowcompare_for_index(RowCompareExpr *clause, int indexcol, List **indexcolnos, bool *var_on_left_p); +extern Oid plan_speculative_use_index(PlannerInfo *root, int widthTarget, + List *indexList); /* * tidpath.h diff --git a/src/include/optimizer/plancat.h b/src/include/optimizer/plancat.h index 8eb2e57..878adfe 100644 --- a/src/include/optimizer/plancat.h +++ b/src/include/optimizer/plancat.h @@ -28,6 +28,8 @@ extern PGDLLIMPORT get_relation_info_hook_type get_relation_info_hook; extern void get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, RelOptInfo *rel); +extern Oid infer_unique_index(PlannerInfo *root); + extern void estimate_rel_size(Relation rel, int32 *attr_widths, BlockNumber *pages, double *tuples, double *allvisfrac); diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h index fa72918..c3a0634 100644 --- a/src/include/optimizer/planmain.h +++ b/src/include/optimizer/planmain.h @@ -85,7 +85,7 @@ extern ModifyTable *make_modifytable(PlannerInfo *root, Index nominalRelation, List *resultRelations, List *subplans, List *withCheckOptionLists, List *returningLists, - List *rowMarks, int epqParam); + List *rowMarks, SpecCmd spec, int epqParam); extern bool is_projection_capable_plan(Plan *plan); /* diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 7c243ec..cf501e6 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -87,6 +87,7 @@ PG_KEYWORD("commit", COMMIT, UNRESERVED_KEYWORD) PG_KEYWORD("committed", COMMITTED, UNRESERVED_KEYWORD) PG_KEYWORD("concurrently", CONCURRENTLY, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("configuration", CONFIGURATION, UNRESERVED_KEYWORD) +PG_KEYWORD("conflict", CONFLICT, UNRESERVED_KEYWORD) PG_KEYWORD("connection", CONNECTION, UNRESERVED_KEYWORD) PG_KEYWORD("constraint", CONSTRAINT, RESERVED_KEYWORD) PG_KEYWORD("constraints", CONSTRAINTS, UNRESERVED_KEYWORD) @@ -180,6 +181,7 @@ PG_KEYWORD("hold", HOLD, UNRESERVED_KEYWORD) PG_KEYWORD("hour", HOUR_P, UNRESERVED_KEYWORD) PG_KEYWORD("identity", IDENTITY_P, UNRESERVED_KEYWORD) PG_KEYWORD("if", IF_P, UNRESERVED_KEYWORD) +PG_KEYWORD("ignore", IGNORE_P, UNRESERVED_KEYWORD) PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD) PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD) diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h index 6a4438f..d1d0d12 100644 --- a/src/include/parser/parse_clause.h +++ b/src/include/parser/parse_clause.h @@ -41,6 +41,8 @@ extern List *transformDistinctClause(ParseState *pstate, List **targetlist, List *sortClause, bool is_agg); extern List *transformDistinctOnClause(ParseState *pstate, List *distinctlist, List **targetlist, List *sortClause); +extern void transformConflictClause(ParseState *pstate, ConflictClause *confClause, + List **arbiterExpr, Node **arbiterWhere); extern List *addTargetToSortList(ParseState *pstate, TargetEntry *tle, List *sortlist, List *targetlist, SortBy *sortby, diff --git a/src/include/replication/reorderbuffer.h b/src/include/replication/reorderbuffer.h index f1e0f57..d694981 100644 --- a/src/include/replication/reorderbuffer.h +++ b/src/include/replication/reorderbuffer.h @@ -43,6 +43,13 @@ typedef struct ReorderBufferTupleBuf * and ComboCids in the same list with the user visible INSERT/UPDATE/DELETE * changes. Users of the decoding facilities will never see changes with * *_INTERNAL_* actions. + * + * The REORDER_BUFFER_CHANGE_INTERNAL_INSERT and + * REORDER_BUFFER_CHANGE_INTERNAL_DELETE changes concern "super deletion", + * which is a mechanism that speculative insertion makes use of to handle + * conflicts. At transaction reassembly these will be consolidated, and so + * decoding plugins will only ever handle REORDER_BUFFER_CHANGE_INSERT changes + * here too (in the common case where speculative insertion works out). */ enum ReorderBufferChangeType { @@ -51,7 +58,9 @@ enum ReorderBufferChangeType REORDER_BUFFER_CHANGE_DELETE, REORDER_BUFFER_CHANGE_INTERNAL_SNAPSHOT, REORDER_BUFFER_CHANGE_INTERNAL_COMMAND_ID, - REORDER_BUFFER_CHANGE_INTERNAL_TUPLECID + REORDER_BUFFER_CHANGE_INTERNAL_TUPLECID, + REORDER_BUFFER_CHANGE_INTERNAL_INSERT, + REORDER_BUFFER_CHANGE_INTERNAL_DELETE }; /* diff --git a/src/include/storage/lmgr.h b/src/include/storage/lmgr.h index f5d70e5..7cc75fc 100644 --- a/src/include/storage/lmgr.h +++ b/src/include/storage/lmgr.h @@ -76,6 +76,11 @@ extern bool ConditionalXactLockTableWait(TransactionId xid); extern void WaitForLockers(LOCKTAG heaplocktag, LOCKMODE lockmode); extern void WaitForLockersMultiple(List *locktags, LOCKMODE lockmode); +/* Lock an XID for tuple insertion (used to wait for an insertion to finish) */ +extern uint32 SpeculativeInsertionLockAcquire(TransactionId xid); +extern void SpeculativeInsertionLockRelease(TransactionId xid); +extern void SpeculativeInsertionWait(TransactionId xid, uint32 token); + /* Lock a general object (other than a relation) of the current database */ extern void LockDatabaseObject(Oid classid, Oid objid, uint16 objsubid, LOCKMODE lockmode); diff --git a/src/include/storage/lock.h b/src/include/storage/lock.h index 1477a6f..29edab4 100644 --- a/src/include/storage/lock.h +++ b/src/include/storage/lock.h @@ -176,6 +176,8 @@ typedef enum LockTagType /* ID info for a transaction is its TransactionId */ LOCKTAG_VIRTUALTRANSACTION, /* virtual transaction (ditto) */ /* ID info for a virtual transaction is its VirtualTransactionId */ + LOCKTAG_PROMISE_TUPLE, /* tuple insertion, keyed by Xid and token */ + /* ID info for a transaction is its TransactionId */ LOCKTAG_OBJECT, /* non-relation database object */ /* ID info for an object is DB OID + CLASS OID + OBJECT OID + SUBID */ @@ -261,6 +263,14 @@ typedef struct LOCKTAG (locktag).locktag_type = LOCKTAG_VIRTUALTRANSACTION, \ (locktag).locktag_lockmethodid = DEFAULT_LOCKMETHOD) +#define SET_LOCKTAG_SPECULATIVE_INSERTION(locktag,xid,token) \ + ((locktag).locktag_field1 = (xid), \ + (locktag).locktag_field2 = (token), \ + (locktag).locktag_field3 = 0, \ + (locktag).locktag_field4 = 0, \ + (locktag).locktag_type = LOCKTAG_PROMISE_TUPLE, \ + (locktag).locktag_lockmethodid = DEFAULT_LOCKMETHOD) + #define SET_LOCKTAG_OBJECT(locktag,dboid,classoid,objoid,objsubid) \ ((locktag).locktag_field1 = (dboid), \ (locktag).locktag_field2 = (classoid), \ diff --git a/src/include/utils/snapshot.h b/src/include/utils/snapshot.h index 26fb257..cd5ad76 100644 --- a/src/include/utils/snapshot.h +++ b/src/include/utils/snapshot.h @@ -87,6 +87,17 @@ typedef struct SnapshotData bool copied; /* false if it's a static snapshot */ /* + * Snapshot's speculative token is value set by HeapTupleSatisfiesDirty, + * indicating that the tuple is being inserted speculatively, and may yet + * be "super-deleted" before EOX. The caller may use the value with + * PromiseTupleInsertionWait to wait for the inserter to decide. It is only + * set when a valid 'xmin' is set, too. By convention, when + * speculativeToken is zero, the caller must assume that is should wait on + * a non-speculative tuple (i.e. wait for xmin/xmax to commit). + */ + uint32 speculativeToken; + + /* * note: all ids in subxip[] are >= xmin, but we don't bother filtering * out any that are >= xmax */ diff --git a/src/test/isolation/expected/insert-conflict-ignore.out b/src/test/isolation/expected/insert-conflict-ignore.out new file mode 100644 index 0000000..e6cc2a1 --- /dev/null +++ b/src/test/isolation/expected/insert-conflict-ignore.out @@ -0,0 +1,23 @@ +Parsed test spec with 2 sessions + +starting permutation: ignore1 ignore2 c1 select2 c2 +step ignore1: INSERT INTO ints(key, val) VALUES(1, 'ignore1') ON CONFLICT IGNORE; +step ignore2: INSERT INTO ints(key, val) VALUES(1, 'ignore2') ON CONFLICT IGNORE; +step c1: COMMIT; +step ignore2: <... completed> +step select2: SELECT * FROM ints; +key val + +1 ignore1 +step c2: COMMIT; + +starting permutation: ignore1 ignore2 a1 select2 c2 +step ignore1: INSERT INTO ints(key, val) VALUES(1, 'ignore1') ON CONFLICT IGNORE; +step ignore2: INSERT INTO ints(key, val) VALUES(1, 'ignore2') ON CONFLICT IGNORE; +step a1: ABORT; +step ignore2: <... completed> +step select2: SELECT * FROM ints; +key val + +1 ignore2 +step c2: COMMIT; diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index c055a53..59d14e9 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -16,6 +16,7 @@ test: fk-deadlock2 test: eval-plan-qual test: lock-update-delete test: lock-update-traversal +test: insert-conflict-ignore test: delete-abort-savept test: delete-abort-savept-2 test: aborted-keyrevoke diff --git a/src/test/isolation/specs/insert-conflict-ignore.spec b/src/test/isolation/specs/insert-conflict-ignore.spec new file mode 100644 index 0000000..fde43b3 --- /dev/null +++ b/src/test/isolation/specs/insert-conflict-ignore.spec @@ -0,0 +1,41 @@ +# INSERT...ON CONFLICT IGNORE test +# +# This test tries to expose problems with the interaction between concurrent +# sessions during INSERT...ON CONFLICT IGNORE. +# +# The convention here is that session 1 always ends up inserting, and session 2 +# always ends up ignoring. + +setup +{ + CREATE TABLE ints (key int primary key, val text); +} + +teardown +{ + DROP TABLE ints; +} + +session "s1" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step "ignore1" { INSERT INTO ints(key, val) VALUES(1, 'ignore1') ON CONFLICT IGNORE; } +step "c1" { COMMIT; } +step "a1" { ABORT; } + +session "s2" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step "ignore2" { INSERT INTO ints(key, val) VALUES(1, 'ignore2') ON CONFLICT IGNORE; } +step "select2" { SELECT * FROM ints; } +step "c2" { COMMIT; } +step "a2" { ABORT; } + +# Regular case where one session block-waits on another to determine if it +# should proceed with an insert or ignore. +permutation "ignore1" "ignore2" "c1" "select2" "c2" +permutation "ignore1" "ignore2" "a1" "select2" "c2" diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out new file mode 100644 index 0000000..a34d857 --- /dev/null +++ b/src/test/regress/expected/insert_conflict.out @@ -0,0 +1,55 @@ +-- +-- insert...on conflict update unique index inference +-- +create table insertconflicttest(key int4, fruit text); +-- +-- Test partial unique index inference +-- +create unique index partial_key_index on insertconflicttest(key) where fruit like '%berry'; +-- Succeeds +insert into insertconflicttest values (23, 'Blackberry') on conflict (key where fruit like '%berry' and fruit = 'inconsequential') ignore; +-- fails +insert into insertconflicttest values (23, 'Blackberry') on conflict (key where fruit like '%berry' or fruit = 'consequential') ignore; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (23, 'Uncovered by Index') on conflict (key where fruit like '%berry') ignore; +ERROR: partial arbiter unique index has predicate that does not cover tuple proposed for insertion +DETAIL: ON CONFLICT inference clause implies that the tuple proposed for insertion must be covered by predicate for partial index "partial_key_index". +drop index partial_key_index; +-- Cleanup +drop table insertconflicttest; +-- ****************************************************************** +-- * * +-- * Test inheritance (example taken from tutorial) * +-- * * +-- ****************************************************************** +create table cities ( + name text, + population float8, + altitude int -- (in ft) +); +create table capitals ( + state char(2) +) inherits (cities); +-- Create unique indexes. Due to a general limitation of inheritance, +-- uniqueness is only enforced per-relation +create unique index cities_names_unique on cities (name); +create unique index capitals_names_unique on capitals (name); +-- prepopulate the tables. +insert into cities values ('San Francisco', 7.24E+5, 63); +insert into cities values ('Las Vegas', 2.583E+5, 2174); +insert into cities values ('Mariposa', 1200, 1953); +insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA'); +insert into capitals values ('Madison', 1.913E+5, 845, 'WI'); +-- Tests proper for inheritance: +-- fails: +insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict (name) ignore; +ERROR: relation "cities" has inheritance children +HINT: Only heap relations without inheritance children are accepted as targets when a unique index is inferred for ON CONFLICT. +-- Succeeds: +-- There is at least limited support for relations with children: +insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict ignore; +-- No children, and so no restrictions: +insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA') on conflict (name) ignore; +-- clean up +drop table capitals; +drop table cities; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 1788270..1d67331 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1123,6 +1123,10 @@ SELECT * FROM shoelace_log ORDER BY sl_name; SELECT * FROM shoelace_obsolete WHERE sl_avail = 0; insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0); insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0); +-- Unsupported (even though a similar updatable view construct is) +insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0) + on conflict ignore; +ERROR: INSERT with ON CONFLICT clause may not target relation with INSERT or UPDATE rules SELECT * FROM shoelace_obsolete ORDER BY sl_len_cm; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm ------------+----------+------------+--------+----------+----------- @@ -2348,6 +2352,11 @@ DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2". insert into rule_and_refint_t3 values (1, 13, 11, 'row6'); ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". +-- Ordinary table +insert into rule_and_refint_t3 values (1, 13, 11, 'row6') + on conflict ignore; +ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" +DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3 where (exists (select 1 from rule_and_refint_t3 where (((rule_and_refint_t3.id3a = new.id3a) diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 9e7ba72..b8823b4 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -215,6 +215,10 @@ INSERT INTO rw_view15 VALUES (3, 'ROW 3'); -- should fail ERROR: cannot insert into column "upper" of view "rw_view15" DETAIL: View columns that are not columns of their base relation are not updatable. INSERT INTO rw_view15 (a) VALUES (3); -- should be OK +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT IGNORE; -- succeeds +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) IGNORE; -- fails, unsupported +ERROR: relation "rw_view15" is not an ordinary table +HINT: Only ordinary tables are accepted as targets when a unique index is inferred for ON CONFLICT. ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET'; INSERT INTO rw_view15 (a) VALUES (4); -- should fail ERROR: cannot insert into column "upper" of view "rw_view15" diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 6d3b865..b0ebb6b 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -36,6 +36,7 @@ test: geometry horology regex oidjoins type_sanity opr_sanity # These four each depend on the previous one # ---------- test: insert +test: insert_conflict test: create_function_1 test: create_type test: create_table diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 8326894..8409c0f 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -50,6 +50,7 @@ test: oidjoins test: type_sanity test: opr_sanity test: insert +test: insert_conflict test: create_function_1 test: create_type test: create_table diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql new file mode 100644 index 0000000..e330ecd --- /dev/null +++ b/src/test/regress/sql/insert_conflict.sql @@ -0,0 +1,65 @@ +-- +-- insert...on conflict update unique index inference +-- +create table insertconflicttest(key int4, fruit text); + +-- +-- Test partial unique index inference +-- +create unique index partial_key_index on insertconflicttest(key) where fruit like '%berry'; + +-- Succeeds +insert into insertconflicttest values (23, 'Blackberry') on conflict (key where fruit like '%berry' and fruit = 'inconsequential') ignore; + +-- fails +insert into insertconflicttest values (23, 'Blackberry') on conflict (key where fruit like '%berry' or fruit = 'consequential') ignore; +insert into insertconflicttest values (23, 'Uncovered by Index') on conflict (key where fruit like '%berry') ignore; + +drop index partial_key_index; + +-- Cleanup +drop table insertconflicttest; + +-- ****************************************************************** +-- * * +-- * Test inheritance (example taken from tutorial) * +-- * * +-- ****************************************************************** +create table cities ( + name text, + population float8, + altitude int -- (in ft) +); + +create table capitals ( + state char(2) +) inherits (cities); + +-- Create unique indexes. Due to a general limitation of inheritance, +-- uniqueness is only enforced per-relation +create unique index cities_names_unique on cities (name); +create unique index capitals_names_unique on capitals (name); + +-- prepopulate the tables. +insert into cities values ('San Francisco', 7.24E+5, 63); +insert into cities values ('Las Vegas', 2.583E+5, 2174); +insert into cities values ('Mariposa', 1200, 1953); + +insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA'); +insert into capitals values ('Madison', 1.913E+5, 845, 'WI'); + +-- Tests proper for inheritance: + +-- fails: +insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict (name) ignore; + +-- Succeeds: + +-- There is at least limited support for relations with children: +insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict ignore; +-- No children, and so no restrictions: +insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA') on conflict (name) ignore; + +-- clean up +drop table capitals; +drop table cities; diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index c385e41..5807331 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -680,6 +680,9 @@ SELECT * FROM shoelace_log ORDER BY sl_name; insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0); insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0); +-- Unsupported (even though a similar updatable view construct is) +insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0) + on conflict ignore; SELECT * FROM shoelace_obsolete ORDER BY sl_len_cm; SELECT * FROM shoelace_candelete; @@ -844,6 +847,9 @@ insert into rule_and_refint_t3 values (1, 12, 11, 'row3'); insert into rule_and_refint_t3 values (1, 12, 12, 'row4'); insert into rule_and_refint_t3 values (1, 11, 13, 'row5'); insert into rule_and_refint_t3 values (1, 13, 11, 'row6'); +-- Ordinary table +insert into rule_and_refint_t3 values (1, 13, 11, 'row6') + on conflict ignore; create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3 where (exists (select 1 from rule_and_refint_t3 diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index 60c7e29..48dd9a9 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -69,6 +69,8 @@ DELETE FROM rw_view14 WHERE a=3; -- should be OK -- Partially updatable view INSERT INTO rw_view15 VALUES (3, 'ROW 3'); -- should fail INSERT INTO rw_view15 (a) VALUES (3); -- should be OK +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT IGNORE; -- succeeds +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) IGNORE; -- fails, unsupported ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET'; INSERT INTO rw_view15 (a) VALUES (4); -- should fail UPDATE rw_view15 SET upper='ROW 3' WHERE a=3; -- should fail -- 1.9.1