

SQL MERGE is quite distinct from UPSERT - caf
http://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com

======
dang
It's cool to see this show up today, because yesterday we asked the
programmers who implemented upsert in Postgres if they'd be interested in
having a technical discussion about it on HN. They said yes, so here's the
plan: Peter's going to write a high-level post about the design and evolution
of the feature. Then he, Andres, Heikki, and anybody else who's interested
will join the thread where we can all learn about how they did it and what the
challenges were.

On the HN side, this is the sort of experiment we'd like to do more of, and
this seemed like a great place to try it, since there's clearly unsatisfied
curiosity in the community (including us!) about how upsert was implemented.

~~~
applecore
Very cool!

------
skybrian
It seems a bit odd that they try so hard to avoid reporting an error. Any I/O
operation can fail. Furthermore, any I/O operation with a timeout can fail
unpredictably, since a timeout introduces a race condition.

~~~
petergeoghegan
Avoiding reporting an error only extends to errors that the user can
reasonably hope to avoid, like spurious duplicate violations (from the
"arbiter unique index"), and what I call unprincipled deadlocks (deadlocks
where there is no user-visible mutual dependency, that users can't really do
anything about except not use UPSERT so much). The point of UPSERT is that
you're not supposed to have to worry about those.

Obviously if you run out of disk space or something like that, that will throw
an error aborting the transaction.

------
pzxc
I am not the most advanced SQL developer by any means. Why is UPSERT
needed/advantageous when you can do this?

    
    
      update [table] set ...; if @@ROWCOUNT = 0 insert into [table] ...

~~~
petergeoghegan
It has race conditions. The only work-around that works (unless perhaps you
have a 2-phase locking system, and are using serializable isolation level)
involves subtransactions.

For example: [http://www.postgresql.org/docs/current/static/plpgsql-
contro...](http://www.postgresql.org/docs/current/static/plpgsql-control-
structures.html#PLPGSQL-UPSERT-EXAMPLE)

This is ugly an inefficient.

------
cwyers
Is there a source somewhere that details how the implementation of
MERGE/UPSERT in other databases falls short of what Postgres implemented for
INSERT ON CONFLICT?

------
ccleve
It's possible (but maybe not) that this was prompted by my comment a week ago:

[https://news.ycombinator.com/item?id=9541362](https://news.ycombinator.com/item?id=9541362)

MERGE is ANSI standard SQL, and so far as I know it's the only upsert-like
syntax supported by both Oracle and MS SQL. (Please correct me if I'm wrong.)

Even if the MERGE command isn't what we're looking for, we really should make
an effort to come up with something that can be implemented in a uniform way
across databases.

Let's consider whether to use the MySQL syntax
([http://dev.mysql.com/doc/refman/5.6/en/insert-on-
duplicate.h...](http://dev.mysql.com/doc/refman/5.6/en/insert-on-
duplicate.html)) instead of inventing something new.

------
ExpiredLink
SQL has been too simple. Let's make it harder!

~~~
dang
Please don't be snarky about other people's work. The things users have had to
do to get this behavior in their applications are far from simple. It was the
most-requested feature for a reason.

~~~
ExpiredLink
Then we should adapt our wording: CRUD becomes CRUDU.

