Re: BUG #13846: INSERT ON CONFLICT consumessequencersonconflicts

On Fri, May 6, 2016 at 12:10 PM, Peter Geoghegan <pg@heroku.com> wrote:

> 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 MISSI=
NG
> > 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.
>

=E2=80=8BUPDATE ON MISSING INSERT ON CONFLICT DO THE ORIGINAL UPDATE ...

=E2=80=8BI'm nowhere near close enough to this to contribute deeply - the m=
ost I
hope for is to spark an idea in someone else.

=E2=80=8BDavid J.
=E2=80=8B