Re: offset and limit in update and subselect

Lincoln Yeoh <lyeoh@pop.jaring.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