Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 4CE302E33C1
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 28 Nov 2007 00:50:54 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 36738-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 28 Nov 2007 00:50:50 -0400 (AST)
Received: from momjian.us (momjian.us [70.90.9.53])
	by postgresql.org (Postfix) with ESMTP id 603A32E33C5
	for <pgsql-sql@postgresql.org>; Wed, 28 Nov 2007 00:50:50 -0400 (AST)
Received: (from bruce@localhost) by momjian.us (8.11.6/8.11.6) id
 lAS4opo18455;
	Tue, 27 Nov 2007 23:50:51 -0500 (EST)
From: Bruce Momjian <bruce@momjian.us>
Message-Id: <200711280450.lAS4opo18455@momjian.us>
Subject: Re: Strang behaviour SELECT ... LIMIT n FOR UPDATE
In-Reply-To: <18953.1196225189@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Tue, 27 Nov 2007 23:50:51 -0500 (EST)
CC: Daniel Caune <daniel.caune@ubisoft.com>, pgsql-sql@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL124 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/141
X-Sequence-Number: 29669

Tom Lane wrote:
> "Daniel Caune" <daniel.caune@ubisoft.com> writes:
> > I'm facing a strange behaviour with a statement SELECT ... LIMIT n FOR
> > UPDATE in PostgreSQL 8.1.  The number of rows returned is actually (n -
> > 1).  I'm trying to find whether this is an identified issue with
> > PostgreSQL 8.1 that might have been fixed in a later version such as
> > 8.2; I don't have any problem in moving to a later version if needed.
> 
> There's no known issue specifically of that form (and a quick test of
> 8.1 doesn't reproduce any such behavior).  However, it is known and
> documented that LIMIT and FOR UPDATE behave rather oddly together:
> the LIMIT is applied first, which means that if FOR UPDATE rejects
> any rows as being no longer up-to-date, you get fewer than the expected
> number of rows out.  You did not mention any concurrent activity in
> your example, but I'm betting there was some ...

Current documentation explains why in the SELECT manual page:

    It is possible for a <command>SELECT</> command using both
    <literal>LIMIT</literal> and  <literal>FOR UPDATE/SHARE</literal>
    clauses to return fewer rows than specified by
    <literal>LIMIT</literal>.  This is because <literal>LIMIT</> is applied
    first.  The command selects the specified number of rows, but might
    then block trying to obtain lock on one or more of them.  Once the
    <literal>SELECT</> unblocks, the row might have been deleted or updated
    so that it does not meet the query <literal>WHERE</> condition anymore,
    in which case it will not be returned.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +