Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock

Поиск
Список
Период
Сортировка
От Brett Hoerner
Тема Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock
Дата
Msg-id AANLkTil6prYH5HmaFA7v5VuG8nJyXduRHRt-UB1ZBFq-@mail.gmail.com
обсуждение исходный текст
Ответы Re: Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock  (Craig Ringer <craig@postnewspapers.com.au>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: printing vector column seems to print the rest of the row too
Следующее
От: Craig Ringer
Дата:
Сообщение: "parameterized views" or return-type-inferring SQL functions?