Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 066AA2E004F
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Sun,  6 Apr 2008 17:17:41 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 58161-07
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Sun,  6 Apr 2008 17:17:38 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from fhw-relay07.plus.net (fhw-relay07.plus.net [212.159.14.215])
	by developer.postgresql.org (Postfix) with ESMTP id 5F5322E0048
	for <pgsql-performance@postgresql.org>;
	Sun,  6 Apr 2008 17:17:38 -0300 (ADT)
Received: from [80.229.150.39] (helo=mansionfamily.plus.com)
	by fhw-relay07.plus.net with esmtp (Exim) id 1JibJ1-0007te-Ns
	for pgsql-performance@postgresql.org; Sun, 06 Apr 2008 21:17:36 +0100
Received: from [192.168.0.89] ([192.168.0.89]:2885)
	by mansionfamily.plus.com with [XMail 1.22 ESMTP Server]
	id <S161B6> for <pgsql-performance@postgresql.org> from
	<james@mansionfamily.plus.com>; Sun, 6 Apr 2008 21:22:41 +0100
Message-ID: <47F930D5.5030607@mansionfamily.plus.com>
Date: Sun, 06 Apr 2008 21:21:41 +0100
From: James Mansion <james@mansionfamily.plus.com>
Organization: MsgWare
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
To: Robins Tharakan <tharakan@gmail.com>
CC: chemuduguntar@gmail.com,  pgsql-performance@postgresql.org
Subject: Re: Performance Implications of Using Exceptions
References: <7a4208ef0803311720s1dac8362gff2477d3dc534968@mail.gmail.com>
	<20080401005235.GM4999@tamriel.snowman.net>
	<F0238EBA67824444BC1CB4700960CB4804F8B1BA@dmpeints002.isotach.com>
	<47F3DC5A.30705@mansionfamily.plus.com>
	<27de86ba-4756-4ec3-a63e-07a2bcb4a531@i12g2000prf.googlegroups.com>
	<36af4bed0804030627i32b5d9cdk255c757ccff45c32@mail.gmail.com>
In-Reply-To: <36af4bed0804030627i32b5d9cdk255c757ccff45c32@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Plusnet-Relay: 9bc9e9158c2650334f95e48153756035
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200804/50
X-Sequence-Number: 29543

Robins Tharakan wrote:
>
> 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.
How does that help?

If the matching row doesn't exist at that point - what is there to get 
locked?

The problem is that you need to effectively assert a lock on the primary 
key so that you can update
the row (if it exists) or insert a row with that key (if it doesn't) 
without checking and then inserting and
finding that some other guy you were racing performed the insert and you 
get a duplicate key error.

How does Postgresql protect against this?

James