Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group