pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adriaan Joubert <a.joubert@albourne.com>
To: pgsql-jdbc@postgresql.org
Subject: Re: currval() race condition on server?
Date: Mon, 23 Oct 2006 18:25:47 +0300
Message-ID: <453CDEFB.4030504@albourne.com> (raw)
In-Reply-To: <7D3F1FCD-9258-4F8F-841B-219C62E248D5@fastcrypt.com>
References: <453CC859.8070306@albourne.com>
	<7D3F1FCD-9258-4F8F-841B-219C62E248D5@fastcrypt.com>

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



view thread (11+ messages)  latest in thread

Message-ID: <453CDEFB.4030504@albourne.com>
Permalink:  ../453CDEFB.4030504@albourne.com/
Also on:    postgresql.org/message-id/453CDEFB.4030504@albourne.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: a.joubert@albourne.com, pgsql-jdbc@postgresql.org
  Subject: Re: currval() race condition on server?
  In-Reply-To: <453CDEFB.4030504@albourne.com>
  References: <453CC859.8070306@albourne.com> <7D3F1FCD-9258-4F8F-841B-219C62E248D5@fastcrypt.com> <453CDEFB.4030504@albourne.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox