Re: BUG #13846: INSERT ON CONFLICT consumessequencersonconflicts

On Fri, May 6, 2016 at 12:02 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> To solve this situation it is likely that some form of "UPDATE ON MISSING
> INSERT" would need to be designed.  The insert portion would specify
> "DEFAULT" for sequence columns and would execute nextval() only if the ON
> MISSING portion is executed.

That's unworkable, at least without accepting a bunch of new
edge-cases, like having the insert then have a duplicate violation
involving a value that was determined to not exist in the first phase.
IOW, it's unworkable to do an insert on the basis of an *absence* of
something in an index or in a table (and not get those edge-cases).
Doing so on the basis of the *presence* of a value (i.e. INSERT ... ON
CONFLICT DO UPDATE as implemented) lets the implementation clamp down
on race conditions enough to provide those useful user-visible
guarantees about getting 1 of 2 possible outcomes.

There are multiple definitions of a value "existing" here that are in
tension here. It's rather complicated.

--
Peter Geoghegan