SQL MERGE is quite distinct from UPSERT | Hacker News Reader

SQL MERGE is quite distinct from UPSERT

postgresql.org

60 pointscaf14 comments

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.

Very cool!

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.

> It seems a bit odd that they try so hard to avoid reporting an error.

Is it really? If an upsert can race, deadlock or trigger constraint violation there's no point to having it, existing construct can race, deadlock or violate constraints just fine.

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.

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] ...

Because I may want to insert a large number of rows. The above requires me to do so one by one, which is pretty inefficient.

There are other ways to emulate upserts, but they tend to have performance and/or concurrency considerations.

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...

This is ugly an inefficient.

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?

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

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...) instead of inventing something new.

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

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.

Then we should adapt our wording: CRUD becomes CRUDU.

This change does make SQL simpler for PostgreSQL.

Reply on news.ycombinator.com