Re: UPDATE grabs multiple rows when it seems like it should only grab one

Kevin Burke <burke(at)shyp(dot)com> writes:
> I'm trying to write a job queue that grabs one job at a time from the
> queue. I expect that the following query should update a maximum of one row
> in the table:

> UPDATE queued_jobs
> SET status='in-progress',
> updated_at=now()
> FROM (
> SELECT id AS inner_id
> FROM queued_jobs
> WHERE status='queued'
> AND name = $1
> AND run_after <= now()
> LIMIT 1
> FOR UPDATE
> ) find_job
> WHERE queued_jobs.id = find_job.inner_id
> AND status='queued'

I think you're assuming that the sub-query will always select the same
row, but it doesn't have to. LIMIT without an ORDER BY is ill-defined.
Another problem is that once the outer UPDATE has changed the status
of whichever row the sub-query selects initially, that row isn't
a candidate to be returned by later subquery runs, so it'd certainly
move on to another row. (I'm assuming here that FOR UPDATE allows
the sub-query to see the effects of the outer update immediately,
which might be wrong; I lack the time to go check right now.)

You might have better luck by putting the sub-query in a CTE, where
it will be executed at most once.

regards, tom lane