Thread: RE: [SQL] Re: [INTERFACES] JDBC and getting just assigned serial number

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.

-----Original Message-----
From: Herouth Maoz [mailto:herouth@oumail.openu.ac.il]
Sent: Wednesday, September 22, 1999 11:17 AM
To: steinbeck@ice.mpg.de; pgsql-interfaces@postgreSQL.org
Cc: pgsql-sql@postgreSQL.org
Subject: [SQL] Re: [INTERFACES] JDBC and getting just assigned serial
number


At 21:19 +0200 on 13/09/1999, Christoph Steinbeck wrote:


> I'm thrilled to see that there is some kind of discussion running in the
> C area on the exact equivalent of my very recent JDBC problem.
> I insert a row into a table where one field is a SERIAL. This SERIAL, of
> course, is automatically assigned by PostgreSQL. How do I get this
> number immeadiately after inserting the row? When I issue the command
> with psql I get back some kind of number which presumably is the OID.
> Getting just this one would be fine too because I need it for
> referencing.

I have answered this question several times already on the SQL list. It has
nothing to do with Java or any other interface you are using (except,
maybe, the question about how to get the OID).

Let's make it short.

1) Don't use OIDs for reference. It will only make life harder.

2) If you want to use the OID just to retrieve that last row, make sure  that the OID column is indexed, otherwise you
arein for sequential  searching. See, I told you not to use it...
 

3) As for the serial field. "serial" is a shorthand notation for  creating an PostgreSQL sequence, and taking the next
numberfrom  that sequence. You can see the name of the sequence that was  created with a quick \ds in psql.
 
  Now, after inserting a row, use SELECT currval('sequence_name');  The number returned is the number assigned to the
serialnumber in  the last INSERT statement of the current session.
 

Herouth

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



************
_____________________________________________________________
Email addresses, faxes, and any other means of electronic contact are for
the business use of Ply Marts, Inc., its employees, suppliers, and any other
party interested in the business of Ply Marts, Inc.  Individuals using
computer systems owned or operated by Ply Marts, Inc. do so subject to
applicable laws and company policies. Ply Marts, Inc. disclaims all
responsibility and/or warranties for information and materials residing on
non-company systems or available over publicly accessible networks. Such
materials do not necessarily reflect the attitudes, opinions, or values of
Ply Marts, Inc. or its employees.  Abuse of these systems may be punishable
under local or federal laws.

To report suspected abuse, please send email to: mailto:abuse@plymart.com
_____________________________________________________________
We are on the web at http://www.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