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 3CD76D1B2E0
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Jul 2004 09:15:31 -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 91491-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Jul 2004 12:15:35 +0000 (GMT)
Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net
	[194.217.242.85])
	by svr1.postgresql.org (Postfix) with ESMTP id 69769D1B2D9
	for <pgsql-sql@postgresql.org>; Thu, 22 Jul 2004 09:15:26 -0300 (ADT)
Received: from mailgate.bray-healthcare.com ([80.177.250.202]
	helo=solport.bray-healthcare.com)
	by anchor-post-35.mail.demon.net with esmtp (Exim 3.35 #1)
	id 1BncU1-000DZM-0Z; Thu, 22 Jul 2004 13:15:33 +0100
Received: from [192.168.1.18] (helo=braydb.bray-healthcare.com)
	by solport.bray-healthcare.com with esmtp (Exim 3.36 #1 (Debian))
	id 1BncTy-0003bd-00; Thu, 22 Jul 2004 13:15:30 +0100
Subject: Re: next integer in serial key
From: Oliver Elphick <olly@lfix.co.uk>
Reply-To: olly@lfix.co.uk
To: terry@ashtonwoodshomes.com
Cc: 'Kenneth Gonsalves' <lawgon@thenilgiris.com>,
	"Postgresql Sql Group (E-mail)" <pgsql-sql@postgresql.org>
In-Reply-To: <019001c46fe1$cb182b20$2766f30a@development.greatgulfhomes.com>
References: <019001c46fe1$cb182b20$2766f30a@development.greatgulfhomes.com>
Content-Type: text/plain
Message-Id: <1090498526.5446.9.camel@braydb>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 
Date: Thu, 22 Jul 2004 13:15:26 +0100
Content-Transfer-Encoding: 7bit
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/173
X-Sequence-Number: 18425

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.

Oliver Elphick