Tom Lane wrote: > "Daniel Caune" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly