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 <[email protected]> Author: Marko Tiikkaja <[email protected]> Author: Viktor Holmberg <[email protected]> Reviewed-by: Joel Jacobson <[email protected]> Reviewed-by: Kirill Reshke <[email protected]> Reviewed-by: Dean Rasheed <[email protected]> Reviewed-by: Jian He <[email protected]> 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/[email protected] Discussion: https://postgr.es/m/CAL9smLCdV-v3KgOJX3mU19FYK82N7yzqJj2HAwWX70E=p98...@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/88327092ff06c48676d2a603420089bf493770f3 Modified Files -------------- 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 + .../isolation/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(-)
