Is "WITH () UPDATE" Thread Safe ?

[Paul GOERGLER <pgoergler@xxxxxxxxx>]

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 ?

-- 
Paul