Re: MERGE vs REPLACE

Oh, good point. I was thinking just about concurrent MERGEs. However,
it is more complicated than that. By definitaion you can not see
changes from other transactions while your statement is being run (even
if you increment CommandCounter), so to be atomic, you would still see
the row even though some other transaction had deleted it.

I think we avoid that now because UPDATE, (which is a DELETE then
INSERT) chains the tuples together so others see the activity happening.

Seems like we are going to have to peek at rows like we do now for
INSERT and peek at index rows, if I remember correctly. I can't think
of any other place in the code where we loop around other backend's
activity like this.

This could be tricky.

---------------------------------------------------------------------------

Dennis Bjorklund wrote:
> 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
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073