Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 89ECD1337BBB for ; Sat, 23 Apr 2011 00:17:58 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 92708-02 for ; Sat, 23 Apr 2011 03:17:50 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id BD3FB1337999 for ; Sat, 23 Apr 2011 00:17:49 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p3N3HmKB001162; Fri, 22 Apr 2011 23:17:48 -0400 (EDT) To: Craig James cc: "pgsql-admin@postgresql.org" Subject: Re: select for update In-reply-to: <4DB20AEC.90400@emolecules.com> References: <4DB1BFC2.7020806@emolecules.com> <10313.1303505924@sss.pgh.pa.us> <4DB20AEC.90400@emolecules.com> Comments: In-reply-to Craig James message dated "Fri, 22 Apr 2011 16:10:36 -0700" Date: Fri, 22 Apr 2011 23:17:48 -0400 Message-ID: <1161.1303528668@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201104/216 X-Sequence-Number: 37397 Craig James writes: > On 4/22/11 1:58 PM, Tom Lane wrote: >> Craig James 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