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 427ED5658F for ; Fri, 11 Mar 2005 16:49:00 +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 95204-08 for ; Fri, 11 Mar 2005 16:48:50 +0000 (GMT) Received: from titan.internal (dsl093-203-229.ind1.dsl.speakeasy.net [66.93.203.229]) by svr1.postgresql.org (Postfix) with ESMTP id 6986E566F7 for ; Fri, 11 Mar 2005 16:48:47 +0000 (GMT) Received: from [10.0.1.102] (helo=[10.0.1.102] ident=kris) by titan.internal with asmtp (Exim 4.34 #1 (Debian)) id 1D9nJc-0007uS-Dg for ; Fri, 11 Mar 2005 11:48:44 -0500 Message-ID: <4231CBEE.60109@musicrebellion.com> Date: Fri, 11 Mar 2005 11:48:46 -0500 From: Kris Kiger User-Agent: Mozilla Thunderbird 0.9 (X11/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Pgsql-Admin (E-mail)" Subject: Re: Functions and transactions 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> <27098.1110499829@sss.pgh.pa.us> In-Reply-To: <27098.1110499829@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/184 X-Sequence-Number: 16682 Interesting. That makes sense, though. So, is there a good way to lock a set of rows using SELECT FOR UPDATE in plpgsql? I assume using PERFORM would yield the same problem, because it immediately discards the results. Thanks! Kris Tom Lane wrote: >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 > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >