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, [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
