Re: [GENERAL] Sequences

At 1:15 +0200 on 21/10/98, Anand Surelia wrote:

> I am using a sequence to automatically generate a transaction number in
> my database. I've set up the transaction number to have a default value
> from the sequence, so that I don't have to provide a number while
> entering. However, I want to know the number of the transaction I've
> just entered. I can do that by calling curval(sequence), but in a
> dynamic situation with lot of transactions being recorded by multiple
> users, how can I be sure I am getting the value which I had entered.
> It sounds silly, but I am sure there is a simple solution for it. Do I
> have to lock the sequence while I am using so that no one else can use
> it, or do I fire both the queries in the same TRANSACTION block?
> Thanks for any help,

The following is from the "create_sequence" manpage:

After sequence created, You may use function nextval with
sequence name as argument to get new number from sequence
specified. Function currval ('sequence_name') may be used
to determine number returned by last call to nextval for
specified sequence in current session.

When they say "current session", they mean "the connection we are working
with in the current process".

That is, if your client uses a connection, and this connection has used
nextval (within the default clause, invoked by an insert), the call to
currval will *always* relate to that specific nextval. Even if three
hundred other processes have inserted lines and received their own nextval,
they will each have a different value to currval - the value they, and only
they, have created.

So, unless you have a connection shared between multiple threads of the
same process or something on the client side, currval is the right thing to
use, with no fear.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma