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 01F8ED1B374
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Jul 2004 10:52:18 -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 45023-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Jul 2004 13:52:24 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 93E47D1B33D
	for <pgsql-sql@postgresql.org>; Thu, 22 Jul 2004 10:52:15 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 4B190358B2; Thu, 22 Jul 2004 06:52:25 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 49711358AA; Thu, 22 Jul 2004 06:52:25 -0700 (PDT)
Date: Thu, 22 Jul 2004 06:52:25 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: terry@ashtonwoodshomes.com
Cc: olly@lfix.co.uk, 'Kenneth Gonsalves' <lawgon@thenilgiris.com>,
	"'Postgresql Sql Group (E-mail)'" <pgsql-sql@postgresql.org>
Subject: Re: next integer in serial key
In-Reply-To: <01a401c46fed$451d58e0$2766f30a@development.greatgulfhomes.com>
Message-ID: <20040722064825.Q98843@megazone.bigpanda.com>
References: <01a401c46fed$451d58e0$2766f30a@development.greatgulfhomes.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
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/180
X-Sequence-Number: 18432

On Thu, 22 Jul 2004 terry@ashtonwoodshomes.com wrote:

> > > 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.
>
> That statement depends on different factors.  If you for example have an application server, and the
> database connection is shared across multiple application server clients (or the query results get
> cached by your application server, Ugh!), the statement IS valid:  I encountered this issue 2 years
> ago with coldfusion 4.5 using the unixODBC driver against Postgres 7.1.1

If your application server will share your connection without your
explicit releasing of it, then yes, currval() is unsafe.  So are
basically transactions, cursors, session variables and deferrable
constraints.  That's not a valid platform to be using a database from
really.