pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael J Davis <michael.j.davis@tvguide.com>
To: '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 12:22:56 -0500
Message-ID: <93C04F1F5173D211A27900105AA8FCFC299248@lambic.prevuenet.com> (raw)

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:51 AM
	To:	Hutton, Rob; steinbeck@ice.mpg.de
	Cc:	pgsql-sql@postgreSQL.org
	Subject:	RE: [SQL] Re: [INTERFACES] JDBC and 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
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



	************



view thread (7+ messages)  latest in thread

Message-ID: <93C04F1F5173D211A27900105AA8FCFC299248@lambic.prevuenet.com>
Permalink:  ../93C04F1F5173D211A27900105AA8FCFC299248@lambic.prevuenet.com/
Also on:    postgresql.org/message-id/93C04F1F5173D211A27900105AA8FCFC299248@lambic.prevuenet.com

 · 

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-sql@postgresql.org
  Cc: michael.j.davis@tvguide.com, 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: <93C04F1F5173D211A27900105AA8FCFC299248@lambic.prevuenet.com>
  References: <93C04F1F5173D211A27900105AA8FCFC299248@lambic.prevuenet.com>

* 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