Steve Crawford wrote:
> Anyone have any ideas on how to handle a work queue? I've been
> thinking
> about optimizing this process for quite a while.
I use a variant of The Tom Lane Solution previously pointed to, your
Plan 1 is very similar.
> This does not produce desirable results. In the case where requests
> for
> work overlap, the first query will complete. The second query will
> block
> until the first completes and then apparently re-evaluate the
> condition
> and toss the record thus returning zero-rows.
I have no experience with this, but I think you can do SELECT FOR
UPDATE NOWAIT to avoid the blocking.
> Plan 1a:
>
> Check for tuples returned and re-run query if zero. This will go
> into an
> infinite loop whenever there is nothing in the queue and cause
> undesirable thrashing if there is too much contention.
So either sleep a bit, as in Tom's solution, or use NOTIFY/LISTEN,
which is what I do. I have a trigger like this on my queue:
create or replace function notify_new_work() returns trigger as
'
BEGIN
NOTIFY WORK;
RETURN NULL;
END;
' language 'plpgsql';
create trigger notify_new_work
after insert on work_queue
for each statement execute procedure notify_new_work();
My workers do LISTEN WORK after connecting, and then do a (UNIX)
select on the connection socket when they get zero results from the
(SQL) select. This puts them to sleep until the next NOTIFY fires.
How to get the socket and do the (UNIX) select will depend on your
client library and language.
- John Burger
MITRE