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@pixar.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
>