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

Re: Re: offset and limit in update and subselect

From: Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Re: offset and limit in update and subselect
Date: 2001-02-26 01:26:47
Message-ID: 3.0.5.32.20010226092647.00951a50@192.228.128.13 (view raw or flat)
Thread:
Lists: pgsql-hackers
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.


In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2001-02-26 02:00:41
Subject: [PATCHES] A patch for xlog.c
Previous:From: Tom LaneDate: 2001-02-26 00:55:10
Subject: Re: CommitDelay performance improvement

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