From: Dmitriy Igrishin <dmitigr@gmail.com>
To: Joe Carr <joe.carr@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Correct usage of FOR UPDATE?
Date: Sat, 11 Dec 2010 13:32:10 +0300
Message-ID: <AANLkTim9x6prp_Vxpzadq3KV0Pd2RhVpUSjyT8ijsqUS@mail.gmail.com> (raw)
In-Reply-To: <AANLkTin1qX+ZSOGsvdY7RH0dkTEkxth-XaAy2DSA8HTd@mail.gmail.com>
References: <AANLkTin1qX+ZSOGsvdY7RH0dkTEkxth-XaAy2DSA8HTd@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dmitigr@gmail.com, joe.carr@gmail.com
Subject: Re: Correct usage of FOR UPDATE?
In-Reply-To: <AANLkTim9x6prp_Vxpzadq3KV0Pd2RhVpUSjyT8ijsqUS@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox