Re: MERGE vs REPLACE

Josh Berkus wrote:
> Jaime,
>
> > why? seems that REPLACE only work if there are at least one row
> > matching...
>
> Scenario:
>
> session1: REPLACE .... 1
> session2: REPLACE ..... 1
> session1: check to see that "1" exists .... no
> session2: check to see that "1" exists .... no
> session1: INSERT 1
> session2: INSERT 1 .... ERROR
>
> Get the picture? The only way to avoid a race condition is to be able to
> do "predicate locking", that is to lock the table against any data write
> matching that predicate.

Isn't the standard practice to do the INSERT, and if that fails, do
the UPDATE? In fact, if someone is already doing a INSERT inside a
transaction, it will wait for the transaction to complete so you can
then do the UPDATE, or complete the INSERT if the transaction aborted.

--
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