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

On Fri, Apr 22, 2016 at 4:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 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=3D'in-progress',
> >         updated_at=3Dnow()
> > FROM (
> >         SELECT id AS inner_id
> >         FROM queued_jobs
> >         WHERE status=3D'queued'
> >                 AND name =3D $1
> >                 AND run_after <=3D now()
> >         LIMIT 1
> >         FOR UPDATE
> > ) find_job
> > WHERE queued_jobs.id =3D find_job.inner_id
> >         AND status=3D'queued'
>
> I think you're assuming that the sub-query will always select the same
> row, but it doesn't have to.


=E2=80=8BActually, I assumed that the uncorrelated subquery would only be r=
un a
single time...=E2=80=8B

=E2=80=8BThe documentation on update, to me, seems to support this interpre=
tation.

"""
When using FROM you should ensure that the join produces at most one output
row for each row to be modified. In other words, a target row shouldn't
join to more than one row from the other table(s)
"""=E2=80=8B

=E2=80=8BThe understanding of JOIN that I hold is to take two complete rela=
tions
and combine them on some predicate.  The from relation here, when complete,
only has one row and given it is effectively a self-join on the PK the
result of the join is guaranteed to be a single row.  I do not follow how
the sub-select is allowed to be evaluated multiple times.=E2=80=8B

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

=E2=80=8BSince I presume that is the desired semantics here=E2=80=8B

=E2=80=8Bthat seems like this is the best proper solution.  Though now I'm =
curious
what people did before CTEs were available...this problem isn't new.

David J.
=E2=80=8B