Re: can we add SKIP LOCKED to UPDATE?

On Mon, Nov 9, 2015 at 9:06 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> =?GBK?B?tcK45w==?= <digoal(at)126(dot)com> writes:
>> PostgreSQL 9.5 added skip locked to select for update to improve concurrency performance, but why not add it to update sql?
>
> Seems like you'd have unpredictable results from the update then.

But with use of RETURNING, you could at least know what those results
were and so could deal with the unpredictability.

I don't understand Digoal's use case (Google Translate does a poor job
on the linked page), but this would be handy in conjunction with LIMIT
(which also doesn't exist for UPDATE right now).

update work_queue set assigned='Jeff' where assigned is null and
skills_needed <@ '{whining,"breaking things"}' limit 1 skip locked
returning id, description

In 9.5 you will be able to do it with a subselect, but putting them
directly on the UPDATE would be easier to understand, and perhaps more
efficient to execute.

Cheers,

Jeff