Re: offset and limit in update and subselect

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: offset and limit in update and subselect
Date: 2001-02-26 04:16:01
Message-ID: 22296.983160961@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 2001-02-26 04:19:33 Re: pgaccess Japanese input capability patch
Previous Message Vince Vielhaber 2001-02-26 03:33:45 Re: Monitor status