]> granicus.if.org Git - postgresql/commit
MERGE SQL Command following SQL:2016
authorSimon Riggs <simon@2ndQuadrant.com>
Tue, 3 Apr 2018 08:28:16 +0000 (09:28 +0100)
committerSimon Riggs <simon@2ndQuadrant.com>
Tue, 3 Apr 2018 08:28:16 +0000 (09:28 +0100)
commitd204ef63776b8a00ca220adec23979091564e465
tree5fa3d403db1d0377d85d20b9afb949c58855a37f
parentaa5877bb26347c58a34aee4e460eb1e1123bb096
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
82 files changed:
contrib/test_decoding/expected/ddl.out
contrib/test_decoding/sql/ddl.sql
doc/src/sgml/libpq.sgml
doc/src/sgml/mvcc.sgml
doc/src/sgml/plpgsql.sgml
doc/src/sgml/ref/allfiles.sgml
doc/src/sgml/ref/create_policy.sgml
doc/src/sgml/ref/insert.sgml
doc/src/sgml/reference.sgml
doc/src/sgml/trigger.sgml
src/backend/access/heap/heapam.c
src/backend/catalog/sql_features.txt
src/backend/commands/explain.c
src/backend/commands/prepare.c
src/backend/commands/trigger.c
src/backend/executor/Makefile
src/backend/executor/README
src/backend/executor/execMain.c
src/backend/executor/execPartition.c
src/backend/executor/execReplication.c
src/backend/executor/nodeModifyTable.c
src/backend/executor/spi.c
src/backend/nodes/copyfuncs.c
src/backend/nodes/equalfuncs.c
src/backend/nodes/nodeFuncs.c
src/backend/nodes/outfuncs.c
src/backend/nodes/readfuncs.c
src/backend/optimizer/plan/createplan.c
src/backend/optimizer/plan/planner.c
src/backend/optimizer/plan/setrefs.c
src/backend/optimizer/prep/preptlist.c
src/backend/optimizer/util/pathnode.c
src/backend/optimizer/util/plancat.c
src/backend/parser/Makefile
src/backend/parser/analyze.c
src/backend/parser/gram.y
src/backend/parser/parse_agg.c
src/backend/parser/parse_clause.c
src/backend/parser/parse_collate.c
src/backend/parser/parse_expr.c
src/backend/parser/parse_func.c
src/backend/parser/parse_relation.c
src/backend/rewrite/rewriteHandler.c
src/backend/rewrite/rowsecurity.c
src/backend/tcop/pquery.c
src/backend/tcop/utility.c
src/include/access/heapam.h
src/include/commands/trigger.h
src/include/executor/execPartition.h
src/include/executor/instrument.h
src/include/executor/nodeModifyTable.h
src/include/executor/spi.h
src/include/nodes/execnodes.h
src/include/nodes/nodes.h
src/include/nodes/parsenodes.h
src/include/nodes/plannodes.h
src/include/nodes/relation.h
src/include/optimizer/pathnode.h
src/include/parser/analyze.h
src/include/parser/kwlist.h
src/include/parser/parse_clause.h
src/include/parser/parse_node.h
src/include/rewrite/rewriteHandler.h
src/interfaces/libpq/fe-exec.c
src/pl/plpgsql/src/pl_exec.c
src/pl/plpgsql/src/pl_gram.y
src/pl/plpgsql/src/pl_scanner.c
src/pl/plpgsql/src/plpgsql.h
src/test/isolation/isolation_schedule
src/test/regress/expected/identity.out
src/test/regress/expected/privileges.out
src/test/regress/expected/rowsecurity.out
src/test/regress/expected/rules.out
src/test/regress/expected/triggers.out
src/test/regress/parallel_schedule
src/test/regress/serial_schedule
src/test/regress/sql/identity.sql
src/test/regress/sql/privileges.sql
src/test/regress/sql/rowsecurity.sql
src/test/regress/sql/rules.sql
src/test/regress/sql/triggers.sql
src/tools/pgindent/typedefs.list