Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id DDD079FB41E for ; Mon, 23 Oct 2006 12:26:24 -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 87446-10 for ; Mon, 23 Oct 2006 12:25:56 -0300 (ADT) X-Greylist: delayed 01:36:32.729431 by SQLgrey- Received: from nguni.albourne.com (nguni.albourne.com [213.207.142.98]) by postgresql.org (Postfix) with ESMTP id 58B099FB41A for ; Mon, 23 Oct 2006 12:25:51 -0300 (ADT) 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 k9NFPgqS010884 for ; Mon, 23 Oct 2006 18:25:44 +0300 (EEST) Message-ID: <453CDEFB.4030504@albourne.com> Date: Mon, 23 Oct 2006 18:25:47 +0300 From: Adriaan Joubert User-Agent: Thunderbird 1.5.0.7 (X11/20060922) MIME-Version: 1.0 To: pgsql-jdbc@postgresql.org Subject: Re: currval() race condition on server? References: <453CC859.8070306@albourne.com> <7D3F1FCD-9258-4F8F-841B-219C62E248D5@fastcrypt.com> In-Reply-To: <7D3F1FCD-9258-4F8F-841B-219C62E248D5@fastcrypt.com> 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=3.816 tagged_above=0 required=5 tests=SPF_HELO_SOFTFAIL, SPF_SOFTFAIL X-Spam-Level: *** X-Archive-Number: 200610/103 X-Sequence-Number: 15986 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 /* Find a plan for getting the next sequence number */ plan = find_plan(sequence_name, &ExecPlans, &nExecPlans); if (plan->splan == NULL) { sprintf(query, "SELECT nextval('%s')", sequence_name); /* Prepare plan for query */ pplan = SPI_prepare(query, 0, NULL); if (pplan == NULL) elog(ERROR, "audit(%s): SPI_prepare returned %d", sequence_name, SPI_result); pplan = SPI_saveplan(pplan); if (pplan == NULL) elog(ERROR, "audit(%s): SPI_saveplan returned %d", sequence_name, SPI_result); plan->splan = pplan; } /* Execute the plan */ ret = SPI_execp(plan->splan, NULL, NULL, 0); if (ret < 0) elog(ERROR, "audit(%s): SPI_execp returned %d", sequence_name, ret); /* Get the new sequence number */ new_seq = (int) DatumGetInt64 ( SPI_getbinval( SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull) ); The update part of the trigger then inserts the sequence number into the row (new_tuple being the tuple that is passed into the trigger) newtuple = SPI_modifytuple(rel, newtuple, 1, &i_audit, (Datum *) &new_seq, NULL); which puts the sequence number into the relation. As this is in a BEFORE trigger, I would have through that it should be available to currval before executing the second statement in the query. As to Tom's question: the jdbc driver is executing the update without any problems. On the java side I can do an PreparedStatement st = connection.prepareStatement(...); ... st.execute(); nUpdated = st.getUpdateCount(); if (nUpdated == 1 && st.getMoreResults()) { ResultSet rs = st.getResultSet(); if (rs.next()) { oldAudit_ = audit_; setAudit(rs.getInt(1)); } } st.close(); which works well. So the real question is why currval is not working even though the update succeeds? Or rather - the update does not succeed, as the transaction is rolled back, but if I try to do the same thing a second time it does. Perhaps the assumption that the update has succeeded is incorrect - but then, if the update statement fails, it should never attempt to execute the SELECT currval(), should it? Certainly in updates that fail we do not get an error from the SELECT currval(). Thanks for all your responses! Adriaan