On 21/07/10 07:27, Brett Hoerner wrote: > 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? You kick off two queries at once. Both have subqueries that grab a set of id,job,arg . There's no exclusion at this stage, so they can easily both land up with some or all of the same results. THEN you filter the result. The filter will drop the result list to empty if it can't acquire the lock. Under what circumstances can it not acquire the lock? If another transaction holds it. The first transaction might have grabbe the data, acquired the lock, done its processing, and committed/rolled back to *release* the lock before the second transaction gets around to checking the lock. In this case, the second transaction will happily acquire the lock. Classic race condition. You should probably use one of the existing queuing mechanisms rather than rolling your own, because building a high-performance, reliable queueing mechanism is surprisingly hard to build. A search of the archives here will turn up several options. I've noticed that PGQ from Skytools gets mentioned a lot. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
