For the first time, I find myself wanting to use some of PG's
concurrency control stuff, and I could use some advice.
I have requests showing up in a database, and I have one or more
servers picking these up with listen/notice. The requests go into a
table with a status column, which is initially NULL, so a server
finds requests to process like so:
begin;
select id from requests where status is null order by ts limit 1;
(ts is a timestamp column - process the earliest request first.)
A server takes responsibility for a request by setting the status for
the id it got with the previous query:
update requests set status = 'start' where qid = 42 and status is
null;
commit;
My client library (Python pgdb) lets me know how many rows got
updated, in this case 1, if the server set the status, or 0 if that
request's status is no longer NULL, like if some other server picked
it up in the interim.
Now I know that this last bit is not really any protection against
two servers both thinking they should process the same request. I
suspect I want to use SELECT FOR UPDATE in the first query - will
that be sufficient in this situation?
Thanks for any advice.
- John D. Burger
MITRE