X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id D98DED1B18C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Jul 2004 10:27:33 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 32669-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Jul 2004 13:27:38 +0000 (GMT)
Received: from sharp.noc24x7.com (unknown [69.56.128.138])
	by svr1.postgresql.org (Postfix) with ESMTP id 781BFD1B1FD
	for <pgsql-sql@postgresql.org>; Thu, 22 Jul 2004 10:27:30 -0300 (ADT)
Received: from [61.1.218.166] (helo=thenilgiris.com)
	by sharp.noc24x7.com with asmtp (Exim 4.34)
	id 1Bndbo-0001Iu-JE; Thu, 22 Jul 2004 18:57:42 +0530
Received: from localhost (localhost [[UNIX: localhost]])
	by thenilgiris.com (8.11.2/8.11.2) id i6MBpUY20640;
	Thu, 22 Jul 2004 17:21:30 +0530
Content-Type: text/plain;
  charset="iso-8859-1"
From: Kenneth Gonsalves <lawgon@thenilgiris.com>
To: Oliver Elphick <olly@lfix.co.uk>, terry@ashtonwoodshomes.com
Subject: Re: next integer in serial key
Date: Thu, 22 Jul 2004 17:21:30 +0530
X-Mailer: KMail [version 1.2]
Cc: "Postgresql Sql Group (E-mail)" <pgsql-sql@postgresql.org>
References: <019001c46fe1$cb182b20$2766f30a@development.greatgulfhomes.com>
	<1090498526.5446.9.camel@braydb>
In-Reply-To: <1090498526.5446.9.camel@braydb>
MIME-Version: 1.0
Message-Id: <04072217213011.01150@thenilgiris.com>
Content-Transfer-Encoding: 8bit
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - sharp.noc24x7.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - thenilgiris.com
X-Source: 
X-Source-Args: 
X-Source-Dir: 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200407/178
X-Sequence-Number: 18430

On Thursday 22 July 2004 05:45 pm, Oliver Elphick wrote:
> On Thu, 2004-07-22 at 12:48, terry@ashtonwoodshomes.com wrote:
> > Actually it does work, call nextval to get your next value, then call
> > your INSERT statement,
> > explicitly giving said value for the serial column.  Then you can
> > proceed with using said value in
> > the INSERT statement of the related inserts with foreign keys to it.
> >
> > Alternatively, you can do:
> > INSERT  (accepting the default)
> > then SELECT currval(the_sequence_object);
> > then <extra inserts of related foreign key records>
> >
> > NOTE: 2nd method assumes that nobody else called nextval() on the
> > sequence between when you did the
> > insert and when you did the select currval().  Note that being inside
> > a transaction is NOT
> > sufficient, you need an explicit lock on the sequence.  I do not
> > recommend the 2nd method, too much
> > can go wrong.
>
> This last paragraph is wrong and irrelevant.  It is a point which for
> some reason is continually being misunderstood.
>
> currval() *always* returns the last value generated for the sequence in
> the *current session*.  It is specifically designed to do what you are
> suggesting without any conflict with other sessions.  There is *never*
> any risk of getting a value that nextval() returned to some other user's
> session.
>
> The downside is that it operates outside the transaction and therefore
> cannot be rolled back.  It is also necessary to run nextval() in the
> session (either explicitly or by letting a serial column take its
> default) before you can use currval() on the sequence.

in short, the only safe way of doing this is to commit on insert to the main 
table and then query it to get the value to insert in the other tables - and 
if the subsequent inserts fail ..................
-- 
regards
kg

http://www.onlineindianhotels.net - hotel bookings reservations in over 4600 
hotels in India
http://www.ootygolfclub.org