pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednext integer in serial key
10+ messages / 5 participants
[nested] [flat]
* next integer in serial key
@ 2004-07-22 02:45 Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-07-22 04:55 ` Re: next integer in serial key terry@greatgulfhomes.com
2004-07-22 05:31 ` Re: next integer in serial key Oliver Elphick <olly@lfix.co.uk>
0 siblings, 2 replies; 10+ messages in thread
From: Kenneth Gonsalves @ 2004-07-22 02:45 UTC (permalink / raw)
To: pgsql-sql
hi,
how does one get the next number in a serial type row?
--
regards
kg
http://www.onlineindianhotels.net - hotel bookings reservations in over 4600
hotels in India
http://www.ootygolfclub.org
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: next integer in serial key
2004-07-22 02:45 next integer in serial key Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-07-22 04:55 ` terry@greatgulfhomes.com
1 sibling, 0 replies; 10+ messages in thread
From: terry@greatgulfhomes.com @ 2004-07-22 04:55 UTC (permalink / raw)
To: 'Kenneth Gonsalves' <lawgon@thenilgiris.com>; pgsql-sql
The same way the default value is defined, which you can find by doing:
\d tablename
Which usually gives something like:
Table "public.gbs_floorplans"
Column | Type | Modifiers
----------------------+---------------+-----------------------------------------
---------------------------------
floorplan_id | integer | not null default
nextval('public.gbs_floorplans_floorplan_id_seq'::text)
division_id | character(3) | not null
floorplan_display_id | character(10) | not null
Hence
SELECT nextval('public.gbs_floorplans_floorplan_id_seq'::text)
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Kenneth Gonsalves
> Sent: Wednesday, July 21, 2004 10:46 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] next integer in serial key
>
>
> hi,
> how does one get the next number in a serial type row?
> --
> regards
> kg
>
> http://www.onlineindianhotels.net - hotel bookings
> reservations in over 4600
> hotels in India
> http://www.ootygolfclub.org
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: next integer in serial key
2004-07-22 02:45 next integer in serial key Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-07-22 05:31 ` Oliver Elphick <olly@lfix.co.uk>
1 sibling, 0 replies; 10+ messages in thread
From: Oliver Elphick @ 2004-07-22 05:31 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: pgsql-sql
On Thu, 2004-07-22 at 03:45, Kenneth Gonsalves wrote:
> hi,
> how does one get the next number in a serial type row?
When inserting a new row, do not mention the serial column in the list
of columns, or else give it the value DEFAULT.
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Then Peter and the other apostles answered and said,
We ought to obey God rather than men."
Acts 5:29
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: next integer in serial key
@ 2004-07-22 11:48 terry@ashtonwoodshomes.com
2004-07-22 11:40 ` Re: next integer in serial key Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-07-22 12:15 ` Re: next integer in serial key Oliver Elphick <olly@lfix.co.uk>
0 siblings, 2 replies; 10+ messages in thread
From: terry@ashtonwoodshomes.com @ 2004-07-22 11:48 UTC (permalink / raw)
To: 'Kenneth Gonsalves' <lawgon@thenilgiris.com>; pgsql-sql
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.
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 12:13 AM
> To: terry@greatgulfhomes.com
> Subject: Re: [SQL] next integer in serial key
>
>
> On Thursday 22 July 2004 10:25 am, you wrote:
> > The same way the default value is defined, which you can
> find by doing:
> > \d tablename
> >
> > Which usually gives something like:
> > Table
> "public.gbs_floorplans"
> > Column | Type |
>
> > Modifiers
> >
> >
> ----------------------+---------------+-----------------------
> -------------
> >----- ---------------------------------
> > floorplan_id | integer | not null default
> > nextval('public.gbs_floorplans_floorplan_id_seq'::text)
> > division_id | character(3) | not null
> > floorplan_display_id | character(10) | not null
> >
> > Hence
> > SELECT nextval('public.gbs_floorplans_floorplan_id_seq'::text)
>
> nope. what happens is that i enter data into a table with a
> serial type id,
> and then use that id as a foreign key to enter data into
> another table. so i
> need to know the id for the second entry. i commit after both entries
> succeed. If i use nextval to find the id, this increments the
> id, which will
> defeat the purpose.
> --
> regards
> kg
>
> http://www.onlineindianhotels.net - hotel bookings
> reservations in over 4600
> hotels in India
> http://www.ootygolfclub.org
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: next integer in serial key
2004-07-22 11:48 Re: next integer in serial key terry@ashtonwoodshomes.com
@ 2004-07-22 11:40 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
1 sibling, 0 replies; 10+ messages in thread
From: Kenneth Gonsalves @ 2004-07-22 11:40 UTC (permalink / raw)
To: terry@ashtonwoodshomes.com; pgsql-sql
On Thursday 22 July 2004 05:18 pm, 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>
did this. barf: foreign key not in original table
--
regards
kg
http://www.onlineindianhotels.net - hotel bookings reservations in over 4600
hotels in India
http://www.ootygolfclub.org
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: next integer in serial key
2004-07-22 11:48 Re: next integer in serial key terry@ashtonwoodshomes.com
@ 2004-07-22 12:15 ` Oliver Elphick <olly@lfix.co.uk>
2004-07-22 11:51 ` Re: next integer in serial key Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-07-22 13:10 ` Re: next integer in serial key terry@ashtonwoodshomes.com
1 sibling, 2 replies; 10+ messages in thread
From: Oliver Elphick @ 2004-07-22 12:15 UTC (permalink / raw)
To: terry@ashtonwoodshomes.com; +Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: next integer in serial key
2004-07-22 11:48 Re: next integer in serial key terry@ashtonwoodshomes.com
2004-07-22 12:15 ` Re: next integer in serial key Oliver Elphick <olly@lfix.co.uk>
@ 2004-07-22 11:51 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-07-22 13:38 ` Re: next integer in serial key terry@ashtonwoodshomes.com
1 sibling, 1 reply; 10+ messages in thread
From: Kenneth Gonsalves @ 2004-07-22 11:51 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; terry@ashtonwoodshomes.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: next integer in serial key
2004-07-22 11:48 Re: next integer in serial key terry@ashtonwoodshomes.com
2004-07-22 12:15 ` Re: next integer in serial key Oliver Elphick <olly@lfix.co.uk>
2004-07-22 11:51 ` Re: next integer in serial key Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-07-22 13:38 ` terry@ashtonwoodshomes.com
0 siblings, 0 replies; 10+ messages in thread
From: terry@ashtonwoodshomes.com @ 2004-07-22 13:38 UTC (permalink / raw)
To: 'Kenneth Gonsalves' <lawgon@thenilgiris.com>; 'Oliver Elphick' <olly@lfix.co.uk>; +Cc: 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: next integer in serial key
2004-07-22 11:48 Re: next integer in serial key terry@ashtonwoodshomes.com
2004-07-22 12:15 ` Re: next integer in serial key Oliver Elphick <olly@lfix.co.uk>
@ 2004-07-22 13:10 ` terry@ashtonwoodshomes.com
2004-07-22 13:52 ` Re: next integer in serial key Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 1 reply; 10+ messages in thread
From: terry@ashtonwoodshomes.com @ 2004-07-22 13:10 UTC (permalink / raw)
To: olly@lfix.co.uk; terry@ashtonwoodshomes.com; +Cc: 'Kenneth Gonsalves' <lawgon@thenilgiris.com>; 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: next integer in serial key
2004-07-22 11:48 Re: next integer in serial key terry@ashtonwoodshomes.com
2004-07-22 12:15 ` Re: next integer in serial key Oliver Elphick <olly@lfix.co.uk>
2004-07-22 13:10 ` Re: next integer in serial key terry@ashtonwoodshomes.com
@ 2004-07-22 13:52 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 10+ messages in thread
From: Stephan Szabo @ 2004-07-22 13:52 UTC (permalink / raw)
To: terry@ashtonwoodshomes.com; +Cc: olly@lfix.co.uk, 'Kenneth Gonsalves' <lawgon@thenilgiris.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2004-07-22 13:52 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-22 02:45 next integer in serial key Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-07-22 04:55 ` terry@greatgulfhomes.com
2004-07-22 05:31 ` Oliver Elphick <olly@lfix.co.uk>
2004-07-22 11:48 Re: next integer in serial key terry@ashtonwoodshomes.com
2004-07-22 11:40 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-07-22 12:15 ` Oliver Elphick <olly@lfix.co.uk>
2004-07-22 11:51 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-07-22 13:38 ` terry@ashtonwoodshomes.com
2004-07-22 13:10 ` terry@ashtonwoodshomes.com
2004-07-22 13:52 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox