Re: using database for queuing operations?

Well everybody else has thrown in there suggestions. I have several
processes that do something similar to this. Granted I'm moving data
around instead of processing images but the queue principles are the
same. I use a nullable process_time to keep track of state because I
maintain history file.

First I spawn the task from a cron job periodically although this
could also be done by a daemon. The process has a limit of how many
units it will perform. Since my data units are small I have about
1000 max per process.

SELECT serial FROM queue WHERE process_time IS NULL ORDER BY serial LIMIT 1000.

This gives me 1000 units. I then loop through each unit as
$this_serial. And try
SELECT serial FROM queue WHERE serial = $this_serial AND process_date
is NULL FOR UPDATE.

I check if a row is returned. If it is not then I know another
process took the job and continue on through the list.

Next I move data, or in your case process the image. Finally if my
process succeeds I
UPDATE queue SET process_time = CURRENT_TIMESTAMP WHERE serial = $this_serial
and commit the transaction otherwise I rollback and clear my lock.

This works great for me because if the process fails then I never
update and the next run will pick it up and retry.

In my world I have to mainly deal with the possibility of runs
colliding with each other. It seems to me that since you have several
machines you might want to use a start time and a finish time. You
could then have a garbage collection clear out finished jobs if you
need. This also gives you the benefit of keeping track of zombied
processes.

On Mon, 20 Sep 2004 17:13:30 -0700, Mark Harrison <mh(at)pixar(dot)com> wrote:
> Tom Lane wrote:
> > See the archives; this has been discussed in great detail before
> > (several times before, if memory serves).
> >
> > regards, tom lane
>
> Sorry for the cluelessness, but searching on queuing, scheduling,
> and their spelling variants isn't turning up anything useful. Got
> something else I can search on?
>
> TIA!
> Mark
>
> PS, so far the comments received have been very useful... thanks so much!!!
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>