While awaiting the awesomeness of the upcoming "skip locked" feature in 9.5 I need to handle a work queue.
Does anyone see any glaring issues or subtle nuances with the basic method below which combines CTEs with queue-handling methods posted by depesz, on the PG wiki and elsewhere.
Note that it appears that there is the slight potential for a race-condition which would cause one worker to occasionally fail to get a record but the application code handles that issue fine.
The work is sent to an externally hosted API which will ultimately reply to a callback API at our end so obviously there's a lot of other stuff in the system to update final results, recover from lost work, add to the queue, etc. I'm just asking about the sanity of the queue processing query itself:
with next_up as (
select
the_id
from
queuetest
where
not sent_for_processing
and pg_try_advisory_xact_lock(12345, the_id)
order by
the_priority
limit 1 for update)
update
queuetest
set
sent_for_processing = true
where
the_id = (select the_id from next_up)
returning
the_work_to_do;
Cheers,
Steve