Re: MERGE vs REPLACE

On Thu, 17 Nov 2005, Bruce Momjian wrote:

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

No matter what operation you start with you need a loop that try
insert/update until one of them succeed like in this example:

http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

Without a loop you might not get to execute neither the insert nor the
update. Why? Think about this example:

BEGIN

INSERT <- fail because there is a row already

<- before we manage to do the update someone
delete the row (which we can see in the
default transaction isolation level)

UPDATE <- fail because there is no row so we will loop
and try the insert again

<- before we manage to do the insert someone else does
an insert

INSERT <- fail because there is a row already

<- before we manage to do the update someone
delete the row
....

You might need to loop any number of times before you manage to perform
one of the two operations. Which operation you should start with depends
on which of the two cases is the common one.

--
/Dennis Björklund