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

On Fri, Apr 22, 2016 at 3:56 PM, Kevin Burke <burke(at)shyp(dot)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 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'
> RETURNING id,
> name,
> attempts,
> run_after,
> expires_at,
> status,
> data,
> created_at,
> updated_at
>
>
​Can you alter this to do:

RETURNING ctid, id, etc....

And then show the records that are returned?

No promises it will work....

David J.​