Re: pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.

On 8 May 2015 at 04:43, Andres Freund <andres(at)anarazel(dot)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