Re: MERGE vs REPLACE

OK, in this case I don't care about either MERGE or REPLACE, but for an
UPSERT which does the locking :-)

Cheers,
Csaba.

On Thu, 2005-11-17 at 13:32, Martijn van Oosterhout wrote:
> 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,