Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 8C05C9FB41A for ; Mon, 23 Oct 2006 11:34:57 -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 68819-06 for ; Mon, 23 Oct 2006 11:34:40 -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 78CD79FB412 for ; Mon, 23 Oct 2006 11:34:39 -0300 (ADT) Received: (qmail 13145 invoked from network); 23 Oct 2006 14:34:38 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.2?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 23 Oct 2006 14:34:38 -0000 In-Reply-To: <453CC859.8070306@albourne.com> References: <453CC859.8070306@albourne.com> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <7D3F1FCD-9258-4F8F-841B-219C62E248D5@fastcrypt.com> Cc: pgsql-jdbc@postgresql.org Content-Transfer-Encoding: 7bit From: Dave Cramer Subject: Re: currval() race condition on server? Date: Mon, 23 Oct 2006 10:34:36 -0400 To: Adriaan Joubert X-Mailer: Apple Mail (2.752.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.97 tagged_above=0 required=5 tests=AWL, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL X-Spam-Level: * X-Archive-Number: 200610/102 X-Sequence-Number: 15985 On 23-Oct-06, at 9:49 AM, Adriaan Joubert wrote: > Hi, > > I've run into an intermittent problem with our code recently. We > have the following set-up: > > table A : some data table > table B : a history table for table A > > A trigger copies the old version of a row into table B whenever an > update is done on table A. Both A and B contain an audit number, > and the trigger obtains an audit number from a sequence and inserts > it into the row inserted into table A. > > For some bookkeeping purposes I need the new audit number back from > the update, so I submit a prepared statement through jdbc of the form > > UPDATE A SET ....; SELECT currval('ip_audit_seq'); > > On the first call I get > > ERROR: currval of sequence "ip_audit_seq" is not yet defined in > this session currval is only defined after you call nextval in that connection. > > Note that this works without any trouble if I issue the commands in > a transaction through psql, and this used to work in earlier > versions of postgres. > > We have a lot of users on very high latency links, so it is > important for the responsiveness of the application to try to do > the update and select in a single database trip. > > So I'm wondering whether there is some type of race condition, > where the selection of the new value from ip_audit_seq is not > available to the currval function straight away? Anybody got any > ideas on this? There is no race condition > > Cheers, > > Adriaan > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >