.tip

Claiming a job with UPDATE ... SET status = 'running' and committing frees the connection, but then nothing holds the row. A worker that is killed mid job leaves it running forever and nobody picks it up. Stamp the claim with a time and let a reaper take the expired ones back.

UPDATE jobs SET status = 'pending', attempts = attempts + 1
WHERE status = 'running' AND claimed_at < now() - interval '5 minutes';

Make the lease longer than the slowest honest run, or you hand a live job to a second worker. Anything reclaimable runs twice, so it has to be idempotent.