Re: MERGE vs REPLACE

On Fri, 2005-11-11 at 14:40 -0800, Josh Berkus wrote:
> Jaime,
>
> > why? seems that REPLACE only work if there are at least one row
> > matching...

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

So? That is what save points are for. You can even skip the select for
update if you don't mind dead tuples from the attempted insert.

SELECT ... FOR UPDATE;
IF not exists THEN
SAVEPOINT;
INSERT ;
IF UNIQUE VIOLATION THEN
/* Someone else inserted between the SELECT and our INSERT */
ROLLBACK TO SAVEPOINT;
UPDATE;
ELSE
RELEASE SAVEPOINT;
FI
ELSE
UPDATE;
FI
--