Skip site navigation (1) Skip section navigation (2)

Re: next integer in serial key

From: <terry(at)ashtonwoodshomes(dot)com>
To: <olly(at)lfix(dot)co(dot)uk>, <terry(at)ashtonwoodshomes(dot)com>
Cc: "'Kenneth Gonsalves'" <lawgon(at)thenilgiris(dot)com>,"'Postgresql Sql Group (E-mail)'" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: next integer in serial key
Date: 2004-07-22 13:10:32
Message-ID: 01a401c46fed$451d58e0$2766f30a@development.greatgulfhomes.com (view raw or flat)
Thread:
Lists: pgsql-sql
> > 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

So without knowing his architecture, I needed to state that caveat, albeit rare.

Even with knowing the architecture, the point still holds that you need to call currval() before
another insert (or any call to nextval) is made.  That probably should have been clearer, sorry.


In response to

Responses

pgsql-sql by date

Next:From: terryDate: 2004-07-22 13:38:51
Subject: Re: next integer in serial key
Previous:From: Peter EisentrautDate: 2004-07-22 12:41:04
Subject: Re: LIKE on index not working

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group