Re: UPDATE grabs multiple rows when it seems like it should only grab one - Mailing list pgsql-bugs

On Fri, Apr 22, 2016 at 3:56 PM, Kevin Burke <burke@shyp.com> wrote:

> Hi,
> 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 r=
ow
> 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'
> RETURNING id,
>         name,
>         attempts,
>         run_after,
>         expires_at,
>         status,
>         data,
>         created_at,
>         updated_at
>
>
=E2=80=8BCan you alter this to do:

RETURNING ctid, id, etc....

And then show the records that are returned?

No promises it will work....

David J.=E2=80=8B