Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id DFFBD475CBB
	for <pgsql-sql@postgresql.org>; Tue,  3 Sep 2002 14:39:55 -0400 (EDT)
Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251])
	by postgresql.org (Postfix) with ESMTP id 4F5DA476003
	for <pgsql-sql@postgresql.org>; Tue,  3 Sep 2002 14:39:28 -0400 (EDT)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.10.1) id g83Id9o07705;
	Tue, 3 Sep 2002 14:39:09 -0400 (EDT)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200209031839.g83Id9o07705@candle.pha.pa.us>
Subject: Re: UPDATE & LIMIT together?
In-Reply-To: <20020903083727.GH5674@emaze.net>
To: tp@emaze.net
Date: Tue, 3 Sep 2002 14:39:09 -0400 (EDT)
Cc: pgsql-sql@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL99 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/16
X-Sequence-Number: 9293

tp wrote:
> Hmm,,
> 
> thanks so far, it helped.
> 
> The query is horrible slow on full tables (>100.000 rows).
> Is there any other solution? I just want to have the 'next 10 entries'
> WHERE state=10 and update state=20.
> (so that on the next request i or another process only gets the 
> new entires in queue).
> 
> 
> My query now looks like:
> UPDATE queue SET state=20 WHERE (id) IN (SELECT id FROM queue LIMIT 10)

Without an ORDER BY, I am not sure what that LIMIT is returning.

I don't know of a faster way.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073