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