In a queue table we have:
DELETE FROM alert_queue WHERE id = (SELECT MIN(id) FROM alert_queue) RETURNING *
Which allows many processes to get (and process) a single id from the queue table, with no 2 processes getting the same id to be processed.
I would like to do the same kind of thing in a regular table, but with select and update (no delete) - in 1 sql command (no transactions).
Using Postgres 9.0 and Perl DBI,
we have rows of that have a bool column "to_be_processed". I want to get the next row id where "to_be_processed" = 'f' while updating the column to 't' and have many processes running the same query with each getting a separate row (i.e no 2 processes getting the same row).
Update alert_queue set to_be_processed = 't' WHERE id = (SELECT MIN(id) FROM alert_queue where to_be_processed = 'f') RETURNING id