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

Re: 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: Re: offset and limit in update and subselect
Date: 2001-02-25 21:58:04
Message-ID: 21151.983138284@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> writes:
> Would it then be fine to use update ... limit in the following scenario?
> I have a todo queue:
> create table todo ( task text, pid int default 0);
> The tasks are inserted into the todo table.
> Then the various worker processes do the following update to grab tasks
> without duplication.
> update todo set pid=$mypid where pid=0 limit 1;

There's no LIMIT clause in UPDATE.  You could do something like

	BEGIN
	SELECT taskid FROM todo WHERE pid = 0 FOR UPDATE LIMIT 1;
	UPDATE todo SET pid = $mypid WHERE taskid = $selectedid;
	COMMIT

(assuming taskid is unique; you could use the OID if you have no
application-defined ID).

> What would the performance impact of "order by" be in a LIMIT X case? Would
> it require a full table scan?

Yes, unless there's an index on the order-by item.  The above example
should be fairly efficient if both pid and taskid are indexed.


Hmm ... trying this out just now, I realize that 7.1 effectively does
the LIMIT before the FOR UPDATE, which is not the way 7.0 behaved.
Ugh.  Too late to fix it for 7.1, but I guess FOR UPDATE marking ought
to become a plan node just like LIMIT did.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Hiroshi InoueDate: 2001-02-26 00:17:03
Subject: Re: CommitDelay performance improvement
Previous:From: Bruce MomjianDate: 2001-02-25 21:17:40
Subject: Re: A patch for xlog.c

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