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 57766565AC for ; Thu, 10 Mar 2005 19:57:39 +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 41349-04 for ; Thu, 10 Mar 2005 19:57:30 +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 6FAFF5659F for ; Thu, 10 Mar 2005 19:57:29 +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 1D9Tmh-0005Pm-Nq for ; Thu, 10 Mar 2005 14:57:27 -0500 Message-ID: <4230A6AA.6030204@musicrebellion.com> Date: Thu, 10 Mar 2005 14:57:30 -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> In-Reply-To: <15242.1110469332@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=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/162 X-Sequence-Number: 16660 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? Also, what do you mean by seizing on a non-active row? Your assumption about pkey_id is right, I meant for that to mean partial key, bad naming on my part ;-). In my case, the third paragraph applies to this situation, because I can assume that there will always be an entry in the table that will be active with that pkey_id (lets call this partialKey_id from now on, to avoid further confusion). The alternative you offer is a good idea, I didn't realize that I had the option to create a unique index on a subset of data within the table. Unfortunately, it will not work in this situation. I don't have the option to report failure to the front-end application. I suppose, i'm looking for a method to only allow one invocation of this function, per partialKey_id, at a time. If you have any other alternatives or suggestions, I'm all ears, err eyes... Anyway, thank you ;-) Kris Tom Lane wrote: >Mmm. This might work as you expect in 8.0, but it surely won't in any >prior release, because before 8.0 we didn't advance the transaction >snapshot between statements of a function. > >Another issue is that your SELECT FOR UPDATE locks only one of the >rows having the target pkey_id (I assume that column is misnamed and >isn't actually a primary key?). If it happened to seize on a non-active >row then it might not accomplish your goal of blocking until other >updaters of the same row set commit. That would allow the UPDATE to >start and set its snapshot, which would mean you lose because it >wouldn't see the newly inserted row from the other transaction. > >Even more to the point, if there aren't yet any committed rows at all of >the target pkey_id, there is nothing for the SELECT FOR UPDATE to block >on at all. You could fix the first problem in various ways but I see no >way around this one. Unless you can guarantee that there will always be >a suitable row already in existence, I think you have to abandon the >idea of using a SELECT FOR UPDATE for locking. > >One possibility is to create a unique partial index: > >CREATE UNIQUE INDEX foo ON table1(pkey_id) WHERE active ; > >This will provide an enforcement that you don't have more than one >active row at a time. Now you just simplify the trigger to > update table1 set active = false where NEW.pkey_id = pkey_id and active; > NEW.active := true; >Race conditions will end up causing unique-key errors, which you can just >retry. > > regards, tom lane > >