Re: SQL MERGE is quite distinct from UPSERT

On Sat, Jul 19, 2014 at 09:55:19PM -0700, Peter Geoghegan wrote:
> At a high level SQL MERGE is quite distinct from UPSERT, in that it is
> a utility command that performs inserts, updates and deletes while
> avoiding race conditions (e.g. unique constraint violations) on a more
> or less best effort basis. MERGE is conceptually messy. In contrast
> UPSERT is actually atomic, and having its behavior be relatively easy
> to reason about ought to be the top priority. There is a *really* big
> demand for UPSERT from users, not MERGE, although MERGE is certainly
> useful too.

FWIW, I agree. MERGE is hard enough as it is, but trying to guarentee
some kind of atomicity makes it nigh on impossible.  Indeed, after
reading what you wrote I think it may well be impossible to make it
atomic *and* make it perform in the general case.

So, +1 UPSERT.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.  -- Arthur Schopenhauer