> I find myself having to do this in Sybase, but it sucks because there's > a race - if there's no row updated then there's no lock and you race > another thread doing the same thing. So you grab a row lock on a > sacrificial row used as a mutex, or just a table lock. Or you just > accept that sometimes you have to detect the insert fail and retry the > whole transaction. Which is sucky however you look at it. hmm should I be worried ? I am doing an 'update if not found insert', in some cases I have found that I need to select anyway, for e.g. take away 20 dollars from this person; (all variables prefixed with _ are local variables) select into _money money from person_money where person_id = _person; if (not found) then insert into person_money (person_id, money) values (_person, - _requested_amount); else update person_money set money = money - _requested_amount where person_id = _person; -- return new quantity return _money - _requested_quantity; -- <- i need the quantity so I have to select here. end if; if I am not mistaken your are saying that between the select and the if (not found) then ... end if; block ... another concurrent process could be executing the same thing and insert ... while in the first thread found is still 'false' and so it ends up inserting and over writing / causing a unique violation or some kind? BTW, I did a benchmark with and without exceptions, the exceptions version was very slow, so slow that I ended up killing it ... I am sure it would have taken atleast 5 hours (was already 3 hours in) ... versus, 25 mins! I guess the trouble was that I was using exceptions to overload 'normal' flow ... i.e. update if exists else update is not an exceptional circumstance and so exceptions are a bad choice. It would be interesting to see how much overhead exception containing functions present when they do not throw any exceptions ... for never to every few records to all the time ... maybe I will try it with my parsing functions (which catch exceptions thrown by substring()). -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
