pgsql: MERGE SQL Command following SQL:2016 - Mailing list pgsql-committers
| From | Simon Riggs |
|---|---|
| Subject | pgsql: MERGE SQL Command following SQL:2016 |
| Date | |
| Msg-id | E1f3HKv-0008F2-0b@gemulon.postgresql.org Whole thread Raw |
| List | pgsql-committers |
MERGE SQL Command following SQL:2016 MERGE performs actions that modify rows in the target table using a source table or query. MERGE provides a single SQL statement that can conditionally INSERT/UPDATE/DELETE rows a task that would other require multiple PL statements. e.g. MERGE INTO target AS t USING source AS s ON t.tid = s.sid WHEN MATCHED AND t.balance > s.delta THEN UPDATE SET balance = t.balance - s.delta WHEN MATCHED THEN DELETE WHEN NOT MATCHED AND s.delta > 0 THEN INSERT VALUES (s.sid, s.delta) WHEN NOT MATCHED THEN DO NOTHING; MERGE works with regular and partitioned tables, including column and row security enforcement, as well as support for row, statement and transition triggers. MERGE is optimized for OLTP and is parameterizable, though also useful for large scale ETL/ELT. MERGE is not intended to be used in preference to existing single SQL commands for INSERT, UPDATE or DELETE since there is some overhead. MERGE can be used statically from PL/pgSQL. MERGE does not yet support inheritance, write rules, RETURNING clauses, updatable views or foreign tables. MERGE follows SQL Standard per the most recent SQL:2016. Includes full tests and documentation, including full isolation tests to demonstrate the concurrent behavior. This version written from scratch in 2017 by Simon Riggs, using docs and tests originally written in 2009. Later work from Pavan Deolasee has been both complex and deep, leaving the lead author credit now in his hands. Extensive discussion of concurrency from Peter Geoghegan, with thanks for the time and effort contributed. Various issues reported via sqlsmith by Andreas Seltenreich Authors: Pavan Deolasee, Simon Riggs Reviewer: Peter Geoghegan, Amit Langote, Tomas Vondra, Simon Riggs Discussion: https://postgr.es/m/CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com https://postgr.es/m/CAH2-WzkJdBuxj9PO=2QaO9-3h3xGbQPZ34kJH=HukRekwM-GZg@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/d204ef63776b8a00ca220adec23979091564e465 Modified Files -------------- contrib/test_decoding/expected/ddl.out | 46 ++++ contrib/test_decoding/sql/ddl.sql | 16 ++ doc/src/sgml/libpq.sgml | 8 +- doc/src/sgml/mvcc.sgml | 28 ++- doc/src/sgml/plpgsql.sgml | 3 +- doc/src/sgml/ref/allfiles.sgml | 1 + doc/src/sgml/ref/create_policy.sgml | 7 + doc/src/sgml/ref/insert.sgml | 11 +- doc/src/sgml/reference.sgml | 1 + doc/src/sgml/trigger.sgml | 20 ++ src/backend/access/heap/heapam.c | 28 ++- src/backend/catalog/sql_features.txt | 6 +- src/backend/commands/explain.c | 33 +++ src/backend/commands/prepare.c | 1 + src/backend/commands/trigger.c | 156 +++++++++--- src/backend/executor/Makefile | 2 +- src/backend/executor/README | 10 + src/backend/executor/execMain.c | 17 ++ src/backend/executor/execPartition.c | 116 +++++++++ src/backend/executor/execReplication.c | 4 +- src/backend/executor/nodeModifyTable.c | 384 ++++++++++++++++++++++++++---- src/backend/executor/spi.c | 3 + src/backend/nodes/copyfuncs.c | 40 ++++ src/backend/nodes/equalfuncs.c | 32 +++ src/backend/nodes/nodeFuncs.c | 58 ++++- src/backend/nodes/outfuncs.c | 25 ++ src/backend/nodes/readfuncs.c | 26 ++ src/backend/optimizer/plan/createplan.c | 22 +- src/backend/optimizer/plan/planner.c | 29 ++- src/backend/optimizer/plan/setrefs.c | 54 +++++ src/backend/optimizer/prep/preptlist.c | 41 ++++ src/backend/optimizer/util/pathnode.c | 11 +- src/backend/optimizer/util/plancat.c | 4 + src/backend/parser/Makefile | 2 +- src/backend/parser/analyze.c | 18 +- src/backend/parser/gram.y | 158 +++++++++++- src/backend/parser/parse_agg.c | 10 + src/backend/parser/parse_clause.c | 45 +++- src/backend/parser/parse_collate.c | 1 + src/backend/parser/parse_expr.c | 3 + src/backend/parser/parse_func.c | 3 + src/backend/parser/parse_relation.c | 10 + src/backend/rewrite/rewriteHandler.c | 117 ++++++++- src/backend/rewrite/rowsecurity.c | 97 ++++++++ src/backend/tcop/pquery.c | 5 + src/backend/tcop/utility.c | 16 ++ src/include/access/heapam.h | 13 +- src/include/commands/trigger.h | 6 +- src/include/executor/execPartition.h | 1 + src/include/executor/instrument.h | 7 +- src/include/executor/nodeModifyTable.h | 21 ++ src/include/executor/spi.h | 1 + src/include/nodes/execnodes.h | 65 ++++- src/include/nodes/nodes.h | 6 +- src/include/nodes/parsenodes.h | 37 ++- src/include/nodes/plannodes.h | 8 +- src/include/nodes/relation.h | 7 +- src/include/optimizer/pathnode.h | 7 +- src/include/parser/analyze.h | 5 + src/include/parser/kwlist.h | 2 + src/include/parser/parse_clause.h | 5 +- src/include/parser/parse_node.h | 5 +- src/include/rewrite/rewriteHandler.h | 1 + src/interfaces/libpq/fe-exec.c | 9 +- src/pl/plpgsql/src/pl_exec.c | 5 +- src/pl/plpgsql/src/pl_gram.y | 8 + src/pl/plpgsql/src/pl_scanner.c | 1 + src/pl/plpgsql/src/plpgsql.h | 4 +- src/test/isolation/isolation_schedule | 4 + src/test/regress/expected/identity.out | 55 +++++ src/test/regress/expected/privileges.out | 98 ++++++++ src/test/regress/expected/rowsecurity.out | 182 ++++++++++++++ src/test/regress/expected/rules.out | 31 +++ src/test/regress/expected/triggers.out | 48 ++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/identity.sql | 45 ++++ src/test/regress/sql/privileges.sql | 108 +++++++++ src/test/regress/sql/rowsecurity.sql | 156 ++++++++++++ src/test/regress/sql/rules.sql | 33 +++ src/test/regress/sql/triggers.sql | 47 ++++ src/tools/pgindent/typedefs.list | 3 + 82 files changed, 2600 insertions(+), 165 deletions(-)
pgsql-committers by date: