Comment by surprisetalk
3 years ago
I recently published a manifesto and code snippets for exactly this in Postgres!
delete from task
where task_id in
( select task_id
from task
order by random() -- use tablesample for better performance
for update
skip locked
limit 1
)
returning task_id, task_type, params::jsonb as params
Presumably it's okay that this loses work if your task runner has an error?
If you read my guide, you’ll see that I embed it in a transaction that doesn’t COMMIT until the companion code is complete :)
For example, I run the above query to grab a queued email, send it using mailgun, then COMMIT. Nothing is changed in the DB unless the email is sent.
Holding a transaction open for the duration of a request to an external service makes me nervous. I've seen similar code lock up the database and bring down production. Are you using timeouts and circuit breakers to control the length of the transactions?
1 reply →
Long running transactions can lead to an accumulation of dead tuples: https://brandur.org/postgres-queues
3 replies →
Gotcha, apologies for responding without reading!
From the linked article
> The task row will not be deleted if sendEmail fails. The PG transaction will be rolled back. The row and sendEmail will be retried.