From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Hutton, Rob <HuttonR@plymart.com>
To: steinbeck@ice.mpg.de
Cc: pgsql-sql@postgreSQL.org
Subject: RE: [SQL] Re: [INTERFACES] JDBC and getting just assigned serial number
Date: Wed, 22 Sep 1999 18:51:01 +0200
Message-ID: <l03130305b40eb6d94ff1@[147.233.159.109]> (raw)
In-Reply-To: <11EFC736FB68D111B9DD00805FAD7C6D2DACBC@plymartpdc.internal.plymart.com>
At 18:21 +0200 on 22/09/1999, Hutton, Rob wrote:
> Except in an active database, or possibly not so active, where another
> record is inserted between the time yours is committed and you do the
> currval. It is much safer to do a select on nextval to get the value, the
> put it in as part of your update. It eliminates the chance that you will
> get the wrong record, and there is minimal overhead.
NOT TRUE!
Sorry to shout, but currval is the *safe* way to do this. It absolutely
guarantees you get the correct number, it was designed exactly for this
purpose. Using nextval and putting the value in the table will work, but it
requires two operations for the insert, thus potentially wasting more
numbers (someone else locks the row between the nextval and the insert).
Currval works more or less like this: You have some part of memory for the
session between you and the backend. When it draws a nextval for you, it
automatically puts the number it has drawn in this session memory. The
currval operation gets this stored number. It DOES NOT get the last value
from the sequence table. That's a common misconception.
To make myself clear, let's suppose process A and process B run the same
program, where the table has two fields, one serial named "ser", the other
a data field, named "dat".
INSERT INTO the_table( dat ) VALUES ('value');
SELECT currval( 'the_table_ser_seq' );
This has an implicit nextval( 'the_table_ser_seq' ) within the INSERT.
Process Does Memory state
A initially empty
B initially empty
A INSERT 'the_table_ser_seq' = 28
B INSERT 'the_table_ser_seq' = 29
A currval 'the_table_ser_seq' = 28
--- returns 28
B currval 'the_table_ser_seq' = 29
--- returns 29
A (again) INSERT 'the_table_ser_seq' = 30
if B asks for currval, it still has 29. It will answer 29. You see?
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
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-sql@postgresql.org
Cc: herouth@oumail.openu.ac.il, HuttonR@plymart.com, steinbeck@ice.mpg.de
Subject: Re: [SQL] Re: [INTERFACES] JDBC and getting just assigned serial number
In-Reply-To: <l03130305b40eb6d94ff1@[147.233.159.109]>
References: <11EFC736FB68D111B9DD00805FAD7C6D2DACBC@plymartpdc.internal.plymart.com> <l03130305b40eb6d94ff1@[147.233.159.109]>
* 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