Обсуждение: pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
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(-)
On Fri, May 8, 2015 at 12:43 PM, Andres Freund <andres@anarazel.de> wrote: > Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE. magpie is complaining: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=magpie&dt=2015-05-08%2003%3A45%3A15 And I can reproduce the failure as well on my OSX laptop at least. -- Michael
On May 7, 2015 9:07:55 PM PDT, Michael Paquier <michael.paquier@gmail.com> wrote: >On Fri, May 8, 2015 at 12:43 PM, Andres Freund <andres@anarazel.de> >wrote: >> Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE. > >magpie is complaining: >http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=magpie&dt=2015-05-08%2003%3A45%3A15 >And I can reproduce the failure as well on my OSX laptop at least. Pushed something that hopefully fixes that already. Works for you? Andres --- Please excuse brevity and formatting - I am writing this on my mobile phone.
On Fri, May 8, 2015 at 1:09 PM, Andres Freund <andres@anarazel.de> wrote: > On May 7, 2015 9:07:55 PM PDT, Michael Paquier <michael.paquier@gmail.com> wrote: >>On Fri, May 8, 2015 at 12:43 PM, Andres Freund <andres@anarazel.de> >>wrote: >>> Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE. >> >>magpie is complaining: >>http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=magpie&dt=2015-05-08%2003%3A45%3A15 >>And I can reproduce the failure as well on my OSX laptop at least. > > Pushed something that hopefully fixes that already. Works for you? That's fine. Thanks for the quick fix. -- Michael
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
On Mon, May 11, 2015 at 1:49 AM, Thom Brown <thom@linux.com> wrote: > 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. What I'd intended here was the first suggestion of yours. Initially, it was actually a combination of Thom's two suggestions, but this was messed up at some point. What I suggest is that this be changed to match the first suggestion here (the intended message), since the "ON CONSTRAINT ... " variant is really just an escape hatch that I don't expect will see much use. I tried to encourage use of the conventional inference mechanism everywhere. -- Peter Geoghegan