3 ms·
The difficulty is supporting high concurrency AND correctness AND high performance with this feature. Most databases that have supported this in the past have s
by jandrewrogers 12y ago
The difficulty is supporting high concurrency AND correctness AND high performance with this feature. Most databases that have supported this in the past have sacrificed one of these; PostgreSQL requires an implementation with no impact to existing users and without weakening the guarantees people are used to.
In most good databases you can compose an upsert transactionally from a set of independent operations. The problem is that this is slow because you are plumbing the database engine multiple times for an operation that could be executed in one shot in principle. A native upsert implementation provides a fast, single operation. In the case of PostgreSQL though, they had to support this in the context of high-performance, high-concurrency, and correctness which makes it tricky because it is relatively complex to implement under those constraints.
- ryanjshaw 12y agoThis implementation of upsert is especially nice because it is atomic [1]: <literal>ON CONFLICT DO UPDATE</literal> guarantees an atomic <command>INSERT</command> or <command>UPDATE</command> outcome - provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. I don't know about Oracle or MySQL, but the SQL Server implementation of MERGE requires you to carefully consider which locks to use and it's very easy to shoot yourself in the foot with. [1] http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=doc/src/sgml/ref/insert.sgml;h=c88d1b7b50a30dacf4c1809e73e84eacf2ca3611;hp=a3cccb9f7c79a5bac71ed35a96d171e9b9587041;hb=168d5805e4c08bed7b95d351bf097cff7c07dd65;hpb=2c8f4836db058d0715bc30a30655d646287ba509 http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdif...