Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 10ACD9FB3E6 for ; Tue, 24 Oct 2006 07:24:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 47348-05 for ; Tue, 24 Oct 2006 10:24:20 +0000 (UTC) X-Greylist: delayed 00:28:38.859581 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 1E6369FB1D6 for ; Tue, 24 Oct 2006 07:24:19 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from smtp-out4.blueyonder.co.uk (smtp-out4.blueyonder.co.uk [195.188.213.7]) by svr4.postgresql.org (Postfix) with ESMTP id 06D585AF861 for ; Tue, 24 Oct 2006 09:55:39 +0000 (GMT) Received: from [172.23.170.138] (helo=anti-virus01-09) by smtp-out4.blueyonder.co.uk with smtp (Exim 4.52) id 1GcJ0T-0004Ib-UN for pgsql-admin@postgresql.org; Tue, 24 Oct 2006 10:55:37 +0100 Received: from [82.44.23.191] (helo=demolish1) by asmtp-out6.blueyonder.co.uk with smtp (Exim 4.52) id 1GcJ0S-0007gQ-Nl for pgsql-admin@postgresql.org; Tue, 24 Oct 2006 10:55:36 +0100 Message-ID: <001001c6f752$8df17b70$7d64a8c0@demolish1> From: "Donald Fraser" To: "[ADMIN]" References: <453CC859.8070306@albourne.com> <7D3F1FCD-9258-4F8F-841B-219C62E248D5@fastcrypt.com> <453CDEFB.4030504@albourne.com> Subject: Re: [JDBC] currval() race condition on server? Date: Tue, 24 Oct 2006 10:55:36 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1807 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1807 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200610/148 X-Sequence-Number: 23451 Adriaan Joubert wrote >Dave Cramer wrote: >> >> 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. >Yes, and this is done in a C trigger that is called as a result of the >update (it is a BEFORE INSERT OR DELETE OR UPDATE ... trigger). The C >code does a Are you absolutely certain you are not calling currval from within your BEFORE trigger function, before a call to nextval? As you only showed a snippet of your C trigger function, could it be that your code is additionally optimising update statements to not do anything when there is nothing actually updated? Regards Donald Fraser