Re: MERGE vs REPLACE

On Fri, 2005-11-11 at 18:36 -0500, mark(at)mark(dot)mielke(dot)cc wrote:
> 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?

I suppose there is although I hadn't noticed before. I've never run into
it and always check to ensure the expected number of tuples were touched
by the update or delete.

Within the PostgreSQL backend you might get away with having your insert
hold a lock on the index page and follow it up with a FOR UPDATE lock on
the offending tuple thus ensuring that your update will succeed. If you
hack index mechanisms for the support you don't need the SAVEPOINT
either -- just don't throw an error when you run across the existing
entry.

For client side code one possibility is to repeat until successful.

WHILE
SELECT FOR UPDATE;
IF NOT EXISTS THEN
SAVEPOINT
INSERT;
IF UNIQUE VIOLATION THEN
ROLLBACK TO SAVEPOINT;
ELSE
RELEASE SAVEPOINT
EXIT;
FI
ELSE
UPDATE;
EXIT;
END

-- Check for infinite loop
END

--