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, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
