pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Donald Fraser <postgres@kiwi-fraser.net>
To: [ADMIN] <pgsql-admin@postgresql.org>
Subject: Re: [JDBC] currval() race condition on server?
Date: Tue, 24 Oct 2006 10:55:36 +0100
Message-ID: <001001c6f752$8df17b70$7d64a8c0@demolish1> (raw)
References: <453CC859.8070306@albourne.com>
	<7D3F1FCD-9258-4F8F-841B-219C62E248D5@fastcrypt.com>
	<453CDEFB.4030504@albourne.com>

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




view thread (11+ messages)  latest in thread

Message-ID: <001001c6f752$8df17b70$7d64a8c0@demolish1>
Permalink:  ../001001c6f752$8df17b70$7d64a8c0@demolish1/
Also on:    postgresql.org/message-id/001001c6f752$8df17b70$7d64a8c0@demolish1

 · 

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: postgres@kiwi-fraser.net
  Subject: Re: [JDBC] currval() race condition on server?
  In-Reply-To: <001001c6f752$8df17b70$7d64a8c0@demolish1>
  References: <453CC859.8070306@albourne.com> <7D3F1FCD-9258-4F8F-841B-219C62E248D5@fastcrypt.com> <453CDEFB.4030504@albourne.com> <001001c6f752$8df17b70$7d64a8c0@demolish1>

* 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