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