Hello,
I have a lot of tickets, i need to take a batch of tickets and process them.
So the process is :
SELECT ONLY 100 tickets
PROCESS ticket
MARK THEM AS « done »
I’m selecting the tickets with :
WITH t0 AS (
SELECT t.id,
RANDOM() AS rank,
EXTRACT(EPOCH FROM NOW() - b.created_at) as elapsed
FROM tickets AS t
LEFT JOIN batch as b ON b.id = t.batch_id
WHERE (
t.status = 'waiting' OR
(t.status = 'processing' AND t.locked_until IS NOT NULL AND t.locked_until <= NOW())
) AND t.send_at < NOW()
AND (t.send_before IS NULL OR t.send_before > NOW())
ORDER BY
t.priority DESC,
rank ASC
LIMIT 100
FOR UPDATE OF t
)
UPDATE tickets AS t1
SET status = 'processing',
locked_until = NOW() + '1 HOUR’,
extra = t1.extra || hstore('elapsed', t0.elapsed || '') || hstore('rank', rank || '')
FROM t0
WHERE t1.id = t0.id
RETURNING t1.*;
I wonder if this query is thread safe, Can a ticket be updated between the SELECT part (t0) and the UPDATE part ?
If this query is not « thread safe » how can i do this ?
