commit grep author committer pickaxe re ? search:

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.



122 files changed: