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

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