Re: currval() race condition on server?

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