Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Dave Gomboc <dave@cs.ualberta.ca>,
	Tambet Matiisen <tambet.matiisen@mail.ee>
Subject: Re: select from update from select?
Date: Wed, 19 Feb 2003 12:49:03 +0000
User-Agent: KMail/1.4.3
Cc: pgsql-sql@postgresql.org
References: <Pine.LNX.4.44.0302180139210.30967-100000@boyne.cs.ualberta.ca>
In-Reply-To: <Pine.LNX.4.44.0302180139210.30967-100000@boyne.cs.ualberta.ca>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200302191249.04316.dev@archonet.com>

On Tuesday 18 Feb 2003 8:57 am, Dave Gomboc wrote:
> On Mon, 17 Feb 2003, Tambet Matiisen wrote:
> > Why not just store the work_order_id in temporary variable? You need to
> > do SELECT FOR UDPATE in this case, to avoid reserving the same work
> > twice.
>
> This doesn't work -- for the reason you give:
> > While testing this, I discovered, that it doesn't work exactly as I
> > expected. Suppose one transaction locks row with work_queue_id =3D 1. N=
ow
> > before the first transactions has finished, second comes in and tries to
> > get lock for the same row. SELECT FOR UPDATE in second transaction
> > blocks as expected. But when first transaction finishes, the SELECT in
> > second transaction returns 0 rows, not next row that satisfies the
> > conditions. I'm not sure if this should be considered bug or feature.
> > But you have to test if the query returned any rows anyway, because the
> > same happens when there are no unreserved works in queue.
>
> It's definitely a bug to me.  The purpose of the work_queue is hold the
> pool of tasks to be done, and to allow different machines to indicate that
> they will do a certain task without any master program to tell machines
> what work to do.  With the above process, concurrent attempts block
> (except for one), then they don't even get a row!  I definitely want
> concurrent requests to not haggle over the same row, there'd be way too
> much contention between machines, and it defeats the whole purpose of more
> than one job being available simultaneously.  If PostgreSQL had a SKIP
> LOCKED feature, I'd be in business, but (AFAIK) it doesn't?  Using NOWAIT
> and doing a large number of retries using some random back-off scheme
> seems like a big hack rather than something that would actually work
> acceptably.

This has come up before - check the archives for details.

One solution is to have a "processed" flag with values:

U - unprocessed
X - executing
D - done

You claim a job by changing from U to X - this takes very little time, so y=
our=20
worker processes can simply sit in a loop trying. When work is complete the=
=20
flag is marked D.
This has the added advantage that you can spot rows where processing starte=
d=20
but didn't complete due to a crash etc.

Another solution if you know how many workers you have is to have a manager=
=20
process which tags each job with a worker ID, but this does tend to make th=
e=20
manager a point of failure.

--=20
  Richard Huxton