Hi,
I currently have a simple queue written ontop of Postgres. Jobs are
inserted and workers periodically check for jobs they can do, do them,
and then delete the rows. pg_try_advisory_lock is used to (attempt
to) stop two workers from doing the same job.
(I'm working on moving to a "real" messaging queue right now, this is
more a point of curiosity and education now.)
Here is my queue table,
CREATE TABLE queue ( id serial NOT NULL PRIMARY KEY, rcvd timestamp with time zone, sent timestamp with time
zone, host character varying(32), job character varying(32), arg text
);
Here is an example query,
SELECT q.*
FROM (SELECT id, job, arg FROM queue WHERE job = 'foo' OR job = 'bar' OFFSET 0) AS q
WHERE pg_try_advisory_lock(1, q.id)
LIMIT 10
(For information on OFFSET 0 see:
http://blog.endpoint.com/2009/04/offset-0-ftw.html)
Now if I have two workers running I will periodically see that each
worker gets a row with the same q.id (and thus does the work). How is
that possible? The outer query seemingly does a WHERE on an
advisory_lock.
Does anyone have any ideas? Am I grossly misusing advisory_locks?
Thanks,
Brett