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

Kevin Burke <burke@shyp.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