5 ms·
SQL MERGE is quite distinct from UPSERT
- skybrian 11y agoIt 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.
- masklinn 11y ago> 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.
- petergeoghegan 11y agoAvoiding 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.
- dang 11y agoIt'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.
- deleted 11y ago[deleted]
- applecore 11y agoVery cool!
- ExpiredLink 11y agoSQL has been too simple. Let's make it harder!
- dang 11y agoPlease 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 11y agoThen we should adapt our wording: CRUD becomes CRUDU.
- bsg75 11y agoThis change does make SQL simpler for PostgreSQL.
- cwyers 11y agoIs 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 11y agoIt'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.html http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.h...) instead of inventing something new.
- pzxc 11y agoI 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] ...
- Volundr 11y agoBecause 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.
- petergeoghegan 11y agoIt 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-control-structures.html#PLPGSQL-UPSERT-EXAMPLE http://www.postgresql.org/docs/current/static/plpgsql-contro... This is ugly an inefficient.
- deleted 11y ago[deleted]