Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id DB887133798E
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sat, 11 Dec 2010 06:32:20 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 00346-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sat, 11 Dec 2010 10:32:13 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-fx0-f49.google.com (mail-fx0-f49.google.com
	[209.85.161.49])
	by mail.postgresql.org (Postfix) with ESMTP id 34FB61337B58
	for <pgsql-sql@postgresql.org>; Sat, 11 Dec 2010 06:32:12 -0400 (AST)
Received: by fxm19 with SMTP id 19so4565289fxm.22
	for <pgsql-sql@postgresql.org>; Sat, 11 Dec 2010 02:32:12 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:received:in-reply-to
	:references:date:message-id:subject:from:to:cc:content-type;
	bh=rzgoHKvZj48FaWJbJH9jjIXorHA1972j1w0badVt7eM=;
	b=DhrTOG1+sDr6fQtTYg9yu9aHXBL8Q3Dn2byye3DYAJFcWNf4cgamAQxC7fPYXbYDzE
	IWWFXS6t9XoCbJIZT8jN1Q1H1J9vdJMbIplYa3wgvhOA+MEUGA0p7Qc+QLuyr/Bsm7e0
	XiA2rjBG0rnhkLCj/OiisAZeyynCuPbrkrADA=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=EVX5z3RrHAt53XxwuHxq52HJs7q+UOW0Qh72rDS0HdGuSMhDs/8zlQtnMUCqwNCLD9
	b4Sf3pTkB2CPob2KVLR1flroX9RPLtRRMhjX6D8z8qRk0Hnr56iI1DSqofv/m/cn3HQJ
	ux1SyDNW28ZL6GoGZQOcqtcnOo3yw1mzEwxuU=
MIME-Version: 1.0
Received: by 10.223.74.6 with SMTP id s6mr1982997faj.111.1292063530970; Sat,
	11 Dec 2010 02:32:10 -0800 (PST)
Received: by 10.223.96.141 with HTTP; Sat, 11 Dec 2010 02:32:10 -0800 (PST)
In-Reply-To: <AANLkTin1qX+ZSOGsvdY7RH0dkTEkxth-XaAy2DSA8HTd@mail.gmail.com>
References: <AANLkTin1qX+ZSOGsvdY7RH0dkTEkxth-XaAy2DSA8HTd@mail.gmail.com>
Date: Sat, 11 Dec 2010 13:32:10 +0300
Message-ID: <AANLkTim9x6prp_Vxpzadq3KV0Pd2RhVpUSjyT8ijsqUS@mail.gmail.com>
Subject: Re: Correct usage of FOR UPDATE?
From: Dmitriy Igrishin <dmitigr@gmail.com>
To: Joe Carr <joe.carr@gmail.com>
Cc: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=20cf3054a355ca6dbb04971ffbcf
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7
X-Spam-Level: 
X-Archive-Number: 201012/59
X-Sequence-Number: 34895

--20cf3054a355ca6dbb04971ffbcf
Content-Type: text/plain; charset=UTF-8

Hey Joe,

You can wrap the given query in the PL/pgSQL function,
catch the exception and return NULL if it occurs. Please see
http://www.postgresql.org/docs/9.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

2010/12/9 Joe Carr <joe.carr@gmail.com>

> 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!
>



-- 
// Dmitriy.

--20cf3054a355ca6dbb04971ffbcf
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hey Joe,<br><br>You can wrap the given query in the PL/pgSQL function,<br>c=
atch the exception and return NULL if it occurs. Please see<br><a href=3D"h=
ttp://www.postgresql.org/docs/9.0/static/plpgsql-control-structures.html#PL=
PGSQL-ERROR-TRAPPING">http://www.postgresql.org/docs/9.0/static/plpgsql-con=
trol-structures.html#PLPGSQL-ERROR-TRAPPING</a> <br>
<br><div class=3D"gmail_quote">2010/12/9 Joe Carr <span dir=3D"ltr">&lt;<a =
href=3D"mailto:joe.carr@gmail.com">joe.carr@gmail.com</a>&gt;</span><br><bl=
ockquote class=3D"gmail_quote" style=3D"margin: 0pt 0pt 0pt 0.8ex; border-l=
eft: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<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.=C2=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>
</blockquote></div><br><br clear=3D"all"><br>-- <br>// Dmitriy.<br><br><br>

--20cf3054a355ca6dbb04971ffbcf--