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 597BF56483 for ; Thu, 10 Mar 2005 15:42:27 +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 61250-03 for ; Thu, 10 Mar 2005 15:42:09 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E946B564B4 for ; Thu, 10 Mar 2005 15:42:08 +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 j2AFgCkQ015243; Thu, 10 Mar 2005 10:42:12 -0500 (EST) To: Kris Kiger Cc: "Pgsql-Admin (E-mail)" Subject: Re: Functions and transactions In-reply-to: <42305968.10100@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> Comments: In-reply-to Kris Kiger message dated "Thu, 10 Mar 2005 09:27:52 -0500" Date: Thu, 10 Mar 2005 10:42:12 -0500 Message-ID: <15242.1110469332@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/149 X-Sequence-Number: 16647 Kris Kiger writes: > Hmm.. I was trying simplify my function to get the point across with > minimal confusion. If you don't think there is enough detail, let me > know what is lacking and I will add the appropriate detail. The > function is executed BEFORE insert on table1. 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