Re: Re: offset and limit in update and subselect

At 04:58 PM 25-02-2001 -0500, Tom Lane wrote:
>
>There's no LIMIT clause in UPDATE. You could do something like

Oh. I thought 7.1 had that.

> 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).

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);

This is pushing it, but I'm curious on what would happen :).

I'll stick to doing it in two queries, and leave out the "order by random"-
faster select vs low blocking.

Cheerio,
Link.