Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 050B82E0059 for ; Thu, 3 Apr 2008 10:28:00 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 91619-09 for ; Thu, 3 Apr 2008 10:27:47 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from hu-out-0506.google.com (hu-out-0506.google.com [72.14.214.230]) by developer.postgresql.org (Postfix) with ESMTP id D9E9A2E0049 for ; Thu, 3 Apr 2008 10:27:52 -0300 (ADT) Received: by hu-out-0506.google.com with SMTP id 22so1124120hug.23 for ; Thu, 03 Apr 2008 06:27:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=GNpKQ45Tsd6ezKuQHeihLCBgV/BhMPHDtsEwivZDiSo=; b=PpU0H8BmA3cFBkyMdMYACWX1EY9GYGcx2RoqOPl/nuUbQAewImLFC+T4QC4S3pfDylbqaStGqOGaWoob4jZHf2u2ZUt6kNiKONjQL2m6B9Sr8/vIHuKEmz6U64jLCYXie4jaE907PFaXKqhqYn4EQzsPYk0ZsCP/gM4ZssNPLG8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=W/YD3sLBUAxG+upUiyrujPpznu1Lkn5HToiRYZC+j9bKBSjO+ts5bltJsp2epbhrVfm1jRoVhf0sL4QeSSjCB3ZOI7qzXLscPe8Q9DAKea1N+nsU/2U55BRtjGIDrgqyfWF3vkjM8SvHByUV6dyEwnjOX+hR7ptB/MQyN/WcKpA= Received: by 10.82.185.3 with SMTP id i3mr27045700buf.15.1207229247296; Thu, 03 Apr 2008 06:27:27 -0700 (PDT) Received: by 10.82.112.18 with HTTP; Thu, 3 Apr 2008 06:27:27 -0700 (PDT) Message-ID: <36af4bed0804030627i32b5d9cdk255c757ccff45c32@mail.gmail.com> Date: Thu, 3 Apr 2008 18:57:27 +0530 From: "Robins Tharakan" To: "chemuduguntar@gmail.com" Subject: Re: Performance Implications of Using Exceptions Cc: pgsql-performance@postgresql.org In-Reply-To: <27de86ba-4756-4ec3-a63e-07a2bcb4a531@i12g2000prf.googlegroups.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1480_10335175.1207229247242" References: <7a4208ef0803311720s1dac8362gff2477d3dc534968@mail.gmail.com> <20080401005235.GM4999@tamriel.snowman.net> <47F3DC5A.30705@mansionfamily.plus.com> <27de86ba-4756-4ec3-a63e-07a2bcb4a531@i12g2000prf.googlegroups.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/31 X-Sequence-Number: 29524 ------=_Part_1480_10335175.1207229247242 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I think James was talking about Sybase. Postgresql on the other hand has a slightly better way to do this. SELECT ... FOR UPDATE allows you to lock a given row (based on the SELECT ... WHERE clause) and update it... without worrying about a concurrent modification. Of course, if the SELECT ... WHERE didn't bring up any rows, you would need to do an INSERT anyway. Read more about SELECT ... FOR UPDATE here: http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-FOR-UPDATE-SHARE *Robins* On Thu, Apr 3, 2008 at 2:48 PM, chemuduguntar@gmail.com < chemuduguntar@gmail.com> wrote: > > 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > ------=_Part_1480_10335175.1207229247242 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
I think James was talking about Sybase. Postgresql on the other hand has a slightly better way to do this.

SELECT ... FOR UPDATE allows you to lock a given row (based on the SELECT ... WHERE clause) and update it... without worrying about a concurrent modification. Of course, if the SELECT ... WHERE didn't bring up any rows, you would need to do an INSERT anyway.

Read more about SELECT ... FOR UPDATE here: http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-FOR-UPDATE-SHARE

Robins

On Thu, Apr 3, 2008 at 2:48 PM, chemuduguntar@gmail.com <chemuduguntar@gmail.com> wrote:
> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

------=_Part_1480_10335175.1207229247242--