Re: MERGE vs REPLACE

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.

Cheers,
Csaba.

On Thu, 2005-11-17 at 12:34, Zeugswetter Andreas DCP SD wrote:
> > The problem I try to solve is something along: a bunch of clients try
> to update a count, and ONE of them must initialize > the count if it
> does not exist... this can't be done with current infrastructure without
> race conditions.
> 
> The solution without merge but a unique key in other db's is:
> 
> update
> if no rows updated
>   insert
>   if duplicate key
>     update
>     if no rows updated goto insert
> 
> note, that the counter updates need to be of the form set x = x + ?
> where key=y 
> do you see a potential race condition with this ?
> In pg you also need a savepoint before the insert for this to work.
> 
> Depending on the ratio of insert vs update we also start with insert
> when 
> the insert succeeds more that 50% (I would use a higher percentage with
> pg though):
> 
> insert
>   if duplicate key
>     update
>     if no rows updated goto insert
> 
> Andreas