Re: MERGE vs REPLACE

See 'merge_db' in http://lnk.nu/postgresql.org/5sl.html

On Fri, Nov 11, 2005 at 10:07:07PM -0500, Rod Taylor wrote:
> On Fri, 2005-11-11 at 18:36 -0500, mark@mark.mielke.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
> 
> -- 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461