Re: [JDBC] currval() race condition on server? - Mailing list pgsql-admin

From Donald Fraser
Subject Re: [JDBC] currval() race condition on server?
Date
Msg-id 001001c6f752$8df17b70$7d64a8c0@demolish1
Whole thread Raw
List pgsql-admin
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


pgsql-admin by date:

Previous
From: "Mr. Dan"
Date:
Subject: could not fork new process for connection: Cannot allocate memory
Next
From: "rajesh boppana"
Date:
Subject: materialized view