Re: Strang behaviour SELECT ... LIMIT n FOR UPDATE

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
toobtain 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. +