Thread: get the previous assigned sequence value
I've been looking through the online docs, so far have not found this information. After an INSERT, I want to retrieve the value of the sequence I use for unique_ids Any suggestions ? Kevin -------------------------------------------------------------------- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Production | 333 Texas St #175 | FAX:318.221.6612 kheflin@shreve.net | Shreveport, LA 71101 | http://www.shreve.net --------------------------------------------------------------------
Kevin Heflin wrote:
> I've been looking through the online docs, so far have not found this
> information.
>
> After an INSERT, I want to retrieve the value of the sequence I use for
> unique_ids
>
> Any suggestions ?
Use the OID returned from the INSERT to select the newly created value,
i.e.,
insert into mytable (stuff...) ...
select id from mytable where oid = NNNN...
How to get the OID in the first example varies depending on your interface
(perl/DBI, PL/pgsql, etc).
Alternatively, use the currval(mytable_id_seq) function within the same
session immediately after the insert.
Cheers.
Ed
> > > > After an INSERT, I want to retrieve the value of the sequence I use for > > unique_ids > > ... > > Alternatively, use the currval(mytable_id_seq) function within the same > session immediately after the insert. > With this second method, you'd probably need to beware race conditions. If another process inserts a record into mytable after you do but before you call currval(), then you'll get the wrong value. Not an issue if you've only got one process accessing the table - probably is one if you have two or more. - Richard Huxton
On Thu, 9 Dec 1999, Dev Elop wrote: > > > > > > After an INSERT, I want to retrieve the value of the sequence I use for > > > unique_ids > > > > ... > > > > Alternatively, use the currval(mytable_id_seq) function within the same > > session immediately after the insert. > > > With this second method, you'd probably need to beware race conditions. If > another process inserts a record into mytable after you do but before you > call currval(), then you'll get the wrong value. > > Not an issue if you've only got one process accessing the table - probably > is one if you have two or more. I don't think that's true the currval belongs to the process (as it were) so that what happens is that currval remains unchanged by inserts by other processes. nextval however is 'affected' meaning that nextval won't just return curval+1, it returns whatever the next sequence item is taking into account the increasses caused by other processes. at least I seem to remember that from previous postings... any seconders? Cheers, S. Stuart C. G. Rison Department of Biochemistry and Molecular Biology 6th floor, Darwin Building, University College London (UCL) Gower Street, London, WC1E 6BT, United Kingdom Tel. 0207 504 2303, Fax. 0207 380 7193 e-mail: rison@biochem.ucl.ac.uk
> > With this second method, you'd probably need to beware race conditions. If > > another process inserts a record into mytable after you do but before you > > call currval(), then you'll get the wrong value. > > > > Not an issue if you've only got one process accessing the table - probably > > is one if you have two or more. > > I don't think that's true the currval belongs to the process (as it were) > so that what happens is that currval remains unchanged by inserts by other > processes. > > nextval however is 'affected' meaning that nextval won't just return > curval+1, it returns whatever the next sequence item is taking into > account the increasses caused by other processes. > > at least I seem to remember that from previous postings... any seconders? currval always gives you the most recent sequence value returned by your own back-end, so that it is not affected by waht other processes do. With nextval it also depends on what cache size you chose. If youchose 1, the default, nextval is directly affected by whether other processes have doena nextval. If the caches is larger this is not necessarily the case. Adriaan
Why you discuss it????
The problem is quite clean and it was solvev many years ago.....
1. Get the *next* sequence value:
select nextval("gogo_seq");
2. Store it into a variable (for example $X)
3. Insert proper data (and and sequence value)
insert into gogo values( $X, 'NMMM', 'mailto:nmmm@nmmm.nu');
4. Use value $X, as u want
insert into gogo_detail values( $X, 'bla bla0');
insert into gogo_detail values( $X, 'bla bla1');
insert into gogo_detail values( $X, 'bla bla2');
Am I clear?
nmmm
--------------------------------------------------------------
The reboots are for hardware upgrades!
"http://www.nmmm.nu; <nmmm@nmmm.nu>
----- Original Message -----
From: Adriaan Joubert <a.joubert@albourne.com>
Cc: Dev Elop <dev@archonet.com>; pgsql-general
<pgsql-general@postgreSQL.org>
Sent: 09 Äåêåìâðè 1999 ã. 15:22
Subject: Re: [GENERAL] get the previous assigned sequence value
> > > With this second method, you'd probably need to beware race
conditions. If
> > > another process inserts a record into mytable after you do but before
you
> > > call currval(), then you'll get the wrong value.
> > >
> > > Not an issue if you've only got one process accessing the table -
probably
> > > is one if you have two or more.
> >
> > I don't think that's true the currval belongs to the process (as it
were)
> > so that what happens is that currval remains unchanged by inserts by
other
> > processes.
> >
> > nextval however is 'affected' meaning that nextval won't just return
> > curval+1, it returns whatever the next sequence item is taking into
> > account the increasses caused by other processes.
> >
> > at least I seem to remember that from previous postings... any
seconders?
>
>
> currval always gives you the most recent sequence value returned by your
> own back-end, so that it is not affected by waht other processes do.
> With nextval it also depends on what cache size you chose. If youchose
> 1, the default, nextval is directly affected by whether other processes
> have doena nextval. If the caches is larger this is not necessarily the
> case.
>
> Adriaan
>
> ************
Gah, this is getting a bit annoying, correcting this bit of mis-information. currval() is kept as part of the session context, as is completely multi-user safe. That's why the person you quoted said 'in the same session'. Where is everyone coming up with the wrong idea on this? Is there a major commercial DB with sequence objects that gets this wrong? Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005 On Thu, Dec 09, 1999 at 10:36:40AM -0000, Dev Elop wrote: > > > > > > After an INSERT, I want to retrieve the value of the sequence I use for > > > unique_ids > > > > ... > > > > Alternatively, use the currval(mytable_id_seq) function within the same > > session immediately after the insert. > > > With this second method, you'd probably need to beware race conditions. If > another process inserts a record into mytable after you do but before you > call currval(), then you'll get the wrong value. > > Not an issue if you've only got one process accessing the table - probably > is one if you have two or more. > > - > Richard Huxton > > > ************ >
"Ross J. Reedstrom" wrote:
> Gah, this is getting a bit annoying, correcting this bit of
> mis-information.
>
> currval() is kept as part of the session context, as is completely
> multi-user safe. That's why the person you quoted said 'in the same
> session'.
>
> Where is everyone coming up with the wrong idea on this? Is there a
> major commercial DB with sequence objects that gets this wrong?
I think it is primarily because (a) it is intuitive to think there is a race
condition, which fortunately psgql folks have gone to significant efforts to
avoid, and (b) the documentation at
http://www.postgresql.org/docs/postgres/sql-createsequence.htm
is less than concise/clear on this issue.
Same thing is true for the question of how to retrieve the newly-inserted
serial, which is why people keep asking the same question over and over (that,
and because people don't know about www.deja.com or the mailing list search
engine...or the engine is broken).
Cheers.
Ed
> I think it is primarily because (a) it is intuitive to think there is a race > condition, which fortunately psgql folks have gone to significant efforts to > avoid, and (b) the documentation at > > http://www.postgresql.org/docs/postgres/sql-createsequence.htm > > is less than concise/clear on this issue. > > Same thing is true for the question of how to retrieve the newly-inserted > serial, which is why people keep asking the same question over and over (that, > and because people don't know about www.deja.com or the mailing list search > engine...or the engine is broken). My book will cover that in detail. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Orakle's fault :-) I remembered there was a thorough (very thorough) discussion months before. This raises a question: because the web based search engine is slow, why can't setup a text file achive? so that people can ftp the big file and use vi do whatever they want locally? does using the search engine increase potential value to the site? if in that case, I'd better shut up ;-), but I can not see any, it is rather a load burden. perhaps some commercialization plan for future? On Thu, 9 Dec 1999, Ed Loehr wrote: > "Ross J. Reedstrom" wrote: > > > Gah, this is getting a bit annoying, correcting this bit of > > mis-information. > > > > currval() is kept as part of the session context, as is completely > > multi-user safe. That's why the person you quoted said 'in the same > > session'. > > > > Where is everyone coming up with the wrong idea on this? Is there a > > major commercial DB with sequence objects that gets this wrong? > > I think it is primarily because (a) it is intuitive to think there is a race > condition, which fortunately psgql folks have gone to significant efforts to > avoid, and (b) the documentation at > > http://www.postgresql.org/docs/postgres/sql-createsequence.htm > > is less than concise/clear on this issue. > > Same thing is true for the question of how to retrieve the newly-inserted > serial, which is why people keep asking the same question over and over (that, > and because people don't know about www.deja.com or the mailing list search > engine...or the engine is broken). > > Cheers. > Ed > > > > > > ************ >