Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 45F4A9FB415 for ; Mon, 23 Oct 2006 10:49:47 -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 82029-08 for ; Mon, 23 Oct 2006 13:49:36 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id C193F9FB40F for ; Mon, 23 Oct 2006 10:49:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from nguni.albourne.com (nguni.albourne.com [213.207.142.98]) by svr4.postgresql.org (Postfix) with ESMTP id 60CC05AF8AF for ; Mon, 23 Oct 2006 13:49:35 +0000 (GMT) Received: from [192.168.51.186] ([192.168.51.186]) by nguni.albourne.com (8.13.3/8.13.3/Albourne/FW/DNSBL) with ESMTP id k9NDn8vk016356 for ; Mon, 23 Oct 2006 16:49:14 +0300 (EEST) Message-ID: <453CC859.8070306@albourne.com> Date: Mon, 23 Oct 2006 16:49:13 +0300 From: Adriaan Joubert User-Agent: Thunderbird 1.5.0.7 (X11/20060922) MIME-Version: 1.0 To: pgsql-jdbc@postgresql.org Subject: currval() race condition on server? Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.384 tagged_above=0 required=5 tests=SPF_SOFTFAIL X-Spam-Level: * X-Archive-Number: 200610/99 X-Sequence-Number: 15982 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 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? Cheers, Adriaan