update and select to create a queue

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

Quasi SQL:
Update  alert_queue set to_be_processed = 't'  WHERE id = (SELECT MIN(id) FROM alert_queue where to_be_processed = 'f') RETURNING id


Thanks.
freshgrillAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MrNedConnect With a Mentor Commented:
I can't think of any way to do it with a single query. Relevant discussion with workable solutions here: http://stackoverflow.com/questions/389541/select-unlocked-row-in-postgresql

Basically:
1. Select an unlocked row and lock it
2. Process/Delete it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.