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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
