From 88327092ff06c48676d2a603420089bf493770f3 Mon Sep 17 00:00:00 2001 From: Dean Rasheed Date: Thu, 12 Feb 2026 09:55:06 +0000 Subject: [PATCH] Add support for INSERT ... ON CONFLICT DO SELECT. This adds a new ON CONFLICT action DO SELECT [FOR UPDATE/SHARE], which returns the pre-existing rows when conflicts are detected. The INSERT statement must have a RETURNING clause, when DO SELECT is specified. The optional FOR UPDATE/SHARE clause allows the rows to be locked before they are are returned. As with a DO UPDATE conflict action, an optional WHERE clause may be used to prevent rows from being selected for return (but as with a DO UPDATE action, rows filtered out by the WHERE clause are still locked). Bumps catversion as stored rules change. Author: Andreas Karlsson Author: Marko Tiikkaja Author: Viktor Holmberg Reviewed-by: Joel Jacobson Reviewed-by: Kirill Reshke Reviewed-by: Dean Rasheed Reviewed-by: Jian He Discussion: https://postgr.es/m/d631b406-13b7-433e-8c0b-c6040c4b4663@Spark Discussion: https://postgr.es/m/5fca222d-62ae-4a2f-9fcb-0eca56277094@Spark Discussion: https://postgr.es/m/2b5db2e6-8ece-44d0-9890-f256fdca9f7e@proxel.se Discussion: https://postgr.es/m/CAL9smLCdV-v3KgOJX3mU19FYK82N7yzqJj2HAwWX70E=P98kgQ@mail.gmail.com --- contrib/postgres_fdw/postgres_fdw.c | 2 +- doc/src/sgml/dml.sgml | 2 +- doc/src/sgml/fdwhandler.sgml | 2 +- doc/src/sgml/mvcc.sgml | 12 + doc/src/sgml/postgres-fdw.sgml | 2 +- doc/src/sgml/ref/create_policy.sgml | 29 +- doc/src/sgml/ref/create_view.sgml | 4 +- doc/src/sgml/ref/insert.sgml | 135 +++++-- doc/src/sgml/ref/merge.sgml | 3 +- src/backend/access/heap/heapam.c | 8 +- src/backend/commands/explain.c | 36 +- src/backend/executor/execIndexing.c | 6 +- src/backend/executor/execPartition.c | 134 ++++--- src/backend/executor/nodeModifyTable.c | 375 ++++++++++++++---- src/backend/optimizer/plan/createplan.c | 4 + src/backend/optimizer/plan/setrefs.c | 5 +- src/backend/optimizer/util/plancat.c | 27 +- src/backend/parser/analyze.c | 54 ++- src/backend/parser/gram.y | 20 +- src/backend/parser/parse_clause.c | 14 +- src/backend/rewrite/rewriteHandler.c | 27 +- src/backend/rewrite/rowsecurity.c | 111 +++--- src/backend/utils/adt/ruleutils.c | 77 ++-- src/include/catalog/catversion.h | 2 +- src/include/nodes/execnodes.h | 13 +- src/include/nodes/lockoptions.h | 3 +- src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 10 +- src/include/nodes/plannodes.h | 4 +- src/include/nodes/primnodes.h | 12 +- .../expected/insert-conflict-do-select.out | 138 +++++++ src/test/isolation/isolation_schedule | 1 + .../specs/insert-conflict-do-select.spec | 53 +++ src/test/regress/expected/constraints.out | 4 + src/test/regress/expected/insert_conflict.out | 216 +++++++++- src/test/regress/expected/privileges.out | 26 ++ src/test/regress/expected/rowsecurity.out | 94 ++++- src/test/regress/expected/rules.out | 55 +++ src/test/regress/expected/triggers.out | 10 +- src/test/regress/expected/updatable_views.out | 82 +++- src/test/regress/sql/constraints.sql | 3 + src/test/regress/sql/insert_conflict.sql | 74 +++- src/test/regress/sql/privileges.sql | 18 + src/test/regress/sql/rowsecurity.sql | 57 ++- src/test/regress/sql/rules.sql | 26 ++ src/test/regress/sql/triggers.sql | 3 +- src/test/regress/sql/updatable_views.sql | 31 +- src/tools/pgindent/typedefs.list | 2 +- 48 files changed, 1675 insertions(+), 352 deletions(-) create mode 100644 src/test/isolation/expected/insert-conflict-do-select.out create mode 100644 src/test/isolation/specs/insert-conflict-do-select.spec diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 3572689e33b..60d90329a65 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -1856,7 +1856,7 @@ postgresPlanForeignModify(PlannerInfo *root, returningList = (List *) list_nth(plan->returningLists, subplan_index); /* - * ON CONFLICT DO UPDATE and DO NOTHING case with inference specification + * ON CONFLICT DO NOTHING/SELECT/UPDATE with inference specification * should have already been rejected in the optimizer, as presently there * is no way to recognize an arbiter index on a foreign table. Only DO * NOTHING is supported without an inference specification. diff --git a/doc/src/sgml/dml.sgml b/doc/src/sgml/dml.sgml index 61c64cf6c49..cd348d5773a 100644 --- a/doc/src/sgml/dml.sgml +++ b/doc/src/sgml/dml.sgml @@ -385,7 +385,7 @@ UPDATE products SET price = price * 1.10 for a DELETE. However, there are situations where it can still be useful for those commands. For example, in an INSERT with an - ON CONFLICT DO UPDATE + ON CONFLICT DO SELECT/UPDATE clause, the old values will be non-NULL for conflicting rows. Similarly, if a DELETE is turned into an UPDATE by a rewrite rule, diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml index c6d66414b8e..9826e09f983 100644 --- a/doc/src/sgml/fdwhandler.sgml +++ b/doc/src/sgml/fdwhandler.sgml @@ -2045,7 +2045,7 @@ GetForeignServerByName(const char *name, bool missing_ok); INSERT with an ON CONFLICT clause does not support specifying the conflict target, as unique constraints or exclusion constraints on remote tables are not locally known. This - in turn implies that ON CONFLICT DO UPDATE is not supported, + in turn implies that ON CONFLICT DO SELECT/UPDATE is not supported, since the specification is mandatory there. diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index 049ee75a4ba..e775260936a 100644 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -366,6 +366,18 @@ conventionally visible to the command. + + INSERT with an ON CONFLICT DO + SELECT clause behaves similarly to ON CONFLICT DO + UPDATE. In Read Committed mode, each row proposed for insertion + is guaranteed to either insert or return the conflicting row (unless there are + unrelated errors). If a conflict originates in another transaction whose + effects are not yet visible to the INSERT, the command + will wait for that transaction to commit or roll back, then return the + conflicting row if it was committed (even though that row was not visible + when the command started). + + INSERT with an ON CONFLICT DO NOTHING clause may have insertion not proceed for a row due to diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml index 9b032fbf675..fcf10e4317e 100644 --- a/doc/src/sgml/postgres-fdw.sgml +++ b/doc/src/sgml/postgres-fdw.sgml @@ -82,7 +82,7 @@ Note that postgres_fdw currently lacks support for INSERT statements with an ON CONFLICT DO - UPDATE clause. However, the ON CONFLICT DO NOTHING + SELECT/UPDATE clause. However, the ON CONFLICT DO NOTHING clause is supported, provided a unique index inference specification is omitted. Note also that postgres_fdw supports row movement diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index 9065ccb65f9..d8a036739c0 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -294,7 +294,7 @@ CREATE POLICY name ON If an INSERT has an ON CONFLICT DO - UPDATE clause, or an ON CONFLICT DO + SELECT/UPDATE clause, or an ON CONFLICT DO NOTHING clause with an arbiter index or constraint specification, then SELECT permissions are required on the relation, and the rows proposed for @@ -338,8 +338,8 @@ CREATE POLICY name ON - Note that an INSERT with an ON CONFLICT - DO NOTHING/UPDATE clause will check the + Note that an INSERT with an + ON CONFLICT clause will check the INSERT policies' WITH CHECK expressions for all rows proposed for insertion, regardless of whether or not they end up being inserted. @@ -352,9 +352,10 @@ CREATE POLICY name ON Using UPDATE for a policy means that it will apply - to UPDATE, SELECT FOR UPDATE, - and SELECT FOR SHARE commands, as well as - auxiliary ON CONFLICT DO UPDATE clauses of + to UPDATE and + SELECT FOR UPDATE/SHARE commands, as well as + auxiliary ON CONFLICT DO UPDATE and + ON CONFLICT DO SELECT FOR UPDATE/SHARE clauses of INSERT commands, and MERGE commands containing UPDATE actions. Since an UPDATE command @@ -578,6 +579,22 @@ CREATE POLICY name ON — + + ON CONFLICT DO SELECT + Check existing row + — + — + — + — + + + ON CONFLICT DO SELECT FOR UPDATE/SHARE + Check existing row + — + Check existing row + — + — + MERGE Filter source & target rows diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index f8a4740608a..60215eba3b8 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -415,7 +415,7 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; DELETE, or MERGE statement on the view into the corresponding statement on the underlying base relation. INSERT statements that have an ON - CONFLICT DO UPDATE clause are fully supported. + CONFLICT clause are fully supported. @@ -430,7 +430,7 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; an INSERT or MERGE command can potentially insert base-relation rows that do not satisfy the WHERE condition and thus are not - visible through the view (ON CONFLICT DO UPDATE may + visible through the view (ON CONFLICT DO SELECT/UPDATE may similarly affect an existing row not visible through the view). The CHECK OPTION may be used to prevent INSERT, UPDATE, and diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index 42eec5f4edd..121a9edcb99 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -37,6 +37,7 @@ INSERT INTO table_name [ AS and conflict_action is one of: DO NOTHING + DO SELECT [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } ] [ WHERE condition ] DO UPDATE SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) @@ -89,24 +90,27 @@ INSERT INTO table_name [ AS The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted - (or updated, if an ON CONFLICT DO UPDATE clause was - used). This is primarily useful for obtaining values that were + (or selected or updated, if an ON CONFLICT DO SELECT/UPDATE + clause was used). This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. The syntax of the RETURNING list is identical to that of the output list of SELECT. Only rows that were successfully - inserted or updated will be returned. For example, if a row was - locked but not updated because an ON CONFLICT DO UPDATE - ... WHERE clause condition was not satisfied, the - row will not be returned. + inserted, updated, or selected will be returned. For example, if a row was + locked but not updated or selected because an ON CONFLICT ... + WHERE clause condition + was not satisfied, the row will not be returned. You must have INSERT privilege on a table in order to insert into it. If ON CONFLICT DO UPDATE is present, UPDATE privilege on the table is also - required. + required. If ON CONFLICT DO SELECT is present, + SELECT privilege on the table is required. + If ON CONFLICT DO SELECT FOR UPDATE/SHARE is used, + UPDATE privilege is required on at least one + column, in addition to SELECT privilege. @@ -343,8 +347,11 @@ INSERT INTO table_name [ AS For a simple INSERT, all old values will be NULL. However, for an INSERT - with an ON CONFLICT DO UPDATE clause, the old - values may be non-NULL. + with an ON CONFLICT DO SELECT/UPDATE clause, the + old values may be non-NULL (when the row proposed + for insertion conflicts with an existing row). If the + SELECT path is taken, the new values will be + identical to the old values, since no modification takes place. @@ -380,6 +387,9 @@ INSERT INTO table_name [ AS ON CONFLICT DO UPDATE updates the existing row that conflicts with the row proposed for insertion as its alternative action. + ON CONFLICT DO SELECT returns the existing row + that conflicts with the row proposed for insertion, optionally + with row-level locking. @@ -411,6 +421,15 @@ INSERT INTO table_name [ AS . + + ON CONFLICT DO SELECT similarly allows an atomic + INSERT or SELECT outcome. This + is also known as idempotent insert or + get or create. For ON CONFLICT DO + SELECT, a RETURNING clause + must be provided. + + conflict_target @@ -424,7 +443,8 @@ INSERT INTO table_name [ AS conflict_target; when omitted, conflicts with all usable constraints (and unique indexes) are handled. For ON CONFLICT DO - UPDATE, a conflict_target + UPDATE and ON CONFLICT DO SELECT, + a conflict_target must be provided. @@ -434,19 +454,23 @@ INSERT INTO table_name [ AS conflict_action - conflict_action specifies an - alternative ON CONFLICT action. It can be - either DO NOTHING, or a DO - UPDATE clause specifying the exact details of the - UPDATE action to be performed in case of a - conflict. The SET and - WHERE clauses in ON CONFLICT DO - UPDATE have access to the existing row using the - table's name (or an alias), and to the row proposed for insertion - using the special excluded table. - SELECT privilege is required on any column in the - target table where corresponding excluded - columns are read. + conflict_action specifies an alternative + ON CONFLICT action. It can be + DO NOTHING, a DO SELECT + clause that allows conflicting rows to be returned, or a + DO UPDATE clause specifying the exact details + of the UPDATE action to be performed in case + of a conflict. + + + The SET clause in DO UPDATE + and the WHERE clause in both + DO SELECT and DO UPDATE have + access to the existing row using the table's name (or an alias), + and to the row proposed for insertion using the special + excluded table. SELECT + privilege is required on any column in the target table where + corresponding excluded columns are read. Note that the effects of all per-row BEFORE @@ -545,24 +569,41 @@ INSERT INTO table_name [ AS + + FOR UPDATE + FOR NO KEY UPDATE + FOR SHARE + FOR KEY SHARE + + + When specified in an ON CONFLICT DO SELECT clause, + conflicting table rows are locked against concurrent updates. + See in the + documentation. + + + + condition An expression that returns a value of type boolean. Only rows for which this expression - returns true will be updated, although all - rows will be locked when the ON CONFLICT DO UPDATE - action is taken. Note that - condition is evaluated last, after - a conflict has been identified as a candidate to update. + returns true will be updated or selected for + return, although all conflicting rows will be locked when + ON CONFLICT DO UPDATE or + ON CONFLICT DO SELECT FOR UPDATE/SHARE is + specified. Note that condition is + evaluated last, after a conflict has been identified as a candidate + to update or select. Note that exclusion constraints are not supported as arbiters with - ON CONFLICT DO UPDATE. In all cases, only + ON CONFLICT DO SELECT/UPDATE. In all cases, only NOT DEFERRABLE constraints and unique indexes are supported as arbiters. @@ -610,7 +651,7 @@ INSERT INTO table_name [ AS oid count The count is the number of - rows inserted or updated. oid is always 0 (it + rows inserted, updated, or selected for return. oid is always 0 (it used to be the OID assigned to the inserted row if count was exactly one and the target table was declared WITH OIDS and 0 otherwise, but creating a table @@ -621,8 +662,7 @@ INSERT oid countINSERT command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the - RETURNING list, computed over the row(s) inserted or - updated by the command. + RETURNING list, computed over the row(s) affected by the command. @@ -796,6 +836,35 @@ INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution') -- index to arbitrate taking the DO NOTHING action) INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design') ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING; + + + + Insert new distributor if possible, otherwise return the existing + distributor row. Example assumes a unique index has been defined + that constrains values appearing in the did column. + This is useful for get-or-create patterns: + +INSERT INTO distributors (did, dname) VALUES (11, 'Global Electronics') + ON CONFLICT (did) DO SELECT + RETURNING *; + + + + Insert a new distributor if the ID doesn't match, otherwise return + the existing row, if its name doesn't match: + +INSERT INTO distributors AS d (did, dname) VALUES (12, 'Micro Devices Inc') + ON CONFLICT (did) DO SELECT WHERE d.dname != EXCLUDED.dname + RETURNING *; + + + + Insert a new distributor or return and lock the existing row for update. + This is useful when you need to ensure exclusive access to the row: + +INSERT INTO distributors (did, dname) VALUES (13, 'Advanced Systems') + ON CONFLICT (did) DO SELECT FOR UPDATE + RETURNING *; diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml index c2e181066a4..765fe7a7d62 100644 --- a/doc/src/sgml/ref/merge.sgml +++ b/doc/src/sgml/ref/merge.sgml @@ -714,7 +714,8 @@ MERGE total_count on the behavior at each isolation level. You may also wish to consider using INSERT ... ON CONFLICT as an alternative statement which offers the ability to run an - UPDATE if a concurrent INSERT + UPDATE or return the existing row (with + DO SELECT) if a concurrent INSERT occurs. There are a variety of differences and restrictions between the two statement types and they are not interchangeable. diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index ff850099304..98d53caeea8 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -4689,10 +4689,10 @@ l3: if (result == TM_Invisible) { /* - * This is possible, but only when locking a tuple for ON CONFLICT - * UPDATE. We return this value here rather than throwing an error in - * order to give that case the opportunity to throw a more specific - * error. + * This is possible, but only when locking a tuple for ON CONFLICT DO + * SELECT/UPDATE. We return this value here rather than throwing an + * error in order to give that case the opportunity to throw a more + * specific error. */ result = TM_Invisible; goto out_locked; diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index b7bb111688c..b9587983f88 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -4672,10 +4672,36 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors, if (node->onConflictAction != ONCONFLICT_NONE) { - ExplainPropertyText("Conflict Resolution", - node->onConflictAction == ONCONFLICT_NOTHING ? - "NOTHING" : "UPDATE", - es); + const char *resolution = NULL; + + if (node->onConflictAction == ONCONFLICT_NOTHING) + resolution = "NOTHING"; + else if (node->onConflictAction == ONCONFLICT_UPDATE) + resolution = "UPDATE"; + else + { + Assert(node->onConflictAction == ONCONFLICT_SELECT); + switch (node->onConflictLockStrength) + { + case LCS_NONE: + resolution = "SELECT"; + break; + case LCS_FORKEYSHARE: + resolution = "SELECT FOR KEY SHARE"; + break; + case LCS_FORSHARE: + resolution = "SELECT FOR SHARE"; + break; + case LCS_FORNOKEYUPDATE: + resolution = "SELECT FOR NO KEY UPDATE"; + break; + case LCS_FORUPDATE: + resolution = "SELECT FOR UPDATE"; + break; + } + } + + ExplainPropertyText("Conflict Resolution", resolution, es); /* * Don't display arbiter indexes at all when DO NOTHING variant @@ -4684,7 +4710,7 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors, if (idxNames) ExplainPropertyList("Conflict Arbiter Indexes", idxNames, es); - /* ON CONFLICT DO UPDATE WHERE qual is specially displayed */ + /* ON CONFLICT DO SELECT/UPDATE WHERE qual is specially displayed */ if (node->onConflictWhere) { show_upper_qual((List *) node->onConflictWhere, "Conflict Filter", diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c index 6ae0f959592..f0ba7eac87d 100644 --- a/src/backend/executor/execIndexing.c +++ b/src/backend/executor/execIndexing.c @@ -54,9 +54,9 @@ * --------------------- * * Speculative insertion is a two-phase mechanism used to implement - * INSERT ... ON CONFLICT DO UPDATE/NOTHING. The tuple is first inserted - * to the heap and update the indexes as usual, but if a constraint is - * violated, we can still back out the insertion without aborting the whole + * INSERT ... ON CONFLICT. The tuple is first inserted into the heap + * and the indexes are updated as usual, but if a constraint is violated, + * we can still back out of the insertion without aborting the whole * transaction. In an INSERT ... ON CONFLICT statement, if a conflict is * detected, the inserted tuple is backed out and the ON CONFLICT action is * executed instead. diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c index d13e786cf13..bab294f5e91 100644 --- a/src/backend/executor/execPartition.c +++ b/src/backend/executor/execPartition.c @@ -883,20 +883,27 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate, leaf_part_rri->ri_onConflictArbiterIndexes = arbiterIndexes; /* - * In the DO UPDATE case, we have some more state to initialize. + * In the DO UPDATE and DO SELECT cases, we have some more state to + * initialize. */ - if (node->onConflictAction == ONCONFLICT_UPDATE) + if (node->onConflictAction == ONCONFLICT_UPDATE || + node->onConflictAction == ONCONFLICT_SELECT) { - OnConflictSetState *onconfl = makeNode(OnConflictSetState); + OnConflictActionState *onconfl = makeNode(OnConflictActionState); TupleConversionMap *map; map = ExecGetRootToChildMap(leaf_part_rri, estate); - Assert(node->onConflictSet != NIL); + Assert(node->onConflictSet != NIL || + node->onConflictAction == ONCONFLICT_SELECT); Assert(rootResultRelInfo->ri_onConflict != NULL); leaf_part_rri->ri_onConflict = onconfl; + /* Lock strength for DO SELECT [FOR UPDATE/SHARE] */ + onconfl->oc_LockStrength = + rootResultRelInfo->ri_onConflict->oc_LockStrength; + /* * Need a separate existing slot for each partition, as the * partition could be of a different AM, even if the tuple @@ -909,7 +916,7 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate, /* * If the partition's tuple descriptor matches exactly the root * parent (the common case), we can re-use most of the parent's ON - * CONFLICT SET state, skipping a bunch of work. Otherwise, we + * CONFLICT action state, skipping a bunch of work. Otherwise, we * need to create state specific to this partition. */ if (map == NULL) @@ -917,7 +924,7 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate, /* * It's safe to reuse these from the partition root, as we * only process one tuple at a time (therefore we won't - * overwrite needed data in slots), and the results of + * overwrite needed data in slots), and the results of any * projections are independent of the underlying storage. * Projections and where clauses themselves don't store state * / are independent of the underlying storage. @@ -931,66 +938,81 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate, } else { - List *onconflset; - List *onconflcols; - /* - * Translate expressions in onConflictSet to account for - * different attribute numbers. For that, map partition - * varattnos twice: first to catch the EXCLUDED - * pseudo-relation (INNER_VAR), and second to handle the main - * target relation (firstVarno). + * For ON CONFLICT DO UPDATE, translate expressions in + * onConflictSet to account for different attribute numbers. + * For that, map partition varattnos twice: first to catch the + * EXCLUDED pseudo-relation (INNER_VAR), and second to handle + * the main target relation (firstVarno). */ - onconflset = copyObject(node->onConflictSet); - if (part_attmap == NULL) - part_attmap = - build_attrmap_by_name(RelationGetDescr(partrel), - RelationGetDescr(firstResultRel), - false); - onconflset = (List *) - map_variable_attnos((Node *) onconflset, - INNER_VAR, 0, - part_attmap, - RelationGetForm(partrel)->reltype, - &found_whole_row); - /* We ignore the value of found_whole_row. */ - onconflset = (List *) - map_variable_attnos((Node *) onconflset, - firstVarno, 0, - part_attmap, - RelationGetForm(partrel)->reltype, - &found_whole_row); - /* We ignore the value of found_whole_row. */ - - /* Finally, adjust the target colnos to match the partition. */ - onconflcols = adjust_partition_colnos(node->onConflictCols, - leaf_part_rri); - - /* create the tuple slot for the UPDATE SET projection */ - onconfl->oc_ProjSlot = - table_slot_create(partrel, - &mtstate->ps.state->es_tupleTable); + if (node->onConflictAction == ONCONFLICT_UPDATE) + { + List *onconflset; + List *onconflcols; + + onconflset = copyObject(node->onConflictSet); + if (part_attmap == NULL) + part_attmap = + build_attrmap_by_name(RelationGetDescr(partrel), + RelationGetDescr(firstResultRel), + false); + onconflset = (List *) + map_variable_attnos((Node *) onconflset, + INNER_VAR, 0, + part_attmap, + RelationGetForm(partrel)->reltype, + &found_whole_row); + /* We ignore the value of found_whole_row. */ + onconflset = (List *) + map_variable_attnos((Node *) onconflset, + firstVarno, 0, + part_attmap, + RelationGetForm(partrel)->reltype, + &found_whole_row); + /* We ignore the value of found_whole_row. */ - /* build UPDATE SET projection state */ - onconfl->oc_ProjInfo = - ExecBuildUpdateProjection(onconflset, - true, - onconflcols, - partrelDesc, - econtext, - onconfl->oc_ProjSlot, - &mtstate->ps); + /* + * Finally, adjust the target colnos to match the + * partition. + */ + onconflcols = adjust_partition_colnos(node->onConflictCols, + leaf_part_rri); + + /* create the tuple slot for the UPDATE SET projection */ + onconfl->oc_ProjSlot = + table_slot_create(partrel, + &mtstate->ps.state->es_tupleTable); + + /* build UPDATE SET projection state */ + onconfl->oc_ProjInfo = + ExecBuildUpdateProjection(onconflset, + true, + onconflcols, + partrelDesc, + econtext, + onconfl->oc_ProjSlot, + &mtstate->ps); + } /* - * If there is a WHERE clause, initialize state where it will - * be evaluated, mapping the attribute numbers appropriately. - * As with onConflictSet, we need to map partition varattnos - * to the partition's tupdesc. + * For both ON CONFLICT DO UPDATE and ON CONFLICT DO SELECT, + * there may be a WHERE clause. If so, initialize state where + * it will be evaluated, mapping the attribute numbers + * appropriately. As with onConflictSet, we need to map + * partition varattnos twice, to catch both the EXCLUDED + * pseudo-relation (INNER_VAR), and the main target relation + * (firstVarno). */ if (node->onConflictWhere) { List *clause; + if (part_attmap == NULL) + part_attmap = + build_attrmap_by_name(RelationGetDescr(partrel), + RelationGetDescr(firstResultRel), + false); + clause = copyObject((List *) node->onConflictWhere); clause = (List *) map_variable_attnos((Node *) clause, diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index f5e9d369940..6802fc13e95 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -147,12 +147,24 @@ static void ExecCrossPartitionUpdateForeignKey(ModifyTableContext *context, ItemPointer tupleid, TupleTableSlot *oldslot, TupleTableSlot *newslot); +static bool ExecOnConflictLockRow(ModifyTableContext *context, + TupleTableSlot *existing, + ItemPointer conflictTid, + Relation relation, + LockTupleMode lockmode, + bool isUpdate); static bool ExecOnConflictUpdate(ModifyTableContext *context, ResultRelInfo *resultRelInfo, ItemPointer conflictTid, TupleTableSlot *excludedSlot, bool canSetTag, TupleTableSlot **returning); +static bool ExecOnConflictSelect(ModifyTableContext *context, + ResultRelInfo *resultRelInfo, + ItemPointer conflictTid, + TupleTableSlot *excludedSlot, + bool canSetTag, + TupleTableSlot **returning); static TupleTableSlot *ExecPrepareTupleRouting(ModifyTableState *mtstate, EState *estate, PartitionTupleRouting *proute, @@ -274,7 +286,7 @@ ExecCheckPlanOutput(Relation resultRel, List *targetList) * * context: context for the ModifyTable operation * resultRelInfo: current result rel - * cmdType: operation/merge action performed (INSERT, UPDATE, or DELETE) + * isDelete: true if the operation/merge action is a DELETE * oldSlot: slot holding old tuple deleted or updated * newSlot: slot holding new tuple inserted or updated * planSlot: slot holding tuple returned by top subplan node @@ -283,12 +295,15 @@ ExecCheckPlanOutput(Relation resultRel, List *targetList) * econtext's scan tuple and its old & new tuples are not needed (FDW direct- * modify is disabled if the RETURNING list refers to any OLD/NEW values). * + * Note: For the SELECT path of INSERT ... ON CONFLICT DO SELECT, oldSlot and + * newSlot are both the existing tuple, since it's not changed. + * * Returns a slot holding the result tuple */ static TupleTableSlot * ExecProcessReturning(ModifyTableContext *context, ResultRelInfo *resultRelInfo, - CmdType cmdType, + bool isDelete, TupleTableSlot *oldSlot, TupleTableSlot *newSlot, TupleTableSlot *planSlot) @@ -298,23 +313,17 @@ ExecProcessReturning(ModifyTableContext *context, ExprContext *econtext = projectReturning->pi_exprContext; /* Make tuple and any needed join variables available to ExecProject */ - switch (cmdType) + if (isDelete) { - case CMD_INSERT: - case CMD_UPDATE: - /* return new tuple by default */ - if (newSlot) - econtext->ecxt_scantuple = newSlot; - break; - - case CMD_DELETE: - /* return old tuple by default */ - if (oldSlot) - econtext->ecxt_scantuple = oldSlot; - break; - - default: - elog(ERROR, "unrecognized commandType: %d", (int) cmdType); + /* return old tuple by default */ + if (oldSlot) + econtext->ecxt_scantuple = oldSlot; + } + else + { + /* return new tuple by default */ + if (newSlot) + econtext->ecxt_scantuple = newSlot; } econtext->ecxt_outertuple = planSlot; @@ -1158,6 +1167,26 @@ ExecInsert(ModifyTableContext *context, else goto vlock; } + else if (onconflict == ONCONFLICT_SELECT) + { + /* + * In case of ON CONFLICT DO SELECT, optionally lock the + * conflicting tuple, fetch it and project RETURNING on + * it. Be prepared to retry if locking fails because of a + * concurrent UPDATE/DELETE to the conflict tuple. + */ + TupleTableSlot *returning = NULL; + + if (ExecOnConflictSelect(context, resultRelInfo, + &conflictTid, slot, canSetTag, + &returning)) + { + InstrCountTuples2(&mtstate->ps, 1); + return returning; + } + else + goto vlock; + } else { /* @@ -1329,7 +1358,7 @@ ExecInsert(ModifyTableContext *context, } } - result = ExecProcessReturning(context, resultRelInfo, CMD_INSERT, + result = ExecProcessReturning(context, resultRelInfo, false, oldSlot, slot, planSlot); /* @@ -1890,7 +1919,7 @@ ldelete: return NULL; } - rslot = ExecProcessReturning(context, resultRelInfo, CMD_DELETE, + rslot = ExecProcessReturning(context, resultRelInfo, true, slot, NULL, context->planSlot); /* @@ -2692,56 +2721,37 @@ redo_act: /* Process RETURNING if present */ if (resultRelInfo->ri_projectReturning) - return ExecProcessReturning(context, resultRelInfo, CMD_UPDATE, + return ExecProcessReturning(context, resultRelInfo, false, oldSlot, slot, context->planSlot); return NULL; } /* - * ExecOnConflictUpdate --- execute UPDATE of INSERT ON CONFLICT DO UPDATE + * ExecOnConflictLockRow --- lock the row for ON CONFLICT DO SELECT/UPDATE * - * Try to lock tuple for update as part of speculative insertion. If - * a qual originating from ON CONFLICT DO UPDATE is satisfied, update - * (but still lock row, even though it may not satisfy estate's - * snapshot). + * Try to lock tuple for update as part of speculative insertion for ON + * CONFLICT DO UPDATE or ON CONFLICT DO SELECT FOR UPDATE/SHARE. * - * Returns true if we're done (with or without an update), or false if - * the caller must retry the INSERT from scratch. + * Returns true if the row is successfully locked, or false if the caller must + * retry the INSERT from scratch. */ static bool -ExecOnConflictUpdate(ModifyTableContext *context, - ResultRelInfo *resultRelInfo, - ItemPointer conflictTid, - TupleTableSlot *excludedSlot, - bool canSetTag, - TupleTableSlot **returning) +ExecOnConflictLockRow(ModifyTableContext *context, + TupleTableSlot *existing, + ItemPointer conflictTid, + Relation relation, + LockTupleMode lockmode, + bool isUpdate) { - ModifyTableState *mtstate = context->mtstate; - ExprContext *econtext = mtstate->ps.ps_ExprContext; - Relation relation = resultRelInfo->ri_RelationDesc; - ExprState *onConflictSetWhere = resultRelInfo->ri_onConflict->oc_WhereClause; - TupleTableSlot *existing = resultRelInfo->ri_onConflict->oc_Existing; TM_FailureData tmfd; - LockTupleMode lockmode; TM_Result test; Datum xminDatum; TransactionId xmin; bool isnull; /* - * Parse analysis should have blocked ON CONFLICT for all system - * relations, which includes these. There's no fundamental obstacle to - * supporting this; we'd just need to handle LOCKTAG_TUPLE like the other - * ExecUpdate() caller. - */ - Assert(!resultRelInfo->ri_needLockTagTuple); - - /* Determine lock mode to use */ - lockmode = ExecUpdateLockMode(context->estate, resultRelInfo); - - /* - * Lock tuple for update. Don't follow updates when tuple cannot be + * Lock tuple with lockmode. Don't follow updates when tuple cannot be * locked without doing so. A row locking conflict here means our * previous conclusion that the tuple is conclusively committed is not * true anymore. @@ -2786,7 +2796,7 @@ ExecOnConflictUpdate(ModifyTableContext *context, (errcode(ERRCODE_CARDINALITY_VIOLATION), /* translator: %s is a SQL command name */ errmsg("%s command cannot affect row a second time", - "ON CONFLICT DO UPDATE"), + isUpdate ? "ON CONFLICT DO UPDATE" : "ON CONFLICT DO SELECT"), errhint("Ensure that no rows proposed for insertion within the same command have duplicate constrained values."))); /* This shouldn't happen */ @@ -2834,6 +2844,50 @@ ExecOnConflictUpdate(ModifyTableContext *context, } /* Success, the tuple is locked. */ + return true; +} + +/* + * ExecOnConflictUpdate --- execute UPDATE of INSERT ON CONFLICT DO UPDATE + * + * Try to lock tuple for update as part of speculative insertion. If + * a qual originating from ON CONFLICT DO UPDATE is satisfied, update + * (but still lock row, even though it may not satisfy estate's + * snapshot). + * + * Returns true if we're done (with or without an update), or false if + * the caller must retry the INSERT from scratch. + */ +static bool +ExecOnConflictUpdate(ModifyTableContext *context, + ResultRelInfo *resultRelInfo, + ItemPointer conflictTid, + TupleTableSlot *excludedSlot, + bool canSetTag, + TupleTableSlot **returning) +{ + ModifyTableState *mtstate = context->mtstate; + ExprContext *econtext = mtstate->ps.ps_ExprContext; + Relation relation = resultRelInfo->ri_RelationDesc; + ExprState *onConflictSetWhere = resultRelInfo->ri_onConflict->oc_WhereClause; + TupleTableSlot *existing = resultRelInfo->ri_onConflict->oc_Existing; + LockTupleMode lockmode; + + /* + * Parse analysis should have blocked ON CONFLICT for all system + * relations, which includes these. There's no fundamental obstacle to + * supporting this; we'd just need to handle LOCKTAG_TUPLE like the other + * ExecUpdate() caller. + */ + Assert(!resultRelInfo->ri_needLockTagTuple); + + /* Determine lock mode to use */ + lockmode = ExecUpdateLockMode(context->estate, resultRelInfo); + + /* Lock tuple for update */ + if (!ExecOnConflictLockRow(context, existing, conflictTid, + resultRelInfo->ri_RelationDesc, lockmode, true)) + return false; /* * Verify that the tuple is visible to our MVCC snapshot if the current @@ -2875,11 +2929,13 @@ ExecOnConflictUpdate(ModifyTableContext *context, * security barrier quals (if any), enforced here as RLS checks/WCOs. * * The rewriter creates UPDATE RLS checks/WCOs for UPDATE security - * quals, and stores them as WCOs of "kind" WCO_RLS_CONFLICT_CHECK, - * but that's almost the extent of its special handling for ON - * CONFLICT DO UPDATE. + * quals, and stores them as WCOs of "kind" WCO_RLS_CONFLICT_CHECK. + * Since SELECT permission on the target table is always required for + * INSERT ... ON CONFLICT DO UPDATE, the rewriter also adds SELECT RLS + * checks/WCOs for SELECT security quals, using WCOs of the same kind, + * and this check enforces them too. * - * The rewriter will also have associated UPDATE applicable straight + * The rewriter will also have associated UPDATE-applicable straight * RLS checks/WCOs for the benefit of the ExecUpdate() call that * follows. INSERTs and UPDATEs naturally have mutually exclusive WCO * kinds, so there is no danger of spurious over-enforcement in the @@ -2924,6 +2980,141 @@ ExecOnConflictUpdate(ModifyTableContext *context, return true; } +/* + * ExecOnConflictSelect --- execute SELECT of INSERT ON CONFLICT DO SELECT + * + * If SELECT FOR UPDATE/SHARE is specified, try to lock tuple as part of + * speculative insertion. If a qual originating from ON CONFLICT DO SELECT is + * satisfied, select (but still lock row, even though it may not satisfy + * estate's snapshot). + * + * Returns true if we're done (with or without a select), or false if the + * caller must retry the INSERT from scratch. + */ +static bool +ExecOnConflictSelect(ModifyTableContext *context, + ResultRelInfo *resultRelInfo, + ItemPointer conflictTid, + TupleTableSlot *excludedSlot, + bool canSetTag, + TupleTableSlot **returning) +{ + ModifyTableState *mtstate = context->mtstate; + ExprContext *econtext = mtstate->ps.ps_ExprContext; + Relation relation = resultRelInfo->ri_RelationDesc; + ExprState *onConflictSelectWhere = resultRelInfo->ri_onConflict->oc_WhereClause; + TupleTableSlot *existing = resultRelInfo->ri_onConflict->oc_Existing; + LockClauseStrength lockStrength = resultRelInfo->ri_onConflict->oc_LockStrength; + + /* + * Parse analysis should have blocked ON CONFLICT for all system + * relations, which includes these. There's no fundamental obstacle to + * supporting this; we'd just need to handle LOCKTAG_TUPLE appropriately. + */ + Assert(!resultRelInfo->ri_needLockTagTuple); + + /* Fetch/lock existing tuple, according to the requested lock strength */ + if (lockStrength == LCS_NONE) + { + if (!table_tuple_fetch_row_version(relation, + conflictTid, + SnapshotAny, + existing)) + elog(ERROR, "failed to fetch conflicting tuple for ON CONFLICT"); + } + else + { + LockTupleMode lockmode; + + switch (lockStrength) + { + case LCS_FORKEYSHARE: + lockmode = LockTupleKeyShare; + break; + case LCS_FORSHARE: + lockmode = LockTupleShare; + break; + case LCS_FORNOKEYUPDATE: + lockmode = LockTupleNoKeyExclusive; + break; + case LCS_FORUPDATE: + lockmode = LockTupleExclusive; + break; + default: + elog(ERROR, "Unexpected lock strength %d", (int) lockStrength); + } + + if (!ExecOnConflictLockRow(context, existing, conflictTid, + resultRelInfo->ri_RelationDesc, lockmode, false)) + return false; + } + + /* + * Verify that the tuple is visible to our MVCC snapshot if the current + * isolation level mandates that. See comments in ExecOnConflictUpdate(). + */ + ExecCheckTupleVisible(context->estate, relation, existing); + + /* + * Make tuple and any needed join variables available to ExecQual. The + * EXCLUDED tuple is installed in ecxt_innertuple, while the target's + * existing tuple is installed in the scantuple. EXCLUDED has been made + * to reference INNER_VAR in setrefs.c, but there is no other redirection. + */ + econtext->ecxt_scantuple = existing; + econtext->ecxt_innertuple = excludedSlot; + econtext->ecxt_outertuple = NULL; + + if (!ExecQual(onConflictSelectWhere, econtext)) + { + ExecClearTuple(existing); /* see return below */ + InstrCountFiltered1(&mtstate->ps, 1); + return true; /* done with the tuple */ + } + + if (resultRelInfo->ri_WithCheckOptions != NIL) + { + /* + * Check target's existing tuple against SELECT-applicable USING + * security barrier quals (if any), enforced here as RLS checks/WCOs. + * + * The rewriter creates WCOs from the USING quals of SELECT policies, + * and stores them as WCOs of "kind" WCO_RLS_CONFLICT_CHECK. If FOR + * UPDATE/SHARE was specified, UPDATE permissions are required on the + * target table, and the rewriter also adds WCOs built from the USING + * quals of UPDATE policies, using WCOs of the same kind, and this + * check enforces them too. + */ + ExecWithCheckOptions(WCO_RLS_CONFLICT_CHECK, resultRelInfo, + existing, + mtstate->ps.state); + } + + /* RETURNING is required for DO SELECT */ + Assert(resultRelInfo->ri_projectReturning); + + *returning = ExecProcessReturning(context, resultRelInfo, false, + existing, existing, context->planSlot); + + if (canSetTag) + context->estate->es_processed++; + + /* + * Before releasing the existing tuple, make sure that the returning slot + * has a local copy of any pass-by-reference values. + */ + ExecMaterializeSlot(*returning); + + /* + * Clear out existing tuple, as there might not be another conflict among + * the next input rows. Don't want to hold resources till the end of the + * query. + */ + ExecClearTuple(existing); + + return true; +} + /* * Perform MERGE. */ @@ -3549,7 +3740,7 @@ lmerge_matched: case CMD_UPDATE: rslot = ExecProcessReturning(context, resultRelInfo, - CMD_UPDATE, + false, resultRelInfo->ri_oldTupleSlot, newslot, context->planSlot); @@ -3558,7 +3749,7 @@ lmerge_matched: case CMD_DELETE: rslot = ExecProcessReturning(context, resultRelInfo, - CMD_DELETE, + true, resultRelInfo->ri_oldTupleSlot, NULL, context->planSlot); @@ -4329,7 +4520,8 @@ ExecModifyTable(PlanState *pstate) Assert((resultRelInfo->ri_projectReturning->pi_state.flags & EEO_FLAG_HAS_OLD) == 0 && (resultRelInfo->ri_projectReturning->pi_state.flags & EEO_FLAG_HAS_NEW) == 0); - slot = ExecProcessReturning(&context, resultRelInfo, operation, + slot = ExecProcessReturning(&context, resultRelInfo, + operation == CMD_DELETE, NULL, NULL, context.planSlot); return slot; @@ -5031,49 +5223,60 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) } /* - * If needed, Initialize target list, projection and qual for ON CONFLICT - * DO UPDATE. + * For ON CONFLICT DO SELECT/UPDATE, initialize the ON CONFLICT action + * state. */ - if (node->onConflictAction == ONCONFLICT_UPDATE) + if (node->onConflictAction == ONCONFLICT_UPDATE || + node->onConflictAction == ONCONFLICT_SELECT) { - OnConflictSetState *onconfl = makeNode(OnConflictSetState); - ExprContext *econtext; - TupleDesc relationDesc; + OnConflictActionState *onconfl = makeNode(OnConflictActionState); /* already exists if created by RETURNING processing above */ if (mtstate->ps.ps_ExprContext == NULL) ExecAssignExprContext(estate, &mtstate->ps); - econtext = mtstate->ps.ps_ExprContext; - relationDesc = resultRelInfo->ri_RelationDesc->rd_att; - - /* create state for DO UPDATE SET operation */ + /* action state for DO SELECT/UPDATE */ resultRelInfo->ri_onConflict = onconfl; + /* lock strength for DO SELECT [FOR UPDATE/SHARE] */ + onconfl->oc_LockStrength = node->onConflictLockStrength; + /* initialize slot for the existing tuple */ onconfl->oc_Existing = table_slot_create(resultRelInfo->ri_RelationDesc, &mtstate->ps.state->es_tupleTable); /* - * Create the tuple slot for the UPDATE SET projection. We want a slot - * of the table's type here, because the slot will be used to insert - * into the table, and for RETURNING processing - which may access - * system attributes. + * For ON CONFLICT DO UPDATE, initialize target list and projection. */ - onconfl->oc_ProjSlot = - table_slot_create(resultRelInfo->ri_RelationDesc, - &mtstate->ps.state->es_tupleTable); + if (node->onConflictAction == ONCONFLICT_UPDATE) + { + ExprContext *econtext; + TupleDesc relationDesc; + + econtext = mtstate->ps.ps_ExprContext; + relationDesc = resultRelInfo->ri_RelationDesc->rd_att; - /* build UPDATE SET projection state */ - onconfl->oc_ProjInfo = - ExecBuildUpdateProjection(node->onConflictSet, - true, - node->onConflictCols, - relationDesc, - econtext, - onconfl->oc_ProjSlot, - &mtstate->ps); + /* + * Create the tuple slot for the UPDATE SET projection. We want a + * slot of the table's type here, because the slot will be used to + * insert into the table, and for RETURNING processing - which may + * access system attributes. + */ + onconfl->oc_ProjSlot = + table_slot_create(resultRelInfo->ri_RelationDesc, + &mtstate->ps.state->es_tupleTable); + + /* build UPDATE SET projection state */ + onconfl->oc_ProjInfo = + ExecBuildUpdateProjection(node->onConflictSet, + true, + node->onConflictCols, + relationDesc, + econtext, + onconfl->oc_ProjSlot, + &mtstate->ps); + } /* initialize state to evaluate the WHERE clause, if any */ if (node->onConflictWhere) diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 959df43c39e..21f1988cf22 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -7043,6 +7043,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan, if (!onconflict) { node->onConflictAction = ONCONFLICT_NONE; + node->onConflictLockStrength = LCS_NONE; node->onConflictSet = NIL; node->onConflictCols = NIL; node->onConflictWhere = NULL; @@ -7054,6 +7055,9 @@ make_modifytable(PlannerInfo *root, Plan *subplan, { node->onConflictAction = onconflict->action; + /* Lock strength for ON CONFLICT DO SELECT [FOR UPDATE/SHARE] */ + node->onConflictLockStrength = onconflict->lockStrength; + /* * Here we convert the ON CONFLICT UPDATE tlist, if any, to the * executor's convention of having consecutive resno's. The actual diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 5ad6c13830b..1b5b9b5ed9c 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -1163,7 +1163,8 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) * those are already used by RETURNING and it seems better to * be non-conflicting. */ - if (splan->onConflictSet) + if (splan->onConflictAction == ONCONFLICT_UPDATE || + splan->onConflictAction == ONCONFLICT_SELECT) { indexed_tlist *itlist; @@ -3146,7 +3147,7 @@ search_indexed_tlist_for_sortgroupref(Expr *node, * other-relation Vars by OUTER_VAR references, while leaving target Vars * alone. Thus inner_itlist = NULL and acceptable_rel = the ID of the * target relation should be passed. - * 3) ON CONFLICT UPDATE SET/WHERE clauses. Here references to EXCLUDED are + * 3) ON CONFLICT SET and WHERE clauses. Here references to EXCLUDED are * to be replaced with INNER_VAR references, while leaving target Vars (the * to-be-updated relation) alone. Correspondingly inner_itlist is to be * EXCLUDED elements, outer_itlist = NULL and acceptable_rel the target diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 1b20bc805e6..d63e7390be7 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -841,9 +841,9 @@ infer_arbiter_indexes(PlannerInfo *root) /* * Quickly return NIL for ON CONFLICT DO NOTHING without an inference - * specification or named constraint. ON CONFLICT DO UPDATE statements - * must always provide one or the other (but parser ought to have caught - * that already). + * specification or named constraint. ON CONFLICT DO SELECT/UPDATE + * statements must always provide one or the other (but parser ought to + * have caught that already). */ if (onconflict->arbiterElems == NIL && onconflict->constraint == InvalidOid) @@ -1024,10 +1024,17 @@ infer_arbiter_indexes(PlannerInfo *root) */ if (indexOidFromConstraint == idxForm->indexrelid) { - if (idxForm->indisexclusion && onconflict->action == ONCONFLICT_UPDATE) + /* + * ON CONFLICT DO UPDATE and ON CONFLICT DO SELECT are not + * supported with exclusion constraints. + */ + if (idxForm->indisexclusion && + (onconflict->action == ONCONFLICT_UPDATE || + onconflict->action == ONCONFLICT_SELECT)) ereport(ERROR, - (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("ON CONFLICT DO UPDATE not supported with exclusion constraints"))); + errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("ON CONFLICT DO %s not supported with exclusion constraints", + onconflict->action == ONCONFLICT_UPDATE ? "UPDATE" : "SELECT")); /* Consider this one a match already */ results = lappend_oid(results, idxForm->indexrelid); @@ -1037,10 +1044,12 @@ infer_arbiter_indexes(PlannerInfo *root) else if (indexOidFromConstraint != InvalidOid) { /* - * In the case of "ON constraint_name DO UPDATE" we need to skip - * non-unique candidates. + * In the case of "ON constraint_name DO SELECT/UPDATE" we need to + * skip non-unique candidates. */ - if (!idxForm->indisunique && onconflict->action == ONCONFLICT_UPDATE) + if (!idxForm->indisunique && + (onconflict->action == ONCONFLICT_UPDATE || + onconflict->action == ONCONFLICT_SELECT)) continue; } else diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 50d51c880d6..539c16c4f79 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -650,7 +650,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) ListCell *icols; ListCell *attnos; ListCell *lc; - bool isOnConflictUpdate; + bool requiresUpdatePerm; AclMode targetPerms; /* There can't be any outer WITH to worry about */ @@ -668,8 +668,14 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) qry->override = stmt->override; - isOnConflictUpdate = (stmt->onConflictClause && - stmt->onConflictClause->action == ONCONFLICT_UPDATE); + /* + * ON CONFLICT DO UPDATE and ON CONFLICT DO SELECT FOR UPDATE/SHARE + * require UPDATE permission on the target relation. + */ + requiresUpdatePerm = (stmt->onConflictClause && + (stmt->onConflictClause->action == ONCONFLICT_UPDATE || + (stmt->onConflictClause->action == ONCONFLICT_SELECT && + stmt->onConflictClause->lockStrength != LCS_NONE))); /* * We have three cases to deal with: DEFAULT VALUES (selectStmt == NULL), @@ -719,7 +725,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) * to the joinlist or namespace. */ targetPerms = ACL_INSERT; - if (isOnConflictUpdate) + if (requiresUpdatePerm) targetPerms |= ACL_UPDATE; qry->resultRelation = setTargetTable(pstate, stmt->relation, false, false, targetPerms); @@ -1026,6 +1032,15 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) false, true, true); } + /* ON CONFLICT DO SELECT requires a RETURNING clause */ + if (stmt->onConflictClause && + stmt->onConflictClause->action == ONCONFLICT_SELECT && + !stmt->returningClause) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("ON CONFLICT DO SELECT requires a RETURNING clause"), + parser_errposition(pstate, stmt->onConflictClause->location)); + /* Process ON CONFLICT, if any. */ if (stmt->onConflictClause) qry->onConflict = transformOnConflictClause(pstate, @@ -1184,12 +1199,13 @@ transformOnConflictClause(ParseState *pstate, OnConflictExpr *result; /* - * If this is ON CONFLICT ... UPDATE, first create the range table entry - * for the EXCLUDED pseudo relation, so that that will be present while - * processing arbiter expressions. (You can't actually reference it from - * there, but this provides a useful error message if you try.) + * If this is ON CONFLICT DO SELECT/UPDATE, first create the range table + * entry for the EXCLUDED pseudo relation, so that that will be present + * while processing arbiter expressions. (You can't actually reference it + * from there, but this provides a useful error message if you try.) */ - if (onConflictClause->action == ONCONFLICT_UPDATE) + if (onConflictClause->action == ONCONFLICT_UPDATE || + onConflictClause->action == ONCONFLICT_SELECT) { Relation targetrel = pstate->p_target_relation; RangeTblEntry *exclRte; @@ -1218,21 +1234,22 @@ transformOnConflictClause(ParseState *pstate, transformOnConflictArbiter(pstate, onConflictClause, &arbiterElems, &arbiterWhere, &arbiterConstraint); - /* Process DO UPDATE */ - if (onConflictClause->action == ONCONFLICT_UPDATE) + /* Process DO SELECT/UPDATE */ + if (onConflictClause->action == ONCONFLICT_UPDATE || + onConflictClause->action == ONCONFLICT_SELECT) { /* * Add the EXCLUDED pseudo relation to the query namespace, making it - * available in the UPDATE subexpressions. + * available in SET and WHERE subexpressions. */ addNSItemToQuery(pstate, exclNSItem, false, true, true); - /* - * Now transform the UPDATE subexpressions. - */ - onConflictSet = - transformUpdateTargetList(pstate, onConflictClause->targetList); + /* Process the UPDATE SET clause */ + if (onConflictClause->action == ONCONFLICT_UPDATE) + onConflictSet = + transformUpdateTargetList(pstate, onConflictClause->targetList); + /* Process the SELECT/UPDATE WHERE clause */ onConflictWhere = transformWhereClause(pstate, onConflictClause->whereClause, EXPR_KIND_WHERE, "WHERE"); @@ -1246,13 +1263,14 @@ transformOnConflictClause(ParseState *pstate, pstate->p_namespace = list_delete_last(pstate->p_namespace); } - /* Finally, build ON CONFLICT DO [NOTHING | UPDATE] expression */ + /* Finally, build ON CONFLICT DO [NOTHING | SELECT | UPDATE] expression */ result = makeNode(OnConflictExpr); result->action = onConflictClause->action; result->arbiterElems = arbiterElems; result->arbiterWhere = arbiterWhere; result->constraint = arbiterConstraint; + result->lockStrength = onConflictClause->lockStrength; result->onConflictSet = onConflictSet; result->onConflictWhere = onConflictWhere; result->exclRelIndex = exclRelIndex; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 713ee5c10a2..c567252acc4 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -481,7 +481,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type OptNoLog %type OnCommitOption -%type for_locking_strength +%type for_locking_strength opt_for_locking_strength %type for_locking_item %type for_locking_clause opt_for_locking_clause for_locking_items %type locked_rels_list @@ -12496,12 +12496,24 @@ insert_column_item: ; opt_on_conflict: + ON CONFLICT opt_conf_expr DO SELECT opt_for_locking_strength where_clause + { + $$ = makeNode(OnConflictClause); + $$->action = ONCONFLICT_SELECT; + $$->infer = $3; + $$->targetList = NIL; + $$->lockStrength = $6; + $$->whereClause = $7; + $$->location = @1; + } + | ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list where_clause { $$ = makeNode(OnConflictClause); $$->action = ONCONFLICT_UPDATE; $$->infer = $3; $$->targetList = $7; + $$->lockStrength = LCS_NONE; $$->whereClause = $8; $$->location = @1; } @@ -12512,6 +12524,7 @@ opt_on_conflict: $$->action = ONCONFLICT_NOTHING; $$->infer = $3; $$->targetList = NIL; + $$->lockStrength = LCS_NONE; $$->whereClause = NULL; $$->location = @1; } @@ -13741,6 +13754,11 @@ for_locking_strength: | FOR KEY SHARE { $$ = LCS_FORKEYSHARE; } ; +opt_for_locking_strength: + for_locking_strength { $$ = $1; } + | /* EMPTY */ { $$ = LCS_NONE; } + ; + locked_rels_list: OF qualified_name_list { $$ = $2; } | /* EMPTY */ { $$ = NIL; } diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index e35fd25c9bb..06b65d4a605 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -3373,13 +3373,15 @@ transformOnConflictArbiter(ParseState *pstate, *arbiterWhere = NULL; *constraint = InvalidOid; - if (onConflictClause->action == ONCONFLICT_UPDATE && !infer) + if ((onConflictClause->action == ONCONFLICT_UPDATE || + onConflictClause->action == ONCONFLICT_SELECT) && !infer) ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("ON CONFLICT DO UPDATE requires inference specification or constraint name"), - errhint("For example, ON CONFLICT (column_name)."), - parser_errposition(pstate, - exprLocation((Node *) onConflictClause)))); + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("ON CONFLICT DO %s requires inference specification or constraint name", + onConflictClause->action == ONCONFLICT_UPDATE ? "UPDATE" : "SELECT"), + errhint("For example, ON CONFLICT (column_name)."), + parser_errposition(pstate, + exprLocation((Node *) onConflictClause))); /* * To simplify certain aspects of its design, speculative insertion into diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 19dcce80ec4..7c99290be4d 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -658,6 +658,19 @@ rewriteRuleAction(Query *parsetree, rule_action = sub_action; } + /* + * If rule_action is INSERT .. ON CONFLICT DO SELECT, the parser should + * have verified that it has a RETURNING clause, but we must also check + * that the triggering query has a RETURNING clause. + */ + if (rule_action->onConflict && + rule_action->onConflict->action == ONCONFLICT_SELECT && + (!rule_action->returningList || !parsetree->returningList)) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("ON CONFLICT DO SELECT requires a RETURNING clause"), + errdetail("A rule action is INSERT ... ON CONFLICT DO SELECT, which requires a RETURNING clause.")); + /* * If rule_action has a RETURNING clause, then either throw it away if the * triggering query has no RETURNING clause, or rewrite it to emit what @@ -3643,11 +3656,12 @@ rewriteTargetView(Query *parsetree, Relation view) } /* - * For INSERT .. ON CONFLICT .. DO UPDATE, we must also update assorted - * stuff in the onConflict data structure. + * For INSERT .. ON CONFLICT .. DO SELECT/UPDATE, we must also update + * assorted stuff in the onConflict data structure. */ if (parsetree->onConflict && - parsetree->onConflict->action == ONCONFLICT_UPDATE) + (parsetree->onConflict->action == ONCONFLICT_UPDATE || + parsetree->onConflict->action == ONCONFLICT_SELECT)) { Index old_exclRelIndex, new_exclRelIndex; @@ -3656,9 +3670,8 @@ rewriteTargetView(Query *parsetree, Relation view) List *tmp_tlist; /* - * Like the INSERT/UPDATE code above, update the resnos in the - * auxiliary UPDATE targetlist to refer to columns of the base - * relation. + * For ON CONFLICT DO UPDATE, update the resnos in the auxiliary + * UPDATE targetlist to refer to columns of the base relation. */ foreach(lc, parsetree->onConflict->onConflictSet) { @@ -3677,7 +3690,7 @@ rewriteTargetView(Query *parsetree, Relation view) } /* - * Also, create a new RTE for the EXCLUDED pseudo-relation, using the + * Create a new RTE for the EXCLUDED pseudo-relation, using the * query's new base rel (which may well have a different column list * from the view, hence we need a new column alias list). This should * match transformOnConflictClause. In particular, note that the diff --git a/src/backend/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.c index 93a205d02bc..e88a1bc1a89 100644 --- a/src/backend/rewrite/rowsecurity.c +++ b/src/backend/rewrite/rowsecurity.c @@ -301,40 +301,48 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index, } /* - * For INSERT ... ON CONFLICT DO UPDATE we need additional policy - * checks for the UPDATE which may be applied to the same RTE. + * For INSERT ... ON CONFLICT DO SELECT/UPDATE we need additional + * policy checks for the SELECT/UPDATE which may be applied to the + * same RTE. */ - if (commandType == CMD_INSERT && - root->onConflict && root->onConflict->action == ONCONFLICT_UPDATE) + if (commandType == CMD_INSERT && root->onConflict && + (root->onConflict->action == ONCONFLICT_UPDATE || + root->onConflict->action == ONCONFLICT_SELECT)) { - List *conflict_permissive_policies; - List *conflict_restrictive_policies; + List *conflict_permissive_policies = NIL; + List *conflict_restrictive_policies = NIL; List *conflict_select_permissive_policies = NIL; List *conflict_select_restrictive_policies = NIL; - /* Get the policies that apply to the auxiliary UPDATE */ - get_policies_for_relation(rel, CMD_UPDATE, user_id, - &conflict_permissive_policies, - &conflict_restrictive_policies); - - /* - * Enforce the USING clauses of the UPDATE policies using WCOs - * rather than security quals. This ensures that an error is - * raised if the conflicting row cannot be updated due to RLS, - * rather than the change being silently dropped. - */ - add_with_check_options(rel, rt_index, - WCO_RLS_CONFLICT_CHECK, - conflict_permissive_policies, - conflict_restrictive_policies, - withCheckOptions, - hasSubLinks, - true); + if (perminfo->requiredPerms & ACL_UPDATE) + { + /* + * Get the policies that apply to the auxiliary UPDATE or + * SELECT FOR UPDATE/SHARE. + */ + get_policies_for_relation(rel, CMD_UPDATE, user_id, + &conflict_permissive_policies, + &conflict_restrictive_policies); + + /* + * Enforce the USING clauses of the UPDATE policies using WCOs + * rather than security quals. This ensures that an error is + * raised if the conflicting row cannot be updated/locked due + * to RLS, rather than the change being silently dropped. + */ + add_with_check_options(rel, rt_index, + WCO_RLS_CONFLICT_CHECK, + conflict_permissive_policies, + conflict_restrictive_policies, + withCheckOptions, + hasSubLinks, + true); + } /* * Get and add ALL/SELECT policies, as WCO_RLS_CONFLICT_CHECK WCOs - * to ensure they are considered when taking the UPDATE path of an - * INSERT .. ON CONFLICT DO UPDATE, if SELECT rights are required + * to ensure they are considered when taking the SELECT/UPDATE + * path of an INSERT .. ON CONFLICT, if SELECT rights are required * for this relation, also as WCO policies, again, to avoid * silently dropping data. See above. */ @@ -352,29 +360,36 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index, true); } - /* Enforce the WITH CHECK clauses of the UPDATE policies */ - add_with_check_options(rel, rt_index, - WCO_RLS_UPDATE_CHECK, - conflict_permissive_policies, - conflict_restrictive_policies, - withCheckOptions, - hasSubLinks, - false); - /* - * Add ALL/SELECT policies as WCO_RLS_UPDATE_CHECK WCOs, to ensure - * that the final updated row is visible when taking the UPDATE - * path of an INSERT .. ON CONFLICT DO UPDATE, if SELECT rights - * are required for this relation. + * For INSERT .. ON CONFLICT DO UPDATE, add additional policies to + * be checked when the auxiliary UPDATE is executed. */ - if (perminfo->requiredPerms & ACL_SELECT) + if (root->onConflict->action == ONCONFLICT_UPDATE) + { + /* Enforce the WITH CHECK clauses of the UPDATE policies */ add_with_check_options(rel, rt_index, WCO_RLS_UPDATE_CHECK, - conflict_select_permissive_policies, - conflict_select_restrictive_policies, + conflict_permissive_policies, + conflict_restrictive_policies, withCheckOptions, hasSubLinks, - true); + false); + + /* + * Add ALL/SELECT policies as WCO_RLS_UPDATE_CHECK WCOs, to + * ensure that the final updated row is visible when taking + * the UPDATE path of an INSERT .. ON CONFLICT, if SELECT + * rights are required for this relation. + */ + if (perminfo->requiredPerms & ACL_SELECT) + add_with_check_options(rel, rt_index, + WCO_RLS_UPDATE_CHECK, + conflict_select_permissive_policies, + conflict_select_restrictive_policies, + withCheckOptions, + hasSubLinks, + true); + } } } @@ -398,8 +413,8 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index, * XXX We are setting up USING quals as WITH CHECK. If RLS prohibits * UPDATE/DELETE on the target row, we shall throw an error instead of * silently ignoring the row. This is different than how normal - * UPDATE/DELETE works and more in line with INSERT ON CONFLICT DO UPDATE - * handling. + * UPDATE/DELETE works and more in line with INSERT ON CONFLICT DO + * SELECT/UPDATE handling. */ if (commandType == CMD_MERGE) { @@ -784,9 +799,9 @@ add_security_quals(int rt_index, * added by an INSERT or UPDATE are consistent with the specified RLS * policies. Normally new data must satisfy the WITH CHECK clauses from the * policies. If a policy has no explicit WITH CHECK clause, its USING clause - * is used instead. In the special case of an UPDATE arising from an - * INSERT ... ON CONFLICT DO UPDATE, existing records are first checked using - * a WCO_RLS_CONFLICT_CHECK WithCheckOption, which always uses the USING + * is used instead. In the special case of a SELECT or UPDATE arising from an + * INSERT ... ON CONFLICT DO SELECT/UPDATE, existing records are first checked + * using a WCO_RLS_CONFLICT_CHECK WithCheckOption, which always uses the USING * clauses from RLS policies. * * New WCOs are added to withCheckOptions, and hasSubLinks is set to true if diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index b5a7ad9066e..89cbdd3b1e7 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -426,6 +426,7 @@ static void get_update_query_targetlist_def(Query *query, List *targetList, static void get_delete_query_def(Query *query, deparse_context *context); static void get_merge_query_def(Query *query, deparse_context *context); static void get_utility_query_def(Query *query, deparse_context *context); +static char *get_lock_clause_strength(LockClauseStrength strength); static void get_basic_select_query(Query *query, deparse_context *context); static void get_target_list(List *targetList, deparse_context *context); static void get_returning_clause(Query *query, deparse_context *context); @@ -5186,10 +5187,10 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan) * source, and all INNER_VAR Vars in other parts of the query refer to its * targetlist. * - * For ON CONFLICT .. UPDATE we just need the inner tlist to point to the - * excluded expression's tlist. (Similar to the SubqueryScan we don't want - * to reuse OUTER, it's used for RETURNING in some modify table cases, - * although not INSERT .. CONFLICT). + * For ON CONFLICT DO SELECT/UPDATE we just need the inner tlist to point + * to the excluded expression's tlist. (Similar to the SubqueryScan we + * don't want to reuse OUTER, it's used for RETURNING in some modify table + * cases, although not INSERT .. CONFLICT). */ if (IsA(plan, SubqueryScan)) dpns->inner_plan = ((SubqueryScan *) plan)->subplan; @@ -5997,30 +5998,9 @@ get_select_query_def(Query *query, deparse_context *context) if (rc->pushedDown) continue; - switch (rc->strength) - { - case LCS_NONE: - /* we intentionally throw an error for LCS_NONE */ - elog(ERROR, "unrecognized LockClauseStrength %d", - (int) rc->strength); - break; - case LCS_FORKEYSHARE: - appendContextKeyword(context, " FOR KEY SHARE", - -PRETTYINDENT_STD, PRETTYINDENT_STD, 0); - break; - case LCS_FORSHARE: - appendContextKeyword(context, " FOR SHARE", - -PRETTYINDENT_STD, PRETTYINDENT_STD, 0); - break; - case LCS_FORNOKEYUPDATE: - appendContextKeyword(context, " FOR NO KEY UPDATE", - -PRETTYINDENT_STD, PRETTYINDENT_STD, 0); - break; - case LCS_FORUPDATE: - appendContextKeyword(context, " FOR UPDATE", - -PRETTYINDENT_STD, PRETTYINDENT_STD, 0); - break; - } + appendContextKeyword(context, + get_lock_clause_strength(rc->strength), + -PRETTYINDENT_STD, PRETTYINDENT_STD, 0); appendStringInfo(buf, " OF %s", quote_identifier(get_rtable_name(rc->rti, @@ -6033,6 +6013,28 @@ get_select_query_def(Query *query, deparse_context *context) } } +static char * +get_lock_clause_strength(LockClauseStrength strength) +{ + switch (strength) + { + case LCS_NONE: + /* we intentionally throw an error for LCS_NONE */ + elog(ERROR, "unrecognized LockClauseStrength %d", + (int) strength); + break; + case LCS_FORKEYSHARE: + return " FOR KEY SHARE"; + case LCS_FORSHARE: + return " FOR SHARE"; + case LCS_FORNOKEYUPDATE: + return " FOR NO KEY UPDATE"; + case LCS_FORUPDATE: + return " FOR UPDATE"; + } + return NULL; /* keep compiler quiet */ +} + /* * Detect whether query looks like SELECT ... FROM VALUES(), * with no need to rename the output columns of the VALUES RTE. @@ -7125,7 +7127,7 @@ get_insert_query_def(Query *query, deparse_context *context) { appendStringInfoString(buf, " DO NOTHING"); } - else + else if (confl->action == ONCONFLICT_UPDATE) { appendStringInfoString(buf, " DO UPDATE SET "); /* Deparse targetlist */ @@ -7140,6 +7142,23 @@ get_insert_query_def(Query *query, deparse_context *context) get_rule_expr(confl->onConflictWhere, context, false); } } + else + { + Assert(confl->action == ONCONFLICT_SELECT); + appendStringInfoString(buf, " DO SELECT"); + + /* Add FOR [KEY] UPDATE/SHARE clause if present */ + if (confl->lockStrength != LCS_NONE) + appendStringInfoString(buf, get_lock_clause_strength(confl->lockStrength)); + + /* Add a WHERE clause if given */ + if (confl->onConflictWhere != NULL) + { + appendContextKeyword(context, " WHERE ", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 1); + get_rule_expr(confl->onConflictWhere, context, false); + } + } } /* Add RETURNING if present */ diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index a910b3d04e6..0bdd42a2b86 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202602101 +#define CATALOG_VERSION_NO 202602121 #endif diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index f8053d9e572..63c067d5aae 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -424,19 +424,20 @@ typedef struct JunkFilter } JunkFilter; /* - * OnConflictSetState + * OnConflictActionState * - * Executor state of an ON CONFLICT DO UPDATE operation. + * Executor state of an ON CONFLICT DO SELECT/UPDATE operation. */ -typedef struct OnConflictSetState +typedef struct OnConflictActionState { NodeTag type; TupleTableSlot *oc_Existing; /* slot to store existing target tuple in */ TupleTableSlot *oc_ProjSlot; /* CONFLICT ... SET ... projection target */ ProjectionInfo *oc_ProjInfo; /* for ON CONFLICT DO UPDATE SET */ + LockClauseStrength oc_LockStrength; /* lock strength for DO SELECT */ ExprState *oc_WhereClause; /* state for the WHERE clause */ -} OnConflictSetState; +} OnConflictActionState; /* ---------------- * MergeActionState information @@ -581,8 +582,8 @@ typedef struct ResultRelInfo /* list of arbiter indexes to use to check conflicts */ List *ri_onConflictArbiterIndexes; - /* ON CONFLICT evaluation state */ - OnConflictSetState *ri_onConflict; + /* ON CONFLICT evaluation state for DO SELECT/UPDATE */ + OnConflictActionState *ri_onConflict; /* for MERGE, lists of MergeActionState (one per MergeMatchKind) */ List *ri_MergeActions[NUM_MERGE_MATCH_KINDS]; diff --git a/src/include/nodes/lockoptions.h b/src/include/nodes/lockoptions.h index 22864454c3e..7961444eed1 100644 --- a/src/include/nodes/lockoptions.h +++ b/src/include/nodes/lockoptions.h @@ -20,7 +20,8 @@ */ typedef enum LockClauseStrength { - LCS_NONE, /* no such clause - only used in PlanRowMark */ + LCS_NONE, /* no such clause - only used in PlanRowMark + * and ON CONFLICT DO SELECT */ LCS_FORKEYSHARE, /* FOR KEY SHARE */ LCS_FORSHARE, /* FOR SHARE */ LCS_FORNOKEYUPDATE, /* FOR NO KEY UPDATE */ diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index b6ad28618ab..59a7df31aba 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -428,6 +428,7 @@ typedef enum OnConflictAction ONCONFLICT_NONE, /* No "ON CONFLICT" clause */ ONCONFLICT_NOTHING, /* ON CONFLICT ... DO NOTHING */ ONCONFLICT_UPDATE, /* ON CONFLICT ... DO UPDATE */ + ONCONFLICT_SELECT, /* ON CONFLICT ... DO SELECT */ } OnConflictAction; /* diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 646d6ced763..0aec49bdd22 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -200,7 +200,7 @@ typedef struct Query /* OVERRIDING clause */ OverridingKind override pg_node_attr(query_jumble_ignore); - OnConflictExpr *onConflict; /* ON CONFLICT DO [NOTHING | UPDATE] */ + OnConflictExpr *onConflict; /* ON CONFLICT DO NOTHING/SELECT/UPDATE */ /* * The following three fields describe the contents of the RETURNING list @@ -1417,7 +1417,8 @@ typedef enum WCOKind WCO_VIEW_CHECK, /* WCO on an auto-updatable view */ WCO_RLS_INSERT_CHECK, /* RLS INSERT WITH CHECK policy */ WCO_RLS_UPDATE_CHECK, /* RLS UPDATE WITH CHECK policy */ - WCO_RLS_CONFLICT_CHECK, /* RLS ON CONFLICT DO UPDATE USING policy */ + WCO_RLS_CONFLICT_CHECK, /* RLS ON CONFLICT DO SELECT/UPDATE USING + * policy */ WCO_RLS_MERGE_UPDATE_CHECK, /* RLS MERGE UPDATE USING policy */ WCO_RLS_MERGE_DELETE_CHECK, /* RLS MERGE DELETE USING policy */ } WCOKind; @@ -1679,9 +1680,10 @@ typedef struct InferClause typedef struct OnConflictClause { NodeTag type; - OnConflictAction action; /* DO NOTHING or UPDATE? */ + OnConflictAction action; /* DO NOTHING, SELECT, or UPDATE */ InferClause *infer; /* Optional index inference clause */ - List *targetList; /* the target list (of ResTarget) */ + LockClauseStrength lockStrength; /* lock strength for DO SELECT */ + List *targetList; /* target list (of ResTarget) for DO UPDATE */ Node *whereClause; /* qualifications */ ParseLoc location; /* token location, or -1 if unknown */ } OnConflictClause; diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 485bec5aabd..8c9321aab8c 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -368,11 +368,13 @@ typedef struct ModifyTable OnConflictAction onConflictAction; /* List of ON CONFLICT arbiter index OIDs */ List *arbiterIndexes; + /* lock strength for ON CONFLICT DO SELECT */ + LockClauseStrength onConflictLockStrength; /* INSERT ON CONFLICT DO UPDATE targetlist */ List *onConflictSet; /* target column numbers for onConflictSet */ List *onConflictCols; - /* WHERE for ON CONFLICT UPDATE */ + /* WHERE for ON CONFLICT DO SELECT/UPDATE */ Node *onConflictWhere; /* RTI of the EXCLUDED pseudo relation */ Index exclRelRTI; diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 5211cadc258..384df50c80a 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -20,6 +20,7 @@ #include "access/attnum.h" #include "access/cmptype.h" #include "nodes/bitmapset.h" +#include "nodes/lockoptions.h" #include "nodes/pg_list.h" @@ -2370,7 +2371,7 @@ typedef struct FromExpr typedef struct OnConflictExpr { NodeTag type; - OnConflictAction action; /* DO NOTHING or UPDATE? */ + OnConflictAction action; /* DO NOTHING, SELECT, or UPDATE */ /* Arbiter */ List *arbiterElems; /* unique index arbiter list (of @@ -2378,9 +2379,14 @@ typedef struct OnConflictExpr Node *arbiterWhere; /* unique index arbiter WHERE clause */ Oid constraint; /* pg_constraint OID for arbiter */ - /* ON CONFLICT UPDATE */ + /* ON CONFLICT DO SELECT */ + LockClauseStrength lockStrength; /* strength of lock for DO SELECT */ + + /* ON CONFLICT DO UPDATE */ List *onConflictSet; /* List of ON CONFLICT SET TargetEntrys */ - Node *onConflictWhere; /* qualifiers to restrict UPDATE to */ + + /* both ON CONFLICT DO SELECT and UPDATE */ + Node *onConflictWhere; /* qualifiers to restrict SELECT/UPDATE */ int exclRelIndex; /* RT index of 'excluded' relation */ List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */ } OnConflictExpr; diff --git a/src/test/isolation/expected/insert-conflict-do-select.out b/src/test/isolation/expected/insert-conflict-do-select.out new file mode 100644 index 00000000000..bccfd47dcfb --- /dev/null +++ b/src/test/isolation/expected/insert-conflict-do-select.out @@ -0,0 +1,138 @@ +Parsed test spec with 2 sessions + +starting permutation: insert1 insert2 c1 select2 c2 +step insert1: INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT RETURNING *; +key|val +---+-------- + 1|original +(1 row) + +step insert2: INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT RETURNING *; +key|val +---+-------- + 1|original +(1 row) + +step c1: COMMIT; +step select2: SELECT * FROM doselect; +key|val +---+-------- + 1|original +(1 row) + +step c2: COMMIT; + +starting permutation: insert1_update insert2_update c1 select2 c2 +step insert1_update: INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; +key|val +---+-------- + 1|original +(1 row) + +step insert2_update: INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; +step c1: COMMIT; +step insert2_update: <... completed> +key|val +---+-------- + 1|original +(1 row) + +step select2: SELECT * FROM doselect; +key|val +---+-------- + 1|original +(1 row) + +step c2: COMMIT; + +starting permutation: insert1_update insert2_update a1 select2 c2 +step insert1_update: INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; +key|val +---+-------- + 1|original +(1 row) + +step insert2_update: INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; +step a1: ABORT; +step insert2_update: <... completed> +key|val +---+-------- + 1|original +(1 row) + +step select2: SELECT * FROM doselect; +key|val +---+-------- + 1|original +(1 row) + +step c2: COMMIT; + +starting permutation: insert1_keyshare insert2_update c1 select2 c2 +step insert1_keyshare: INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR KEY SHARE RETURNING *; +key|val +---+-------- + 1|original +(1 row) + +step insert2_update: INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; +step c1: COMMIT; +step insert2_update: <... completed> +key|val +---+-------- + 1|original +(1 row) + +step select2: SELECT * FROM doselect; +key|val +---+-------- + 1|original +(1 row) + +step c2: COMMIT; + +starting permutation: insert1_share insert2_update c1 select2 c2 +step insert1_share: INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR SHARE RETURNING *; +key|val +---+-------- + 1|original +(1 row) + +step insert2_update: INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; +step c1: COMMIT; +step insert2_update: <... completed> +key|val +---+-------- + 1|original +(1 row) + +step select2: SELECT * FROM doselect; +key|val +---+-------- + 1|original +(1 row) + +step c2: COMMIT; + +starting permutation: insert1_nokeyupd insert2_update c1 select2 c2 +step insert1_nokeyupd: INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR NO KEY UPDATE RETURNING *; +key|val +---+-------- + 1|original +(1 row) + +step insert2_update: INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; +step c1: COMMIT; +step insert2_update: <... completed> +key|val +---+-------- + 1|original +(1 row) + +step select2: SELECT * FROM doselect; +key|val +---+-------- + 1|original +(1 row) + +step c2: COMMIT; diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index 6a4d3532e03..4e466580cd4 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -54,6 +54,7 @@ test: insert-conflict-do-update-2 test: insert-conflict-do-update-3 test: insert-conflict-do-update-4 test: insert-conflict-specconflict +test: insert-conflict-do-select test: merge-insert-update test: merge-delete test: merge-update diff --git a/src/test/isolation/specs/insert-conflict-do-select.spec b/src/test/isolation/specs/insert-conflict-do-select.spec new file mode 100644 index 00000000000..dcfd9f8cb53 --- /dev/null +++ b/src/test/isolation/specs/insert-conflict-do-select.spec @@ -0,0 +1,53 @@ +# INSERT...ON CONFLICT DO SELECT test +# +# This test verifies locking behavior of ON CONFLICT DO SELECT with different +# lock strengths: no lock, FOR KEY SHARE, FOR SHARE, FOR NO KEY UPDATE, and +# FOR UPDATE. + +setup +{ + CREATE TABLE doselect (key int primary key, val text); + INSERT INTO doselect VALUES (1, 'original'); +} + +teardown +{ + DROP TABLE doselect; +} + +session s1 +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step insert1 { INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT RETURNING *; } +step insert1_keyshare { INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR KEY SHARE RETURNING *; } +step insert1_share { INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR SHARE RETURNING *; } +step insert1_nokeyupd { INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR NO KEY UPDATE RETURNING *; } +step insert1_update { INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; } +step c1 { COMMIT; } +step a1 { ABORT; } + +session s2 +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step insert2 { INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT RETURNING *; } +step insert2_update { INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; } +step select2 { SELECT * FROM doselect; } +step c2 { COMMIT; } + +# Test 1: DO SELECT without locking - should not block +permutation insert1 insert2 c1 select2 c2 + +# Test 2: DO SELECT FOR UPDATE - should block until first transaction commits +permutation insert1_update insert2_update c1 select2 c2 + +# Test 3: DO SELECT FOR UPDATE - should unblock when first transaction aborts +permutation insert1_update insert2_update a1 select2 c2 + +# Test 4: Different lock strengths all properly acquire locks +permutation insert1_keyshare insert2_update c1 select2 c2 +permutation insert1_share insert2_update c1 select2 c2 +permutation insert1_nokeyupd insert2_update c1 select2 c2 diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index ebc892a2a42..a6fa9cacb72 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -780,6 +780,10 @@ INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO UPDATE SET c2 = EXCLUDED.c2; ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints +-- fail, because DO SELECT variant requires unique index +INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') + ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO SELECT RETURNING *; +ERROR: ON CONFLICT DO SELECT not supported with exclusion constraints -- succeed because c1 doesn't overlap INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>'); -- succeed because c2 doesn't overlap diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out index b0e12962088..34e2e7ee355 100644 --- a/src/test/regress/expected/insert_conflict.out +++ b/src/test/regress/expected/insert_conflict.out @@ -249,6 +249,25 @@ explain (costs off, format json) insert into insertconflicttest values (0, 'Bilb ] (1 row) +-- Should display lock strength, if specified +explain (costs off) insert into insertconflicttest values (1, 'Apple') on conflict (key) do select returning *; + QUERY PLAN +--------------------------------------- + Insert on insertconflicttest + Conflict Resolution: SELECT + Conflict Arbiter Indexes: key_index + -> Result +(4 rows) + +explain (costs off) insert into insertconflicttest values (1, 'Apple') on conflict (key) do select for key share returning *; + QUERY PLAN +--------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: SELECT FOR KEY SHARE + Conflict Arbiter Indexes: key_index + -> Result +(4 rows) + -- Fails (no unique index inference specification, required for do update variant): insert into insertconflicttest values (1, 'Apple') on conflict do update set fruit = excluded.fruit; ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name @@ -304,6 +323,48 @@ ERROR: column "insertconflicttest" of relation "insertconflicttest" does not ex LINE 1: ...3, 'Kiwi') on conflict (key, fruit) do update set insertconf... ^ HINT: SET target columns cannot be qualified with the relation name. +-- +-- DO SELECT tests +-- +delete from insertconflicttest where fruit = 'Apple'; +insert into insertconflicttest values (1, 'Apple') on conflict (key) do select; -- fails +ERROR: ON CONFLICT DO SELECT requires a RETURNING clause +LINE 1: ...nsert into insertconflicttest values (1, 'Apple') on conflic... + ^ +insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do select returning old, new, i; + old | new | i +-----+-----------+----------- + | (1,Apple) | (1,Apple) +(1 row) + +insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select returning old, new, i; + old | new | i +-----------+-----------+----------- + (1,Apple) | (1,Apple) | (1,Apple) +(1 row) + +insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Apple' returning *; + key | fruit +-----+------- + 1 | Apple +(1 row) + +insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Orange' returning *; + key | fruit +-----+------- +(0 rows) + +insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select where excluded.fruit = 'Apple' returning *; + key | fruit +-----+------- +(0 rows) + +insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select where excluded.fruit = 'Orange' returning *; + key | fruit +-----+------- + 1 | Apple +(1 row) + drop index key_index; -- -- Composite key tests @@ -748,13 +809,58 @@ insert into selfconflict values (6,1), (6,2) on conflict(f1) do update set f2 = ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. commit; +begin transaction isolation level read committed; +insert into selfconflict values (7,1), (7,2) on conflict(f1) do select returning *; + f1 | f2 +----+---- + 7 | 1 + 7 | 1 +(2 rows) + +commit; +begin transaction isolation level repeatable read; +insert into selfconflict values (8,1), (8,2) on conflict(f1) do select returning *; + f1 | f2 +----+---- + 8 | 1 + 8 | 1 +(2 rows) + +commit; +begin transaction isolation level serializable; +insert into selfconflict values (9,1), (9,2) on conflict(f1) do select returning *; + f1 | f2 +----+---- + 9 | 1 + 9 | 1 +(2 rows) + +commit; +begin transaction isolation level read committed; +insert into selfconflict values (10,1), (10,2) on conflict(f1) do select for update returning *; +ERROR: ON CONFLICT DO SELECT command cannot affect row a second time +HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. +commit; +begin transaction isolation level repeatable read; +insert into selfconflict values (11,1), (11,2) on conflict(f1) do select for update returning *; +ERROR: ON CONFLICT DO SELECT command cannot affect row a second time +HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. +commit; +begin transaction isolation level serializable; +insert into selfconflict values (12,1), (12,2) on conflict(f1) do select for update returning *; +ERROR: ON CONFLICT DO SELECT command cannot affect row a second time +HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. +commit; select * from selfconflict; f1 | f2 ----+---- 1 | 1 2 | 1 3 | 1 -(3 rows) + 7 | 1 + 8 | 1 + 9 | 1 +(6 rows) drop table selfconflict; -- check ON CONFLICT handling with partitioned tables @@ -765,11 +871,31 @@ insert into parted_conflict_test values (1, 'a') on conflict do nothing; -- index on a required, which does exist in parent insert into parted_conflict_test values (1, 'a') on conflict (a) do nothing; insert into parted_conflict_test values (1, 'a') on conflict (a) do update set b = excluded.b; +insert into parted_conflict_test values (1, 'a') on conflict (a) do select returning *; + a | b +---+--- + 1 | a +(1 row) + +insert into parted_conflict_test values (1, 'a') on conflict (a) do select for update returning *; + a | b +---+--- + 1 | a +(1 row) + -- targeting partition directly will work insert into parted_conflict_test_1 values (1, 'a') on conflict (a) do nothing; insert into parted_conflict_test_1 values (1, 'b') on conflict (a) do update set b = excluded.b; +insert into parted_conflict_test_1 values (1, 'b') on conflict (a) do select returning b; + b +--- + b +(1 row) + -- index on b required, which doesn't exist in parent -insert into parted_conflict_test values (2, 'b') on conflict (b) do update set a = excluded.a; +insert into parted_conflict_test values (2, 'b') on conflict (b) do update set a = excluded.a; -- fail +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into parted_conflict_test values (2, 'b') on conflict (b) do select returning b; -- fail ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- targeting partition directly will work insert into parted_conflict_test_1 values (2, 'b') on conflict (b) do update set a = excluded.a; @@ -780,13 +906,31 @@ select * from parted_conflict_test order by a; 2 | b (1 row) --- now check that DO UPDATE works correctly for target partition with --- different attribute numbers +-- now check that DO UPDATE and DO SELECT work correctly for target partition +-- with different attribute numbers create table parted_conflict_test_2 (b char, a int unique); alter table parted_conflict_test attach partition parted_conflict_test_2 for values in (3); truncate parted_conflict_test; insert into parted_conflict_test values (3, 'a') on conflict (a) do update set b = excluded.b; insert into parted_conflict_test values (3, 'b') on conflict (a) do update set b = excluded.b; +insert into parted_conflict_test values (3, 'a') on conflict (a) do select returning b; + b +--- + b +(1 row) + +insert into parted_conflict_test values (3, 'a') on conflict (a) do select where excluded.b = 'a' returning parted_conflict_test; + parted_conflict_test +---------------------- + (3,b) +(1 row) + +insert into parted_conflict_test values (3, 'a') on conflict (a) do select where parted_conflict_test.b = 'b' returning b; + b +--- + b +(1 row) + -- should see (3, 'b') select * from parted_conflict_test order by a; a | b @@ -800,6 +944,12 @@ create table parted_conflict_test_3 partition of parted_conflict_test for values truncate parted_conflict_test; insert into parted_conflict_test (a, b) values (4, 'a') on conflict (a) do update set b = excluded.b; insert into parted_conflict_test (a, b) values (4, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a'; +insert into parted_conflict_test (a, b) values (4, 'b') on conflict (a) do select returning b; + b +--- + b +(1 row) + -- should see (4, 'b') select * from parted_conflict_test order by a; a | b @@ -813,6 +963,11 @@ create table parted_conflict_test_4_1 partition of parted_conflict_test_4 for va truncate parted_conflict_test; insert into parted_conflict_test (a, b) values (5, 'a') on conflict (a) do update set b = excluded.b; insert into parted_conflict_test (a, b) values (5, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a'; +insert into parted_conflict_test (a, b) values (5, 'b') on conflict (a) do select where parted_conflict_test.b = 'a' returning b; + b +--- +(0 rows) + -- should see (5, 'b') select * from parted_conflict_test order by a; a | b @@ -833,6 +988,59 @@ select * from parted_conflict_test order by a; 4 | b (3 rows) +-- test DO SELECT with multiple rows hitting different partitions +truncate parted_conflict_test; +insert into parted_conflict_test (a, b) values (1, 'a'), (2, 'b'), (4, 'c'); +insert into parted_conflict_test (a, b) values (1, 'x'), (2, 'y'), (4, 'z') + on conflict (a) do select returning *, tableoid::regclass; + a | b | tableoid +---+---+------------------------ + 1 | a | parted_conflict_test_1 + 2 | b | parted_conflict_test_1 + 4 | c | parted_conflict_test_3 +(3 rows) + +-- should see original values (1, 'a'), (2, 'b'), (4, 'c') +select * from parted_conflict_test order by a; + a | b +---+--- + 1 | a + 2 | b + 4 | c +(3 rows) + +-- test DO SELECT with WHERE filtering across partitions +insert into parted_conflict_test (a, b) values (1, 'n') on conflict (a) do select where parted_conflict_test.b = 'a' returning *; + a | b +---+--- + 1 | a +(1 row) + +insert into parted_conflict_test (a, b) values (2, 'n') on conflict (a) do select where parted_conflict_test.b = 'x' returning *; + a | b +---+--- +(0 rows) + +-- test DO SELECT with EXCLUDED in WHERE across partitions with different layouts +insert into parted_conflict_test (a, b) values (3, 't') on conflict (a) do select where excluded.b = 't' returning *; + a | b +---+--- + 3 | t +(1 row) + +-- test DO SELECT FOR UPDATE across different partition layouts +insert into parted_conflict_test (a, b) values (1, 'l') on conflict (a) do select for update returning *; + a | b +---+--- + 1 | a +(1 row) + +insert into parted_conflict_test (a, b) values (3, 'l') on conflict (a) do select for update returning *; + a | b +---+--- + 3 | t +(1 row) + drop table parted_conflict_test; -- test behavior of inserting a conflicting tuple into an intermediate -- partitioning level diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index daafaa94fde..84c1c1ca38d 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -921,6 +921,32 @@ INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set one = 8; -- f ERROR: permission denied for table atest5 INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10; -- fails (due to INSERT) ERROR: permission denied for table atest5 +-- Check that column level privileges are enforced for ON CONFLICT ... WHERE +-- Ok. we may select one +INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT WHERE atest5.one = 1 RETURNING atest5.two; + two +----- + 2 +(1 row) + +-- Error. No select rights on three +INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT WHERE atest5.three = 1 RETURNING atest5.two; +ERROR: permission denied for table atest5 +-- Check that ON CONFLICT ... SELECT FOR UPDATE/SHARE requires an updatable column +SET SESSION AUTHORIZATION regress_priv_user1; +REVOKE UPDATE (three) ON atest5 FROM regress_priv_user4; +SET SESSION AUTHORIZATION regress_priv_user4; +INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT FOR UPDATE RETURNING atest5.two; -- fails +ERROR: permission denied for table atest5 +SET SESSION AUTHORIZATION regress_priv_user1; +GRANT UPDATE (three) ON atest5 TO regress_priv_user4; +SET SESSION AUTHORIZATION regress_priv_user4; +INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT FOR UPDATE RETURNING atest5.two; -- ok + two +----- + 2 +(1 row) + -- Check that the columns in the inference require select privileges INSERT INTO atest5(four) VALUES (4); -- fail ERROR: permission denied for table atest5 diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index e17f9188dfa..07d93e7def1 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -218,6 +218,50 @@ NOTICE: SELECT USING on rls_test_tgt.(3,"tgt d","TGT D") 3 | tgt d | TGT D (1 row) +ROLLBACK; +-- INSERT ... ON CONFLICT DO SELECT should apply INSERT CHECK and SELECT USING +-- policy clauses to values proposed for insert. In the event of a conflict it +-- should also apply SELECT USING policy clauses to the existing values. +BEGIN; +INSERT INTO rls_test_tgt VALUES (4, 'tgt a') ON CONFLICT (a) DO SELECT RETURNING *; +NOTICE: INSERT CHECK on rls_test_tgt.(4,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(4,"tgt a","TGT A") + a | b | c +---+-------+------- + 4 | tgt a | TGT A +(1 row) + +INSERT INTO rls_test_tgt VALUES (4, 'tgt b') ON CONFLICT (a) DO SELECT RETURNING *; +NOTICE: INSERT CHECK on rls_test_tgt.(4,"tgt b","TGT B") +NOTICE: SELECT USING on rls_test_tgt.(4,"tgt b","TGT B") +NOTICE: SELECT USING on rls_test_tgt.(4,"tgt a","TGT A") + a | b | c +---+-------+------- + 4 | tgt a | TGT A +(1 row) + +ROLLBACK; +-- INSERT ... ON CONFLICT DO SELECT FOR UPDATE should also apply UPDATE USING +-- policy clauses to the existing values, in the event of a conflict. +BEGIN; +INSERT INTO rls_test_tgt VALUES (5, 'tgt a') ON CONFLICT (a) DO SELECT FOR UPDATE RETURNING *; +NOTICE: INSERT CHECK on rls_test_tgt.(5,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(5,"tgt a","TGT A") + a | b | c +---+-------+------- + 5 | tgt a | TGT A +(1 row) + +INSERT INTO rls_test_tgt VALUES (5, 'tgt b') ON CONFLICT (a) DO SELECT FOR UPDATE RETURNING *; +NOTICE: INSERT CHECK on rls_test_tgt.(5,"tgt b","TGT B") +NOTICE: SELECT USING on rls_test_tgt.(5,"tgt b","TGT B") +NOTICE: UPDATE USING on rls_test_tgt.(5,"tgt a","TGT A") +NOTICE: SELECT USING on rls_test_tgt.(5,"tgt a","TGT A") + a | b | c +---+-------+------- + 5 | tgt a | TGT A +(1 row) + ROLLBACK; -- MERGE should always apply SELECT USING policy clauses to both source and -- target rows @@ -2395,10 +2439,58 @@ INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel') ON CONFLICT (did) DO UPDATE SET dauthor = 'regress_rls_carol'; ERROR: new row violates row-level security policy for table "document" -- +-- INSERT ... ON CONFLICT DO SELECT and Row-level security +-- +SET SESSION AUTHORIZATION regress_rls_alice; +DROP POLICY p3_with_all ON document; +CREATE POLICY p1_select_novels ON document FOR SELECT + USING (cid = (SELECT cid from category WHERE cname = 'novel')); +CREATE POLICY p2_insert_own ON document FOR INSERT + WITH CHECK (dauthor = current_user); +CREATE POLICY p3_update_novels ON document FOR UPDATE + USING (cid = (SELECT cid from category WHERE cname = 'novel') AND dlevel = 1) + WITH CHECK (dauthor = current_user); +SET SESSION AUTHORIZATION regress_rls_bob; +-- DO SELECT requires SELECT rights, should succeed for novel +INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel') + ON CONFLICT (did) DO SELECT RETURNING did, dauthor, dtitle; + did | dauthor | dtitle +-----+-----------------+---------------- + 1 | regress_rls_bob | my first novel +(1 row) + +-- DO SELECT requires SELECT rights, should fail for non-novel +INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'science fiction'), 1, 'regress_rls_bob', 'another sci-fi') + ON CONFLICT (did) DO SELECT RETURNING did, dauthor, dtitle; +ERROR: new row violates row-level security policy for table "document" +-- DO SELECT with WHERE and EXCLUDED reference +INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel') + ON CONFLICT (did) DO SELECT WHERE excluded.dlevel = 1 RETURNING did, dauthor, dtitle; + did | dauthor | dtitle +-----+-----------------+---------------- + 1 | regress_rls_bob | my first novel +(1 row) + +-- DO SELECT FOR UPDATE requires both SELECT and UPDATE rights, should succeed for novel and dlevel = 1 +INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel') + ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle; + did | dauthor | dtitle +-----+-----------------+---------------- + 1 | regress_rls_bob | my first novel +(1 row) + +-- should fail UPDATE USING policy for novel with dlevel = 2 +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel') + ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle; +ERROR: new row violates row-level security policy (USING expression) for table "document" +SET SESSION AUTHORIZATION regress_rls_alice; +DROP POLICY p1_select_novels ON document; +DROP POLICY p2_insert_own ON document; +DROP POLICY p3_update_novels ON document; +-- -- MERGE -- RESET SESSION AUTHORIZATION; -DROP POLICY p3_with_all ON document; ALTER TABLE document ADD COLUMN dnotes text DEFAULT ''; -- all documents are readable CREATE POLICY p1 ON document FOR SELECT USING (true); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index f9bc213e5a1..78a37d9fc8f 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -3608,6 +3608,61 @@ SELECT * FROM hat_data WHERE hat_name IN ('h8', 'h9', 'h7') ORDER BY hat_name; (3 rows) DROP RULE hat_upsert ON hats; +-- DO SELECT with a WHERE clause +CREATE RULE hat_confsel AS ON INSERT TO hats + DO INSTEAD + INSERT INTO hat_data VALUES ( + NEW.hat_name, + NEW.hat_color) + ON CONFLICT (hat_name) + DO SELECT FOR UPDATE + WHERE excluded.hat_color <> 'forbidden' AND hat_data.* != excluded.* + RETURNING *; +SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; + definition +-------------------------------------------------------------------------------------- + CREATE RULE hat_confsel AS + + ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + + VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name) DO SELECT FOR UPDATE + + WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*))+ + RETURNING hat_data.hat_name, + + hat_data.hat_color; +(1 row) + +-- fails without RETURNING +INSERT INTO hats VALUES ('h7', 'blue'); +ERROR: ON CONFLICT DO SELECT requires a RETURNING clause +DETAIL: A rule action is INSERT ... ON CONFLICT DO SELECT, which requires a RETURNING clause. +-- works (returns conflicts) +EXPLAIN (costs off) +INSERT INTO hats VALUES ('h7', 'blue') RETURNING *; + QUERY PLAN +------------------------------------------------------------------------------------------------- + Insert on hat_data + Conflict Resolution: SELECT FOR UPDATE + Conflict Arbiter Indexes: hat_data_unique_idx + Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) + -> Result +(5 rows) + +INSERT INTO hats VALUES ('h7', 'blue') RETURNING *; + hat_name | hat_color +------------+------------ + h7 | black +(1 row) + +-- conflicts excluded by WHERE clause +INSERT INTO hats VALUES ('h7', 'forbidden') RETURNING *; + hat_name | hat_color +----------+----------- +(0 rows) + +INSERT INTO hats VALUES ('h7', 'black') RETURNING *; + hat_name | hat_color +----------+----------- +(0 rows) + +DROP RULE hat_confsel ON hats; drop table hats; drop table hat_data; -- test for pg_get_functiondef properly regurgitating SET parameters diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 1acdd12d29e..98dee63b50a 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1670,7 +1670,7 @@ drop table trigger_ddl_table; drop function trigger_ddl_func(); -- -- Verify behavior of before and after triggers with INSERT...ON CONFLICT --- DO UPDATE +-- DO UPDATE and DO SELECT -- create table upsert (key int4 primary key, color text); create function upsert_before_func() @@ -1745,6 +1745,14 @@ insert into upsert values(8, 'yellow') on conflict (key) do update set color = ' WARNING: before insert (new): (8,yellow) WARNING: before insert (new, modified): (9,"yellow trig modified") WARNING: after insert (new): (9,"yellow trig modified") +insert into upsert values(8, 'blue') on conflict (key) do select for update where upsert.color = 'yellow trig modified' returning old.*, new.*, upsert.*; +WARNING: before insert (new): (8,blue) +WARNING: before insert (new, modified): (9,"blue trig modified") + key | color | key | color | key | color +-----+----------------------+-----+----------------------+-----+---------------------- + 9 | yellow trig modified | 9 | yellow trig modified | 9 | yellow trig modified +(1 row) + select * from upsert; key | color -----+----------------------------- diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 03df7e75b7b..9cea538b8e8 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -316,6 +316,21 @@ SELECT * FROM rw_view15; 3 | UNSPECIFIED (6 rows) +INSERT INTO rw_view15 (a) VALUES (3) + ON CONFLICT (a) DO UPDATE SET a = excluded.a WHERE excluded.upper = 'UNSPECIFIED' + RETURNING old, new; + old | new +-----------------+----------------- + (3,UNSPECIFIED) | (3,UNSPECIFIED) +(1 row) + +INSERT INTO rw_view15 (a) VALUES (3) + ON CONFLICT (a) DO SELECT WHERE excluded.upper = 'UNSPECIFIED' RETURNING old, new; + old | new +-----------------+----------------- + (3,UNSPECIFIED) | (3,UNSPECIFIED) +(1 row) + SELECT * FROM rw_view15; a | upper ----+------------- @@ -3646,7 +3661,7 @@ ERROR: new row violates check option for view "wcowrtest_v2" DETAIL: Failing row contains (2, no such row in sometable). drop view wcowrtest_v, wcowrtest_v2; drop table wcowrtest, sometable; --- Check INSERT .. ON CONFLICT DO UPDATE works correctly when the view's +-- Check INSERT .. ON CONFLICT DO SELECT/UPDATE works correctly when the view's -- columns are named and ordered differently than the underlying table's. create table uv_iocu_tab (a text unique, b float); insert into uv_iocu_tab values ('xyxyxy', 0); @@ -3668,6 +3683,13 @@ select * from uv_iocu_tab; xyxyxy | 1 (1 row) +insert into uv_iocu_view (a, b) values ('xyxyxy', 1) + on conflict (a) do select where uv_iocu_view.c = 2 and excluded.c = 2 returning *; + b | c | a | two +---+---+--------+----- + 1 | 2 | xyxyxy | 2.0 +(1 row) + -- OK to access view columns that are not present in underlying base -- relation in the ON CONFLICT portion of the query insert into uv_iocu_view (a, b) values ('xyxyxy', 3) @@ -3731,6 +3753,25 @@ select * from uv_iocu_view; Rejected: (y,1,"(1,y)") | 1 | (1,"Rejected: (y,1,""(1,y)"")") (1 row) +explain (costs off) +insert into uv_iocu_view (aa,bb) values (1,'Rejected: (y,1,"(1,y)")') + on conflict (aa) do select where uv_iocu_view.* = excluded.* returning *; + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------- + Insert on uv_iocu_tab + Conflict Resolution: SELECT + Conflict Arbiter Indexes: uv_iocu_tab_a_key + Conflict Filter: (ROW(uv_iocu_tab.b, uv_iocu_tab.a, (uv_iocu_tab.*)::text) = ROW(excluded.b, excluded.a, (excluded.*)::text)) + -> Result +(5 rows) + +insert into uv_iocu_view (aa,bb) values (1,'Rejected: (y,1,"(1,y)")') + on conflict (aa) do select where uv_iocu_view.* = excluded.* returning *; + bb | aa | cc +-------------------------+----+--------------------------------- + Rejected: (y,1,"(1,y)") | 1 | (1,"Rejected: (y,1,""(1,y)"")") +(1 row) + -- Test omitting a column of the base relation delete from uv_iocu_view; insert into uv_iocu_view (aa,bb) values (1,'x'); @@ -3751,6 +3792,13 @@ select * from uv_iocu_view; Rejected: ("table default",1,"(1,""table default"")") | 1 | (1,"Rejected: (""table default"",1,""(1,""""table default"""")"")") (1 row) +insert into uv_iocu_view (aa) values (1) + on conflict (aa) do select returning *; + bb | aa | cc +-------------------------------------------------------+----+--------------------------------------------------------------------- + Rejected: ("table default",1,"(1,""table default"")") | 1 | (1,"Rejected: (""table default"",1,""(1,""""table default"""")"")") +(1 row) + alter view uv_iocu_view alter column bb set default 'view default'; insert into uv_iocu_view (aa) values (1) on conflict (aa) do update set bb = 'Rejected: '||excluded.*; @@ -3760,6 +3808,13 @@ select * from uv_iocu_view; Rejected: ("view default",1,"(1,""view default"")") | 1 | (1,"Rejected: (""view default"",1,""(1,""""view default"""")"")") (1 row) +insert into uv_iocu_view (aa) values (1) + on conflict (aa) do select returning *; + bb | aa | cc +-----------------------------------------------------+----+------------------------------------------------------------------- + Rejected: ("view default",1,"(1,""view default"")") | 1 | (1,"Rejected: (""view default"",1,""(1,""""view default"""")"")") +(1 row) + -- Should fail to update non-updatable columns insert into uv_iocu_view (aa) values (1) on conflict (aa) do update set cc = 'XXX'; @@ -3767,7 +3822,7 @@ ERROR: cannot insert into column "cc" of view "uv_iocu_view" DETAIL: View columns that are not columns of their base relation are not updatable. drop view uv_iocu_view; drop table uv_iocu_tab; --- ON CONFLICT DO UPDATE permissions checks +-- ON CONFLICT DO SELECT/UPDATE permissions checks create user regress_view_user1; create user regress_view_user2; set session authorization regress_view_user1; @@ -3791,6 +3846,16 @@ insert into rw_view1 values ('zzz',2.0,1) insert into rw_view1 values ('zzz',2.0,1) on conflict (aa) do update set cc = 3.0; -- Not allowed ERROR: permission denied for view rw_view1 +insert into rw_view1 values ('yyy',2.0,1) + on conflict (aa) do select for update returning cc; -- Not allowed +ERROR: permission denied for view rw_view1 +insert into rw_view1 values ('yyy',2.0,1) + on conflict (aa) do select for update returning aa, bb; + aa | bb +----+-------- + 1 | yyyxxx +(1 row) + reset session authorization; select * from base_tbl; a | b | c @@ -3807,9 +3872,19 @@ create view rw_view2 as select b as bb, c as cc, a as aa from base_tbl; insert into rw_view2 (aa,bb) values (1,'xxx') on conflict (aa) do update set bb = excluded.bb; -- Not allowed ERROR: permission denied for table base_tbl +insert into rw_view2 (aa,bb) values (1,'xxx') + on conflict (aa) do select returning 1; -- Not allowed +ERROR: permission denied for table base_tbl create view rw_view3 as select b as bb, a as aa from base_tbl; insert into rw_view3 (aa,bb) values (1,'xxx') on conflict (aa) do update set bb = excluded.bb; -- OK +insert into rw_view3 (aa,bb) values (1,'xxx') + on conflict (aa) do select returning aa, bb; -- OK + aa | bb +----+----- + 1 | xxx +(1 row) + reset session authorization; select * from base_tbl; a | b | c @@ -3822,6 +3897,9 @@ create view rw_view4 as select aa, bb, cc FROM rw_view1; insert into rw_view4 (aa,bb) values (1,'yyy') on conflict (aa) do update set bb = excluded.bb; -- Not allowed ERROR: permission denied for view rw_view1 +insert into rw_view4 (aa,bb) values (1,'yyy') + on conflict (aa) do select returning 1; -- Not allowed +ERROR: permission denied for view rw_view1 create view rw_view5 as select aa, bb FROM rw_view1; insert into rw_view5 (aa,bb) values (1,'yyy') on conflict (aa) do update set bb = excluded.bb; -- OK diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index 1e9989698b6..b7f6efdd814 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -568,6 +568,9 @@ INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') -- fail, because DO UPDATE variant requires unique index INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO UPDATE SET c2 = EXCLUDED.c2; +-- fail, because DO SELECT variant requires unique index +INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') + ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO SELECT RETURNING *; -- succeed because c1 doesn't overlap INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>'); -- succeed because c2 doesn't overlap diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql index 03b1f0e44b0..a5a84d1d4b8 100644 --- a/src/test/regress/sql/insert_conflict.sql +++ b/src/test/regress/sql/insert_conflict.sql @@ -93,6 +93,9 @@ explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on con explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) do update set fruit = excluded.fruit where excluded.fruit != 'Elderberry'; -- Does the same, but JSON format shows "Conflict Arbiter Index" as JSON array: explain (costs off, format json) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *; +-- Should display lock strength, if specified +explain (costs off) insert into insertconflicttest values (1, 'Apple') on conflict (key) do select returning *; +explain (costs off) insert into insertconflicttest values (1, 'Apple') on conflict (key) do select for key share returning *; -- Fails (no unique index inference specification, required for do update variant): insert into insertconflicttest values (1, 'Apple') on conflict do update set fruit = excluded.fruit; @@ -130,6 +133,18 @@ insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (ke -- Check helpful hint when qualifying set column with target table insert into insertconflicttest values (3, 'Kiwi') on conflict (key, fruit) do update set insertconflicttest.fruit = 'Mango'; +-- +-- DO SELECT tests +-- +delete from insertconflicttest where fruit = 'Apple'; +insert into insertconflicttest values (1, 'Apple') on conflict (key) do select; -- fails +insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do select returning old, new, i; +insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select returning old, new, i; +insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Apple' returning *; +insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Orange' returning *; +insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select where excluded.fruit = 'Apple' returning *; +insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select where excluded.fruit = 'Orange' returning *; + drop index key_index; -- @@ -459,6 +474,30 @@ begin transaction isolation level serializable; insert into selfconflict values (6,1), (6,2) on conflict(f1) do update set f2 = 0; commit; +begin transaction isolation level read committed; +insert into selfconflict values (7,1), (7,2) on conflict(f1) do select returning *; +commit; + +begin transaction isolation level repeatable read; +insert into selfconflict values (8,1), (8,2) on conflict(f1) do select returning *; +commit; + +begin transaction isolation level serializable; +insert into selfconflict values (9,1), (9,2) on conflict(f1) do select returning *; +commit; + +begin transaction isolation level read committed; +insert into selfconflict values (10,1), (10,2) on conflict(f1) do select for update returning *; +commit; + +begin transaction isolation level repeatable read; +insert into selfconflict values (11,1), (11,2) on conflict(f1) do select for update returning *; +commit; + +begin transaction isolation level serializable; +insert into selfconflict values (12,1), (12,2) on conflict(f1) do select for update returning *; +commit; + select * from selfconflict; drop table selfconflict; @@ -473,13 +512,17 @@ insert into parted_conflict_test values (1, 'a') on conflict do nothing; -- index on a required, which does exist in parent insert into parted_conflict_test values (1, 'a') on conflict (a) do nothing; insert into parted_conflict_test values (1, 'a') on conflict (a) do update set b = excluded.b; +insert into parted_conflict_test values (1, 'a') on conflict (a) do select returning *; +insert into parted_conflict_test values (1, 'a') on conflict (a) do select for update returning *; -- targeting partition directly will work insert into parted_conflict_test_1 values (1, 'a') on conflict (a) do nothing; insert into parted_conflict_test_1 values (1, 'b') on conflict (a) do update set b = excluded.b; +insert into parted_conflict_test_1 values (1, 'b') on conflict (a) do select returning b; -- index on b required, which doesn't exist in parent -insert into parted_conflict_test values (2, 'b') on conflict (b) do update set a = excluded.a; +insert into parted_conflict_test values (2, 'b') on conflict (b) do update set a = excluded.a; -- fail +insert into parted_conflict_test values (2, 'b') on conflict (b) do select returning b; -- fail -- targeting partition directly will work insert into parted_conflict_test_1 values (2, 'b') on conflict (b) do update set a = excluded.a; @@ -487,13 +530,16 @@ insert into parted_conflict_test_1 values (2, 'b') on conflict (b) do update set -- should see (2, 'b') select * from parted_conflict_test order by a; --- now check that DO UPDATE works correctly for target partition with --- different attribute numbers +-- now check that DO UPDATE and DO SELECT work correctly for target partition +-- with different attribute numbers create table parted_conflict_test_2 (b char, a int unique); alter table parted_conflict_test attach partition parted_conflict_test_2 for values in (3); truncate parted_conflict_test; insert into parted_conflict_test values (3, 'a') on conflict (a) do update set b = excluded.b; insert into parted_conflict_test values (3, 'b') on conflict (a) do update set b = excluded.b; +insert into parted_conflict_test values (3, 'a') on conflict (a) do select returning b; +insert into parted_conflict_test values (3, 'a') on conflict (a) do select where excluded.b = 'a' returning parted_conflict_test; +insert into parted_conflict_test values (3, 'a') on conflict (a) do select where parted_conflict_test.b = 'b' returning b; -- should see (3, 'b') select * from parted_conflict_test order by a; @@ -504,6 +550,7 @@ create table parted_conflict_test_3 partition of parted_conflict_test for values truncate parted_conflict_test; insert into parted_conflict_test (a, b) values (4, 'a') on conflict (a) do update set b = excluded.b; insert into parted_conflict_test (a, b) values (4, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a'; +insert into parted_conflict_test (a, b) values (4, 'b') on conflict (a) do select returning b; -- should see (4, 'b') select * from parted_conflict_test order by a; @@ -514,6 +561,7 @@ create table parted_conflict_test_4_1 partition of parted_conflict_test_4 for va truncate parted_conflict_test; insert into parted_conflict_test (a, b) values (5, 'a') on conflict (a) do update set b = excluded.b; insert into parted_conflict_test (a, b) values (5, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a'; +insert into parted_conflict_test (a, b) values (5, 'b') on conflict (a) do select where parted_conflict_test.b = 'a' returning b; -- should see (5, 'b') select * from parted_conflict_test order by a; @@ -526,6 +574,26 @@ insert into parted_conflict_test (a, b) values (1, 'b'), (2, 'c'), (4, 'b') on c -- should see (1, 'b'), (2, 'a'), (4, 'b') select * from parted_conflict_test order by a; +-- test DO SELECT with multiple rows hitting different partitions +truncate parted_conflict_test; +insert into parted_conflict_test (a, b) values (1, 'a'), (2, 'b'), (4, 'c'); +insert into parted_conflict_test (a, b) values (1, 'x'), (2, 'y'), (4, 'z') + on conflict (a) do select returning *, tableoid::regclass; + +-- should see original values (1, 'a'), (2, 'b'), (4, 'c') +select * from parted_conflict_test order by a; + +-- test DO SELECT with WHERE filtering across partitions +insert into parted_conflict_test (a, b) values (1, 'n') on conflict (a) do select where parted_conflict_test.b = 'a' returning *; +insert into parted_conflict_test (a, b) values (2, 'n') on conflict (a) do select where parted_conflict_test.b = 'x' returning *; + +-- test DO SELECT with EXCLUDED in WHERE across partitions with different layouts +insert into parted_conflict_test (a, b) values (3, 't') on conflict (a) do select where excluded.b = 't' returning *; + +-- test DO SELECT FOR UPDATE across different partition layouts +insert into parted_conflict_test (a, b) values (1, 'l') on conflict (a) do select for update returning *; +insert into parted_conflict_test (a, b) values (3, 'l') on conflict (a) do select for update returning *; + drop table parted_conflict_test; -- test behavior of inserting a conflicting tuple into an intermediate diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index 96eff1104d2..66e06d91a41 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -565,6 +565,24 @@ INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLU INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.three; INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set one = 8; -- fails (due to UPDATE) INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10; -- fails (due to INSERT) +-- Check that column level privileges are enforced for ON CONFLICT ... WHERE +-- Ok. we may select one +INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT WHERE atest5.one = 1 RETURNING atest5.two; +-- Error. No select rights on three +INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT WHERE atest5.three = 1 RETURNING atest5.two; + +-- Check that ON CONFLICT ... SELECT FOR UPDATE/SHARE requires an updatable column +SET SESSION AUTHORIZATION regress_priv_user1; +REVOKE UPDATE (three) ON atest5 FROM regress_priv_user4; +SET SESSION AUTHORIZATION regress_priv_user4; + +INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT FOR UPDATE RETURNING atest5.two; -- fails + +SET SESSION AUTHORIZATION regress_priv_user1; +GRANT UPDATE (three) ON atest5 TO regress_priv_user4; +SET SESSION AUTHORIZATION regress_priv_user4; + +INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT FOR UPDATE RETURNING atest5.two; -- ok -- Check that the columns in the inference require select privileges INSERT INTO atest5(four) VALUES (4); -- fail diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index fb6502d497f..6b3566271df 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -142,6 +142,21 @@ INSERT INTO rls_test_tgt VALUES (3, 'tgt a') ON CONFLICT (a) DO UPDATE SET b = ' INSERT INTO rls_test_tgt VALUES (3, 'tgt c') ON CONFLICT (a) DO UPDATE SET b = 'tgt d' RETURNING *; ROLLBACK; +-- INSERT ... ON CONFLICT DO SELECT should apply INSERT CHECK and SELECT USING +-- policy clauses to values proposed for insert. In the event of a conflict it +-- should also apply SELECT USING policy clauses to the existing values. +BEGIN; +INSERT INTO rls_test_tgt VALUES (4, 'tgt a') ON CONFLICT (a) DO SELECT RETURNING *; +INSERT INTO rls_test_tgt VALUES (4, 'tgt b') ON CONFLICT (a) DO SELECT RETURNING *; +ROLLBACK; + +-- INSERT ... ON CONFLICT DO SELECT FOR UPDATE should also apply UPDATE USING +-- policy clauses to the existing values, in the event of a conflict. +BEGIN; +INSERT INTO rls_test_tgt VALUES (5, 'tgt a') ON CONFLICT (a) DO SELECT FOR UPDATE RETURNING *; +INSERT INTO rls_test_tgt VALUES (5, 'tgt b') ON CONFLICT (a) DO SELECT FOR UPDATE RETURNING *; +ROLLBACK; + -- MERGE should always apply SELECT USING policy clauses to both source and -- target rows MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a @@ -953,11 +968,51 @@ INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel') INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel') ON CONFLICT (did) DO UPDATE SET dauthor = 'regress_rls_carol'; +-- +-- INSERT ... ON CONFLICT DO SELECT and Row-level security +-- +SET SESSION AUTHORIZATION regress_rls_alice; +DROP POLICY p3_with_all ON document; + +CREATE POLICY p1_select_novels ON document FOR SELECT + USING (cid = (SELECT cid from category WHERE cname = 'novel')); +CREATE POLICY p2_insert_own ON document FOR INSERT + WITH CHECK (dauthor = current_user); +CREATE POLICY p3_update_novels ON document FOR UPDATE + USING (cid = (SELECT cid from category WHERE cname = 'novel') AND dlevel = 1) + WITH CHECK (dauthor = current_user); + +SET SESSION AUTHORIZATION regress_rls_bob; + +-- DO SELECT requires SELECT rights, should succeed for novel +INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel') + ON CONFLICT (did) DO SELECT RETURNING did, dauthor, dtitle; + +-- DO SELECT requires SELECT rights, should fail for non-novel +INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'science fiction'), 1, 'regress_rls_bob', 'another sci-fi') + ON CONFLICT (did) DO SELECT RETURNING did, dauthor, dtitle; + +-- DO SELECT with WHERE and EXCLUDED reference +INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel') + ON CONFLICT (did) DO SELECT WHERE excluded.dlevel = 1 RETURNING did, dauthor, dtitle; + +-- DO SELECT FOR UPDATE requires both SELECT and UPDATE rights, should succeed for novel and dlevel = 1 +INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel') + ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle; + +-- should fail UPDATE USING policy for novel with dlevel = 2 +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel') + ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle; + +SET SESSION AUTHORIZATION regress_rls_alice; +DROP POLICY p1_select_novels ON document; +DROP POLICY p2_insert_own ON document; +DROP POLICY p3_update_novels ON document; + -- -- MERGE -- RESET SESSION AUTHORIZATION; -DROP POLICY p3_with_all ON document; ALTER TABLE document ADD COLUMN dnotes text DEFAULT ''; -- all documents are readable diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 3f240bec7b0..40f5c16e540 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -1205,6 +1205,32 @@ SELECT * FROM hat_data WHERE hat_name IN ('h8', 'h9', 'h7') ORDER BY hat_name; DROP RULE hat_upsert ON hats; +-- DO SELECT with a WHERE clause +CREATE RULE hat_confsel AS ON INSERT TO hats + DO INSTEAD + INSERT INTO hat_data VALUES ( + NEW.hat_name, + NEW.hat_color) + ON CONFLICT (hat_name) + DO SELECT FOR UPDATE + WHERE excluded.hat_color <> 'forbidden' AND hat_data.* != excluded.* + RETURNING *; +SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; + +-- fails without RETURNING +INSERT INTO hats VALUES ('h7', 'blue'); + +-- works (returns conflicts) +EXPLAIN (costs off) +INSERT INTO hats VALUES ('h7', 'blue') RETURNING *; +INSERT INTO hats VALUES ('h7', 'blue') RETURNING *; + +-- conflicts excluded by WHERE clause +INSERT INTO hats VALUES ('h7', 'forbidden') RETURNING *; +INSERT INTO hats VALUES ('h7', 'black') RETURNING *; + +DROP RULE hat_confsel ON hats; + drop table hats; drop table hat_data; diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index cc878455ace..ea39817ee3d 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1148,7 +1148,7 @@ drop function trigger_ddl_func(); -- -- Verify behavior of before and after triggers with INSERT...ON CONFLICT --- DO UPDATE +-- DO UPDATE and DO SELECT -- create table upsert (key int4 primary key, color text); @@ -1197,6 +1197,7 @@ insert into upsert values(5, 'purple') on conflict (key) do update set color = ' insert into upsert values(6, 'white') on conflict (key) do update set color = 'updated ' || upsert.color; insert into upsert values(7, 'pink') on conflict (key) do update set color = 'updated ' || upsert.color; insert into upsert values(8, 'yellow') on conflict (key) do update set color = 'updated ' || upsert.color; +insert into upsert values(8, 'blue') on conflict (key) do select for update where upsert.color = 'yellow trig modified' returning old.*, new.*, upsert.*; select * from upsert; diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index c071fffc116..1635adde2d4 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -106,6 +106,12 @@ INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set a = excluded. SELECT * FROM rw_view15; INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set upper = 'blarg'; -- fails SELECT * FROM rw_view15; +INSERT INTO rw_view15 (a) VALUES (3) + ON CONFLICT (a) DO UPDATE SET a = excluded.a WHERE excluded.upper = 'UNSPECIFIED' + RETURNING old, new; +INSERT INTO rw_view15 (a) VALUES (3) + ON CONFLICT (a) DO SELECT WHERE excluded.upper = 'UNSPECIFIED' RETURNING old, new; + SELECT * FROM rw_view15; ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET'; INSERT INTO rw_view15 (a) VALUES (4); -- should fail @@ -1850,7 +1856,7 @@ insert into wcowrtest_v2 values (2, 'no such row in sometable'); drop view wcowrtest_v, wcowrtest_v2; drop table wcowrtest, sometable; --- Check INSERT .. ON CONFLICT DO UPDATE works correctly when the view's +-- Check INSERT .. ON CONFLICT DO SELECT/UPDATE works correctly when the view's -- columns are named and ordered differently than the underlying table's. create table uv_iocu_tab (a text unique, b float); insert into uv_iocu_tab values ('xyxyxy', 0); @@ -1863,6 +1869,8 @@ select * from uv_iocu_tab; insert into uv_iocu_view (a, b) values ('xyxyxy', 1) on conflict (a) do update set b = excluded.b; select * from uv_iocu_tab; +insert into uv_iocu_view (a, b) values ('xyxyxy', 1) + on conflict (a) do select where uv_iocu_view.c = 2 and excluded.c = 2 returning *; -- OK to access view columns that are not present in underlying base -- relation in the ON CONFLICT portion of the query @@ -1899,6 +1907,11 @@ insert into uv_iocu_view (aa,bb) values (1,'y') and excluded.bb != '' and excluded.cc is not null; select * from uv_iocu_view; +explain (costs off) +insert into uv_iocu_view (aa,bb) values (1,'Rejected: (y,1,"(1,y)")') + on conflict (aa) do select where uv_iocu_view.* = excluded.* returning *; +insert into uv_iocu_view (aa,bb) values (1,'Rejected: (y,1,"(1,y)")') + on conflict (aa) do select where uv_iocu_view.* = excluded.* returning *; -- Test omitting a column of the base relation delete from uv_iocu_view; @@ -1911,11 +1924,15 @@ alter table uv_iocu_tab alter column b set default 'table default'; insert into uv_iocu_view (aa) values (1) on conflict (aa) do update set bb = 'Rejected: '||excluded.*; select * from uv_iocu_view; +insert into uv_iocu_view (aa) values (1) + on conflict (aa) do select returning *; alter view uv_iocu_view alter column bb set default 'view default'; insert into uv_iocu_view (aa) values (1) on conflict (aa) do update set bb = 'Rejected: '||excluded.*; select * from uv_iocu_view; +insert into uv_iocu_view (aa) values (1) + on conflict (aa) do select returning *; -- Should fail to update non-updatable columns insert into uv_iocu_view (aa) values (1) @@ -1924,7 +1941,7 @@ insert into uv_iocu_view (aa) values (1) drop view uv_iocu_view; drop table uv_iocu_tab; --- ON CONFLICT DO UPDATE permissions checks +-- ON CONFLICT DO SELECT/UPDATE permissions checks create user regress_view_user1; create user regress_view_user2; @@ -1948,6 +1965,10 @@ insert into rw_view1 values ('zzz',2.0,1) on conflict (aa) do update set bb = rw_view1.bb||'xxx'; -- OK insert into rw_view1 values ('zzz',2.0,1) on conflict (aa) do update set cc = 3.0; -- Not allowed +insert into rw_view1 values ('yyy',2.0,1) + on conflict (aa) do select for update returning cc; -- Not allowed +insert into rw_view1 values ('yyy',2.0,1) + on conflict (aa) do select for update returning aa, bb; reset session authorization; select * from base_tbl; @@ -1960,9 +1981,13 @@ set session authorization regress_view_user2; create view rw_view2 as select b as bb, c as cc, a as aa from base_tbl; insert into rw_view2 (aa,bb) values (1,'xxx') on conflict (aa) do update set bb = excluded.bb; -- Not allowed +insert into rw_view2 (aa,bb) values (1,'xxx') + on conflict (aa) do select returning 1; -- Not allowed create view rw_view3 as select b as bb, a as aa from base_tbl; insert into rw_view3 (aa,bb) values (1,'xxx') on conflict (aa) do update set bb = excluded.bb; -- OK +insert into rw_view3 (aa,bb) values (1,'xxx') + on conflict (aa) do select returning aa, bb; -- OK reset session authorization; select * from base_tbl; @@ -1970,6 +1995,8 @@ set session authorization regress_view_user2; create view rw_view4 as select aa, bb, cc FROM rw_view1; insert into rw_view4 (aa,bb) values (1,'yyy') on conflict (aa) do update set bb = excluded.bb; -- Not allowed +insert into rw_view4 (aa,bb) values (1,'yyy') + on conflict (aa) do select returning 1; -- Not allowed create view rw_view5 as select aa, bb FROM rw_view1; insert into rw_view5 (aa,bb) values (1,'yyy') on conflict (aa) do update set bb = excluded.bb; -- OK diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 39c76691c86..6e2d876a40f 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1840,9 +1840,9 @@ OldToNewMappingData OnCommitAction OnCommitItem OnConflictAction +OnConflictActionState OnConflictClause OnConflictExpr -OnConflictSetState OpClassCacheEnt OpExpr OpFamilyMember -- 2.47.3