Is "WITH () UPDATE" Thread Safe ?

From: Paul GOERGLER <pgoergler(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Is "WITH () UPDATE" Thread Safe ?
Date: 2014-12-01 08:48:09
Message-ID: etPan.547c2b49.4353d0cd.6385@paul.egallys.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message M Tarkeshwar Rao 2014-12-01 09:08:31 What is default password for user postgres
Previous Message Powrie, William 2014-12-01 00:34:14 Ever increasing pg_clog disk usage v8.4