From: Bruce Momjian <bruce@momjian.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Daniel Caune <daniel.caune@ubisoft.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Strang behaviour SELECT ... LIMIT n FOR UPDATE
Date: Tue, 27 Nov 2007 23:50:51 -0500 (EST)
Message-ID: <200711280450.lAS4opo18455@momjian.us> (raw)
In-Reply-To: <18953.1196225189@sss.pgh.pa.us>
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. +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruce@momjian.us, tgl@sss.pgh.pa.us, daniel.caune@ubisoft.com
Subject: Re: Strang behaviour SELECT ... LIMIT n FOR UPDATE
In-Reply-To: <200711280450.lAS4opo18455@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox