RE: [SQL] Re: [INTERFACES] JDBC and getting just assigned serial number - Mailing list pgsql-sql

I do it this way because I don't know the connection state either.  I
don't trust anything.  Every update, insert, select, etc. is in a try..catch
block that tries to recover if there is a problem .  This includes lost
connections, temporary network outages, etc.  Furthermore, I do not do two inserts or insert/update pair, I do one
select to get the nextval, then an insert with all of the values including
the serial value.

-----Original Message-----
From: Michael J Davis [mailto:michael.j.davis@tvguide.com]
Sent: Wednesday, September 22, 1999 1:23 PM
To: 'Herouth Maoz'; Hutton, Rob; steinbeck@ice.mpg.de
Cc: pgsql-sql@postgreSQL.org
Subject: RE: [SQL] Re: [INTERFACES] JDBC and getting just assigned
serial number


This issue has come up before and after contemplating all the comments
surrounding the currval/nextval issue I still feel that getting the nextval
from a sequence before the insert statement is the best and most universal
approach.  The nextval should work in any database environment.  Did Herouth
actually create the code surrounding how currval works?  Is this how other
databases deal with their equivalent of currval?  It would important for me
to know whether this technique is valid for Postgres or all databases (esp
Oracle and SQL Server).  Given the details of the explanation below and how
it interacts with multiple simultaneous users, I would like to know how
intimate Herouth is with the actual code.

Thanks, Michael
-----Original Message-----From:    Herouth Maoz [SMTP:herouth@oumail.openu.ac.il]Sent:    Wednesday, September 22, 1999
10:51AMTo:    Hutton, Rob; steinbeck@ice.mpg.deCc:    pgsql-sql@postgreSQL.orgSubject:    RE: [SQL] Re: [INTERFACES]
JDBCand getting just
 
assigned serial  number
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
absolutelyguarantees you get the correct number, it was designed exactly for
thispurpose. Using nextval and putting the value in the table will work,
but itrequires two operations for the insert, thus potentially wasting
morenumbers (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 thesession between you and the backend. When it draws a nextval for
you, itautomatically puts the number it has drawn in this session memory.
Thecurrval operation gets this stored number. It DOES NOT get the last
valuefrom the sequence table. That's a common misconception.
To make myself clear, let's suppose process A and process B run the
sameprogram, where the table has two fields, one serial named "ser", the
othera 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 stateA          initially      emptyB          initially      emptyA          INSERT
    'the_table_ser_seq' = 28B          INSERT        'the_table_ser_seq' = 29A          currval
'the_table_ser_seq'= 28  --- returns 28B          currval       'the_table_ser_seq' = 29  --- returns 29A (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 projecthttp://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
_____________________________________________________________