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