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
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 |