From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig James <craig_james@emolecules.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: select for update
Date: Fri, 22 Apr 2011 23:17:48 -0400
Message-ID: <1161.1303528668@sss.pgh.pa.us> (raw)
In-Reply-To: <4DB20AEC.90400@emolecules.com>
References: <4DB1BFC2.7020806@emolecules.com>
<10313.1303505924@sss.pgh.pa.us>
<4DB20AEC.90400@emolecules.com>
Craig James <craig_james@emolecules.com> writes:
> On 4/22/11 1:58 PM, Tom Lane wrote:
>> Craig James<craig_james@emolecules.com> writes:
>>> select objectid from archive where db_id is null limit 1 for update
>> The interaction between LIMIT and FOR UPDATE changed in 9.0 ... what
>> PG version are you using?
> 8.4.4
Well, note what it says in the 8.4 SELECT reference page:
Caution
It is possible for a SELECT command using both LIMIT and FOR
UPDATE/SHARE clauses to return fewer rows than specified by
LIMIT. This is because LIMIT is applied first. The command
selects the specified number of rows, but might then block
trying to obtain a lock on one or more of them. Once the SELECT
unblocks, the row might have been deleted or updated so that it
does not meet the query WHERE condition anymore, in which case
it will not be returned.
I think what's probably happening to you is you're getting a NULL not
because there isn't a matching row, but because somebody is updating the
first matching row underneath you and then the LIMIT prevents finding
any other matches. However, that pseudo-code is too pseudo to tell
whether this theory is correct.
(9.0 handles these situations in a less unintuitive fashion, btw.)
regards, tom lane
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, craig_james@emolecules.com
Subject: Re: select for update
In-Reply-To: <1161.1303528668@sss.pgh.pa.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