Re: MERGE vs REPLACE

Unless you have a table lock, INSERT has to be before UPDATE, think
UPDATE, UPDATE (both fail), INSERT, INSERT.

---------------------------------------------------------------------------

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
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073