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.
Any advice, comments, etc, appreciated!
Mark
---------------------------------------------------------
mh=# \d nameq
Table "public.nameq"
Column | Type | Modifiers
---------+-----------------------------+----------------------------------------------------
action | text | not null
name | text | not null
serial | bigint | default nextval('nameq_seq'::text)
addtime | timestamp without time zone | default ('now'::text)::timestamp(6) with time zone
Indexes:
"nameq_addtime" btree (addtime)
"nameq_ser" btree (serial)
mh=# select * from nameq;
action | name | serial | addtime
--------+------+--------+----------------------------
add | bar | 11 | 2004-09-20 11:50:19.756182
del | bar | 13 | 2004-09-20 11:50:25.080124
add | foo | 14 | 2004-09-20 11:50:28.536398
--
Mark Harrison
Pixar Animation Studios