Re: BUG #14343: UPSERT (ON CONFLICT) doesn't check ON CONFLICT constraint first

On Wed, Oct 5, 2016 at 7:34 PM, Reyes Ponce <reyes(dot)r(dot)ponce(at)gmail(dot)com> wrote:
> 1) Upsert is becoming a common feature (probably because it matches well
> with the definition of REST PUT and POST functionality).
>
> 2) The current INSERT... ON CONFLICT... DO UPDATE... doesn't handle the
> scenarios stated in my initial email, which upsert functionality can usually
> handle.
>
> 3) Upsert can be done with CTEs which can handle those scenarios but has
> potential race conditions.

I think that your stored procedure needs to learn about the different
cases. Simple as that.

If an INSERT would fail, you have no right to assume an upsert that
*might* take the insert path, but doesn't this time, should not fail
all the time. It's not as if a NOT NULL constraint is based on
anything other than the simple fact that the row that you mean to
INSERT has attributes that are NULL but shouldn't be. Unlike a unique
constraint, it doesn't matter what anybody else may be doing at the
same time, or may have inserted even before you began -- your tuple is
definitely going to violate the constraint.

> which is why I am asking about this (i.e. if the insert version of upsert
> can't handle these scenarios, maybe we need an upsert based on update).

For reasons that are rather complicated, "an upsert based on update"
is more or less an oxymoron. Basically, all of the useful upsert
guarantees hinge upon the implementation taking the alternative path
in the event of a would-be duplicate violation specifically. You can't
make that work with UPDATE, because it would have to be based on
something *not* existing, which is an impossibly ticklish condition to
rely on, unless you lock the entire table or something.

--
Peter Geoghegan