Re: MERGE vs REPLACE

On Fri, Nov 11, 2005 at 06:00:32PM -0500, Rod Taylor wrote:
> 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

Isn't there still a race between INSERT and UPDATE?

Low probability, for sure, as it would have had to not exist, then
exist, then not exist, but still possible.

I'd like a REPLACE that could be safe, or at least cause a COMMIT to
fail, for this reason.

Cheers,
mark

-- 
mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada
 One ring to rule them all, one ring to find them, one ring to bring them all                      and in the darkness
bindthem...
 
                          http://mark.mielke.cc/