X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1D15B565F7 for ; Fri, 11 Mar 2005 00:10:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09741-07 for ; Fri, 11 Mar 2005 00:10:31 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 477E5565CF for ; Fri, 11 Mar 2005 00:10:30 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2B0AUHp027099; Thu, 10 Mar 2005 19:10:30 -0500 (EST) To: Kris Kiger Cc: "Pgsql-Admin (E-mail)" Subject: Re: Functions and transactions In-reply-to: <4230A6AA.6030204@musicrebellion.com> References: <421CF15C.6010306@turtle-entertainment.de> <421CF1CE.3000800@turtle-entertainment.de> <422F6203.1090800@musicrebellion.com> <7701.1110407641@sss.pgh.pa.us> <42305968.10100@musicrebellion.com> <15242.1110469332@sss.pgh.pa.us> <4230A6AA.6030204@musicrebellion.com> Comments: In-reply-to Kris Kiger message dated "Thu, 10 Mar 2005 14:57:30 -0500" Date: Thu, 10 Mar 2005 19:10:29 -0500 Message-ID: <27098.1110499829@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/165 X-Sequence-Number: 16663 Kris Kiger writes: > In your second paragraph, I think that you are saying that SELECT FOR > UPDATE only locks one row, even though the select itself may return > many. Am I mis-interpreting you? No, I'm saying that plpgsql's SELECT INTO operation only reads one row. The fact that the SELECT might have found more rows if allowed to run to completion doesn't enter into it. If the first row read doesn't have active = true then it won't conflict against concurrent UPDATEs, because you are carefully not UPDATEing rows with active = false. It's the combination of those two things that creates the hazard. regards, tom lane