Re: queueing via database table?

Mark --

As others have indicated, there may be some blocking issues with the approach you outlined.

A variant I have seen used in the past uses a table with a unique id for the job, the work queue it is in, a status flag, priority and at least one time stamp (and perhaps space for a process id).

Each client that wants work issues a request (SELECT FOR UPDATE) to get the next job in its queue that has a status flag of "Available" ordered by priority or initial time of creation, etc.; update that entry with the current timestamp (and perhaps the process id of the client) and set the status flag to show the job is now being worked on all in one transaction.

This releases the job but now with a changed status flag so other processes pulling work from the same queue won't see it anymore.

When the job finishes it selects its entry and updates the status flag and timestamp (and probably clears its process id). Logic for how to bump a job to the next step can be embedded in the client or in another process, depending on your needs.

It is useful to have a daemon or some other process to sweep the queue table and at least send an alert about stale or frozen jobs.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org on behalf of Mark Harrison
Sent: Tue 1/2/2007 10:34 PM
To: pgsql-general(at)postgresql(dot)org
Cc:
Subject: [GENERAL] queueing via database table?

I have a cluster of CPUs generating thumbnails for
a render farm. I would like to place thumbnail
requests on a queue, and have the cluster of client
dequeue the requests and process them.

Of course, each request should be only dequeued once...
if a thumbnail is being processed by one CPU, it
shouldn't be processed by another CPU.

Does the following sound like a reasonable approach?
If not, what's a good way to go?

The processes generating the requests will insert into
a queue table. They may add a priority and timestamp.

The several processes servicing the requests will do a
SELECT FOR UPDATE where ... limit 1, generate thumbnail,
delete the record and commit.

Comments and suggestions welcome,
Mark

--
Mark Harrison
Pixar Animation Studios

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

-------------------------------------------------------
Click link below if it is SPAM gsw(at)globexplorer(dot)com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=459b5025191744846743324&user=gsw(at)globexplorer(dot)com&retrain=spam&template=history&history_page=1"
!DSPAM:459b5025191744846743324!
-------------------------------------------------------