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
