Add a column to the nameq table designating the 'state' of the image.
Then your logic changes to "select * from nameq where serial = (select
min(serial) from nameq) and state="UNPROCESSED" (or whatever)
So you select for update, change the state, then process the
image....then delete.
Viola!
Mark Harrison wrote:
> I would like to try and build a queuing mechanism on top of Postgresql.
>
> Imagine an application where a large number of processes generate images
> and queue up thumbnail requests. A smaller number of processes (running
> on a dedicated set of machines) generate thumbnails for those images.
>
> Adding entries to the queue from multiple processes is easy, by executing
> statements such as:
>
> insert into nameq(action,name) values('add','foo');
>
> Now comes the part I'm not sure about. I can easily write a front
> end program that selects the lowest sequence number
>
> select * from nameq where serial = (select min(serial) from nameq);
>
> and then parcels that out to a subprocess for thumbnail generation.
> It would be really great if I could handle this without the front end
> program, so that multiple programs could do something like the following:
>
>
> select next image to be processed (with above select logic)
> process the image
> delete the row for that image
>
> I think that I can use "select for update" to obtain a write lock (so
> that
> I can safely delete the row when finished), but I'm unsure if it's
> possible
> to avoid the race condition where two processes would get the same row.
>