On Thu, Nov 17, 2005 at 12:52:53PM +0100, Csaba Nagy wrote: > Yes, these algorithms are clear to me, but they don't work for batch > updates in postgres without savepoints before each row insert/update, > which is not good for performance (not to mention on older postgres > versions without savepoint support it won't work at all). If there is a > way of no race condition, no performance penalty, that would be > something new and useful. I just guess the MERGE would provide that.
Well, then you guess wrong. This isn't what MERGE is for. MERGE is just a neat way of specifying the UPDATE and INSERT cases in the same statement. It doesn't remove the possibility duplicate inserts and thus primary key violations. If someone wants to make extensions to MERGE so that it can avoid the race condition and avoid the duplicate key violations, that's fine. But be aware that this is outside of the spec. It may be a useful addition, but perhaps we should consider MERGE and REPLACE as completely seperate targets. MERGE has a whole join construction with subqueries that would be a pain to make work in a way that is truly serialisable. REPLACE deals with only one row and tries to solve the race for that case only. Much easier to consider them seperately, no? I guess what's really irritating is that this clearly exposes the case listed in the docs as "Why SERIALIZABLE isn't in all cases". If we could solve that for MERGE, we could probably solve it in the general case too. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
pgpwqXKPvo94y.pgp
Description: PGP signature
