Re: offset and limit in update and subselect

Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> writes:
>> BEGIN
>> SELECT taskid FROM todo WHERE pid = 0 FOR UPDATE LIMIT 1;
>> UPDATE todo SET pid = $mypid WHERE taskid = $selectedid;
>> COMMIT

> This is very similar to what I'm testing out in 7.0.3 - except I'm
> currently trying "order by random" to prevent blocking. This is because
> all worker processes will tend to select stuff in the same order (in the
> absence of inserts or updates on that table), and thus they will hit the
> same first row (this is what I encountered last week - and I got the wrong
> impression that all rows were locked).

Right. Only the first row is locked, but that doesn't help any. "order
by random" sounds like it might be a good answer, if there aren't many
rows that need to be sorted.

> What would happen if I rewrite that query to:

> update todo set pid = $mypid where exists ( select task id from todo where
> pid = 0 for update limit 1);

Right now you get

ERROR: SELECT FOR UPDATE is not allowed in subselects

This is something that could be fixed if FOR UPDATE were a plan node
instead of a function done at the executor top level.

regards, tom lane