On 8 May 2015 at 04:43, Andres Freund <and...@anarazel.de> wrote: > Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE. > > The newly added ON CONFLICT clause allows to specify an alternative to > raising a unique or exclusion constraint violation error when inserting. > ON CONFLICT refers to constraints that can either be specified using a > inference clause (by specifying the columns of a unique constraint) or > by naming a unique or exclusion constraint. DO NOTHING avoids the > constraint violation, without touching the pre-existing row. DO UPDATE > SET ... [WHERE ...] updates the pre-existing tuple, and has access to > both the tuple proposed for insertion and the existing tuple; the > optional WHERE clause can be used to prevent an update from being > executed. The UPDATE SET and WHERE clauses have access to the tuple > proposed for insertion using the "magic" EXCLUDED alias, and to the > pre-existing tuple using the table name or its alias. > > This feature is often referred to as upsert. > > This is implemented using a new infrastructure called "speculative > insertion". It is an optimistic variant of regular insertion that first > does a pre-check for existing tuples and then attempts an insert. If a > violating tuple was inserted concurrently, the speculatively inserted > tuple is deleted and a new attempt is made. If the pre-check finds a > matching tuple the alternative DO NOTHING or DO UPDATE action is taken. > If the insertion succeeds without detecting a conflict, the tuple is > deemed inserted. > > To handle the possible ambiguity between the excluded alias and a table > named excluded, and for convenience with long relation names, INSERT > INTO now can alias its target table. > > Bumps catversion as stored rules change. > > Author: Peter Geoghegan, with significant contributions from Heikki > Linnakangas and Andres Freund. Testing infrastructure by Jeff Janes. > Reviewed-By: Heikki Linnakangas, Andres Freund, Robert Haas, Simon Riggs, > Dean Rasheed, Stephen Frost and many others. > > Branch > ------ > master > > Details > ------- > http://git.postgresql.org/pg/commitdiff/168d5805e4c08bed7b95d351bf097cff7c07dd65 > > Modified Files > -------------- > contrib/pg_stat_statements/pg_stat_statements.c | 25 + > contrib/postgres_fdw/deparse.c | 7 +- > contrib/postgres_fdw/expected/postgres_fdw.out | 5 + > contrib/postgres_fdw/postgres_fdw.c | 15 +- > contrib/postgres_fdw/postgres_fdw.h | 2 +- > contrib/postgres_fdw/sql/postgres_fdw.sql | 3 + > contrib/test_decoding/expected/ddl.out | 34 ++ > contrib/test_decoding/expected/toast.out | 9 +- > contrib/test_decoding/sql/ddl.sql | 22 + > contrib/test_decoding/sql/toast.sql | 5 + > doc/src/sgml/fdwhandler.sgml | 7 + > doc/src/sgml/keywords.sgml | 7 + > doc/src/sgml/mvcc.sgml | 23 +- > doc/src/sgml/plpgsql.sgml | 14 +- > doc/src/sgml/postgres-fdw.sgml | 8 + > doc/src/sgml/protocol.sgml | 13 +- > doc/src/sgml/ref/create_policy.sgml | 63 ++- > doc/src/sgml/ref/create_rule.sgml | 6 +- > doc/src/sgml/ref/create_table.sgml | 4 +- > doc/src/sgml/ref/create_trigger.sgml | 5 +- > doc/src/sgml/ref/create_view.sgml | 9 +- > doc/src/sgml/ref/insert.sgml | 403 ++++++++++++++++- > doc/src/sgml/trigger.sgml | 48 +- > src/backend/access/heap/heapam.c | 377 ++++++++++++++-- > src/backend/access/heap/hio.c | 27 +- > src/backend/access/heap/tuptoaster.c | 8 + > src/backend/access/nbtree/nbtinsert.c | 28 +- > src/backend/access/rmgrdesc/heapdesc.c | 9 + > src/backend/catalog/index.c | 53 ++- > src/backend/catalog/indexing.c | 2 +- > src/backend/catalog/sql_features.txt | 2 +- > src/backend/commands/constraint.c | 2 +- > src/backend/commands/copy.c | 7 +- > src/backend/commands/explain.c | 70 ++- > src/backend/commands/trigger.c | 19 +- > src/backend/executor/execIndexing.c | 417 ++++++++++++++--- > src/backend/executor/execMain.c | 53 ++- > src/backend/executor/nodeLockRows.c | 12 +- > src/backend/executor/nodeModifyTable.c | 459 ++++++++++++++++++- > src/backend/nodes/copyfuncs.c | 84 ++++ > src/backend/nodes/equalfuncs.c | 62 +++ > src/backend/nodes/nodeFuncs.c | 87 ++++ > src/backend/nodes/outfuncs.c | 41 +- > src/backend/nodes/readfuncs.c | 40 ++ > src/backend/optimizer/plan/createplan.c | 26 +- > src/backend/optimizer/plan/planner.c | 27 ++ > src/backend/optimizer/plan/setrefs.c | 52 ++- > src/backend/optimizer/plan/subselect.c | 4 + > src/backend/optimizer/prep/prepjointree.c | 6 + > src/backend/optimizer/prep/preptlist.c | 13 + > src/backend/optimizer/util/plancat.c | 352 +++++++++++++++ > src/backend/parser/analyze.c | 149 +++++- > src/backend/parser/gram.y | 121 ++++- > src/backend/parser/parse_clause.c | 203 +++++++++ > src/backend/parser/parse_collate.c | 2 + > src/backend/parser/parse_target.c | 11 +- > src/backend/replication/logical/decode.c | 66 ++- > src/backend/replication/logical/reorderbuffer.c | 159 +++++-- > src/backend/rewrite/rewriteHandler.c | 87 +++- > src/backend/rewrite/rowsecurity.c | 82 +++- > src/backend/storage/lmgr/lmgr.c | 91 ++++ > src/backend/tcop/pquery.c | 17 +- > src/backend/utils/adt/lockfuncs.c | 1 + > src/backend/utils/adt/ruleutils.c | 108 +++-- > src/backend/utils/time/tqual.c | 29 +- > src/bin/psql/common.c | 5 +- > src/include/access/heapam.h | 3 + > src/include/access/heapam_xlog.h | 54 ++- > src/include/access/hio.h | 2 +- > src/include/access/htup_details.h | 36 +- > src/include/catalog/catversion.h | 2 +- > src/include/catalog/index.h | 2 + > src/include/executor/executor.h | 13 +- > src/include/nodes/execnodes.h | 15 + > src/include/nodes/nodes.h | 17 + > src/include/nodes/parsenodes.h | 45 +- > src/include/nodes/plannodes.h | 8 + > src/include/nodes/primnodes.h | 42 ++ > src/include/optimizer/plancat.h | 2 + > src/include/optimizer/planmain.h | 2 +- > src/include/optimizer/prep.h | 3 + > src/include/parser/kwlist.h | 1 + > src/include/parser/parse_clause.h | 4 + > src/include/replication/reorderbuffer.h | 9 +- > src/include/rewrite/rowsecurity.h | 3 +- > src/include/storage/lmgr.h | 5 + > src/include/storage/lock.h | 10 + > src/include/utils/snapshot.h | 22 +- > .../expected/insert-conflict-do-nothing.out | 23 + > .../expected/insert-conflict-do-update-2.out | 23 + > .../expected/insert-conflict-do-update-3.out | 26 ++ > .../expected/insert-conflict-do-update.out | 23 + > src/test/isolation/isolation_schedule | 4 + > .../specs/insert-conflict-do-nothing.spec | 41 ++ > .../specs/insert-conflict-do-update-2.spec | 41 ++ > .../specs/insert-conflict-do-update-3.spec | 69 +++ > .../isolation/specs/insert-conflict-do-update.spec | 40 ++ > src/test/regress/expected/errors.out | 4 +- > src/test/regress/expected/insert_conflict.out | 476 ++++++++++++++++++++ > src/test/regress/expected/privileges.out | 29 +- > src/test/regress/expected/returning.out | 24 + > src/test/regress/expected/rowsecurity.out | 132 ++++++ > src/test/regress/expected/rules.out | 90 ++++ > src/test/regress/expected/subselect.out | 22 + > src/test/regress/expected/triggers.out | 102 ++++- > src/test/regress/expected/updatable_views.out | 61 +++ > src/test/regress/expected/update.out | 34 ++ > src/test/regress/expected/with.out | 82 ++++ > src/test/regress/input/constraints.source | 12 + > src/test/regress/output/constraints.source | 24 +- > src/test/regress/parallel_schedule | 1 + > src/test/regress/serial_schedule | 1 + > src/test/regress/sql/insert_conflict.sql | 284 ++++++++++++ > src/test/regress/sql/privileges.sql | 19 +- > src/test/regress/sql/returning.sql | 6 + > src/test/regress/sql/rowsecurity.sql | 112 +++++ > src/test/regress/sql/rules.sql | 59 +++ > src/test/regress/sql/subselect.sql | 14 + > src/test/regress/sql/triggers.sql | 69 ++- > src/test/regress/sql/updatable_views.sql | 9 + > src/test/regress/sql/update.sql | 21 + > src/test/regress/sql/with.sql | 57 +++ > 122 files changed, 6106 insertions(+), 435 deletions(-)
I haven't had time for a proper read of this patch, but I did immediately notice this: HINT: For example, ON CONFLICT ON CONFLICT (<column>). This should perhaps either be: HINT: For example, ON CONFLICT (<column>). or HINT: For example, ON CONFLICT ON CONSTRAINT <constraint_name>. But at the moment it seems to be neither. -- Thom -- Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-committers