Re: next integer in serial key - Mailing list pgsql-sql

That usually works.  But if you can have 2 records in that table that are identical except the
serial column, your query to get the id will return 2 results.  Its also inefficient, if that query
is costly (whether or not it can return 2 results).

That's why I do:

SELECT nextval(my_tables_sequence) AS next_id;

INSERT INTO mytable (serial_column, data_columns...) VALUES (next_id, data_columns...)

INSERT INTO related_table (fkey_column, other_columns...) VALUES (next_id, other_columns...)


You can even do ALL that inside a transaction which guarantees that either:
1) ALL of the inserts are done
OR
2) NONE of the inserts are done

(Note it doesn't roll back the sequence, that id on rollback would become unused)


Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085


> -----Original Message-----
> From: Kenneth Gonsalves [mailto:lawgon@thenilgiris.com]
> Sent: Thursday, July 22, 2004 7:52 AM
> To: Oliver Elphick; terry@ashtonwoodshomes.com
> Cc: Postgresql Sql Group (E-mail)
> Subject: Re: [SQL] next integer in serial key
>
>
> 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
>