Slashdot Mirror
PostgreSQL 9.5 Does UPSERT Right (thenewstack.io)
joabj writes: For years, PostgreSQL users would ask when their favorite open source database system would get the UPSERT operator, which can either insert an entry or update it if a previous version already existed. Other RDMS have long offered this feature. Bruce Momjian, one of the chief contributors to PostgreSQL, admits to being embarrassed that it wasn't supported. Well, PostgreSQL 9.5, now generally available, finally offers a version of UPSERT and users may be glad the dev team took their time with it. Implementations of UPSERT on other database systems were "handled very badly," sometimes leading to unexpected error messages Momjian said. Turns out it is very difficult to implement on multi-user systems. "What is nice about our implementation is that it never generates an unexpected error. You can have multiple people doing this, and there is very little performance impact," Momjian said. Because it can work on multiple tables at once, it can even be used to merge one table into another.
Oh. How disappointing. I'll let the team know to remove the code that does this then seeing as you already have a solution.
PostgeSQL is one of those rare projects that delays a bit but makes sure that they plan their codebase ahead, and Get It Right The First Time.
Nobody should ever whine about delays in projects that value correctness over first to market, especially in an open source project.
This is great. I've been using PosgreSQL for a while now. It's one of those pieces of software that just does what it's told and doesn't let you down. While I'm saying this there are credible rumours to the effect that the Oracle merge operation is broken. Read the comments to the most upvoted answer at this stack exchange question. The final comment is: :( – Randy Magruder Aug 27 '15 at 16:05
Not reliably. I ended up with retry loops in the client code.
This makes me think that Bruce Momijan may have been thinking about Oracle's implementation of merge when he said that other implementations were handled very badly.
Postgres is leading edge in some things, like (ridiculously useful) table inheritance. Their implementation could have been slightly better (the most common complaint being that indices don't inherit, you need to re-add them on all descendants), but I'm very glad that they were early adopters on this one. They even pulled off multiple inheritance well.
He's the sort of person who would sell the Red Cross to Dracula.
Is this summary saying that, prior to now, you could not update data that was in the database?
No. In some cases you want to use a table like a key-value storage. If the key does not exist you need to insert a row with that key. Otherwise you'll want to keep only one row with that key, and just update the value.
One can always do a "if key exists then update else insert", but the problem is that this is not atomic, because the "key exists" check is a separate statement from the insert or update statement. This can lead to issues if you have multiple connections accessing the same keys at the same time.
The UPSERT allows you to do this as one atomic operation.
Most databases allow you to do transactions (BEGIN TRAN/COMMIT TRAN) that force it to be atomic.
Sure, but in that case you might get errors when you try to commit if another connection has changed the value in the meantime, and you'll have to retry the whole thing. That's what UPSERT avoids.