Re: MERGE vs REPLACE

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(at)svana(dot)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.