Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 198B01337B83
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  9 Dec 2010 15:29:51 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 86682-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  9 Dec 2010 19:29:43 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ey0-f178.google.com (mail-ey0-f178.google.com
	[209.85.215.178])
	by mail.postgresql.org (Postfix) with ESMTP id 4A49C1337B7E
	for <pgsql-sql@postgresql.org>; Thu,  9 Dec 2010 15:29:43 -0400 (AST)
Received: by eyh5 with SMTP id 5so2004949eyh.23
	for <pgsql-sql@postgresql.org>; Thu, 09 Dec 2010 11:29:42 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:received:date:message-id
	:subject:from:to:content-type;
	bh=IbSFT4mMuMDVDF090t2DvXFHR7yr7xzmTeUCgPv7XoM=;
	b=RgwshpUTmgr0ieBlk9XzG0m6UbnX67rBZyVMqOZpCYrRguCHVGt42nrCPYzs/jiCbO
	qf3yDA6LTBNws+o7p7nSIwjwZ65JFMSI3pTN7OyjdKnYrmvf2wJzgC00b1Np+XQj/yA9
	nVgmhOUnv0CBkvaYPqJmDBYUFvoqAYguptvFw=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=clb7zxTj9d9zI9denQMs781m/2fQ2TM0VHixg4GHKHpzCZrq80olLMCL5xkScFmQ2R
	ZZKRurVaLHJDoR/BMqeJypqNTEb1vixHC3Eh7ni7+KWEAT0IJAFxflyIFEWc3kiJflj0
	1L1QxnI+U5qYy/Do+dGHiPbyi5TpXt8L0OOeY=
MIME-Version: 1.0
Received: by 10.213.11.8 with SMTP id r8mr1122075ebr.26.1291922982054; Thu, 09
	Dec 2010 11:29:42 -0800 (PST)
Received: by 10.213.34.82 with HTTP; Thu, 9 Dec 2010 11:29:42 -0800 (PST)
Date: Thu, 9 Dec 2010 14:29:42 -0500
Message-ID: <AANLkTin1qX+ZSOGsvdY7RH0dkTEkxth-XaAy2DSA8HTd@mail.gmail.com>
Subject: Correct usage of FOR UPDATE?
From: Joe Carr <joe.carr@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=0015174c11026c2d240496ff4288
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.588 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7,
	T_TO_NO_BRKTS_FREEMAIL=0.01
X-Spam-Level: 
X-Archive-Number: 201012/56
X-Sequence-Number: 34892

--0015174c11026c2d240496ff4288
Content-Type: text/plain; charset=ISO-8859-1

Hello,
I have a simple table-based queue system, and I'd looking for some advice on
improving my dequeue function. it boils down to:

SELECT id
FROM queue
WHERE <whereclause>
FOR UPDATE NOWAIT;

which works well, in that no item gets dequeued more that once. The issue,
however is that when a contention occurs, the error "could not obtain lock
on row in relation" is raised.

Is there a way that I can rewrite this so that when a contention occurs,
there is no error? I would like the "winning" process to lock the row, and
the "losing" process to select null rather than raise the rowlock error.

thanks beforehand for any help!

--0015174c11026c2d240496ff4288
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div>Hello,</div><div>I have a simple table-based queue system, and I&#39;d=
 looking for some advice on improving my dequeue function. it boils down to=
:</div><div><br></div><div>SELECT id</div><div>FROM queue</div><div>WHERE &=
lt;whereclause&gt;</div>
<div>FOR UPDATE NOWAIT;</div><div><br></div><div>which works well, in that =
no item gets dequeued more that once. The issue, however is that when a con=
tention occurs, the error &quot;could not obtain lock on row in relation&qu=
ot; is raised.=A0</div>
<div><br></div><div>Is there a way that I can rewrite this so that when a c=
ontention occurs, there is no error? I would like the &quot;winning&quot; p=
rocess to lock the row, and the &quot;losing&quot; process to select null r=
ather than raise the rowlock error.</div>
<div><br></div><div>thanks beforehand for any help!</div>

--0015174c11026c2d240496ff4288--