Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2AE749F9CF6 for ; Mon, 23 Oct 2006 11:04:32 -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 41673-07 for ; Mon, 23 Oct 2006 11:04:11 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from matrix.gatewaynet.com (host2.dynacom.ondsl.gr [62.103.35.210]) by postgresql.org (Postfix) with ESMTP id 6A4309FA433 for ; Mon, 23 Oct 2006 11:04:10 -0300 (ADT) Received: from host3.dynacom.ondsl.gr ([62.103.35.211] helo=ip216.internal.net) by matrix.gatewaynet.com with esmtp (Exim 4.62) (envelope-from ) id 1Gc0PO-0000SR-Sn for pgsql-jdbc@postgresql.org; Mon, 23 Oct 2006 17:04:06 +0300 From: Achilleas Mantzios Organization: Dynacom Tankers Mgmt To: pgsql-jdbc@postgresql.org Subject: Re: currval() race condition on server? Date: Mon, 23 Oct 2006 17:04:04 +0300 User-Agent: KMail/1.9.3 References: <453CC859.8070306@albourne.com> In-Reply-To: <453CC859.8070306@albourne.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-7" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200610231704.05113.achill@matrix.gatewaynet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.162 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL X-Spam-Level: ** X-Archive-Number: 200610/100 X-Sequence-Number: 15983 =D3=F4=E9=F2 =C4=E5=F5=F4=DD=F1=E1 23 =CF=EA=F4=FE=E2=F1=E9=EF=F2 2006 16:4= 9, =EF/=E7 Adriaan Joubert =DD=E3=F1=E1=F8=E5: > 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. What do you mean here? Inserts it into the row inserted.... > > 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 That normally means that no nextval has been called on the sequence during = the=20 session in question. Can you give us the trigger code? > > 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. > In any case double check that the server version/JDBC version match. > 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? > > Cheers, > > Adriaan > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org =2D-=20 Achilleas Mantzios