Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1E7D89FB3CA for ; Tue, 24 Oct 2006 13:13:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 84298-07 for ; Tue, 24 Oct 2006 13:13:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by postgresql.org (Postfix) with SMTP id 527909FB3C9 for ; Tue, 24 Oct 2006 13:13:33 -0300 (ADT) Received: (qmail 3908 invoked from network); 24 Oct 2006 16:13:32 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.2?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 24 Oct 2006 16:13:32 -0000 In-Reply-To: <8427.1161699501@sss.pgh.pa.us> References: <453CC859.8070306@albourne.com> <23634.1161613423@sss.pgh.pa.us> <453D42E6.8020102@opencloud.com> <453DC2BB.70803@albourne.com> <8427.1161699501@sss.pgh.pa.us> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <92D359F0-6AEC-4970-87CB-F2859FC3F72C@fastcrypt.com> Cc: Adriaan Joubert , pgsql-jdbc@postgresql.org Content-Transfer-Encoding: 7bit From: Dave Cramer Subject: Re: currval() race condition on server? Date: Tue, 24 Oct 2006 12:13:29 -0400 To: Tom Lane X-Mailer: Apple Mail (2.752.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.99 tagged_above=0 required=5 tests=AWL, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL X-Spam-Level: * X-Archive-Number: 200610/108 X-Sequence-Number: 15991 On 24-Oct-06, at 10:18 AM, Tom Lane wrote: > Adriaan Joubert writes: >> It feels an awful lot like a timing issue where the sequence >> number is retrieved, but there is a delay until currval can use >> it. I'm >> not sure how currval works. Additionally since there is some confusion about how currval actually works you need to be aware that currval is not necessarily the current value of the sequence. It is the last value returned by nextval for that connection. So what's the difference? 1) sequence = 1 2) con1 ->nextval .. sequence=2 currval = 2 for con1 3)con2 -> nextval .. sequence = 3 currval=3 for con2 4) now unless nextval is called again in connection 1 currval will still return 2 Dave > > There is no "timing issue" in currval --- the server is single- > threaded > and it's simply not possible that currval wouldn't be aware of a > previous nextval. > > The theory that sounds best to me is the one someone already mentioned > about your trigger having a code path that doesn't execute nextval. > Another straw to grasp at is connection pooling: are you using it, > if so is it conceivable that the SELECT is being issued on a different > connection than the UPDATE? > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >