Re: queueing via database table?

On Tue, 2007-01-02 at 22:34 -0800, Mark Harrison wrote:
> 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.

Here's what I'd do. Create two sequences. Sequence one is used to
assign ids to the thumbnail records when they're placed into the control
table. The other is used to "check out" the records.

A process selects nextval from sequence two, and then selects the
corresponding record info from the control table, and marks the record
as being in work. When it's done, it marks it as done, and selects
another value from the second sequence and repeats the process.

Every now and then run a check program to look for thumbs that have been
missed or skipped and process them or assign them a new id from sequence
one to put them back into the queue.