Horton

Thread

  1. get the previous assigned sequence value

    Kevin Heflin <kheflin@shreve.net> — 1999-12-08T22:40:58Z

    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
--------------------------------------------------------------------

  2. Re: [GENERAL] get the previous assigned sequence value

    Ed Loehr <eloehr@austin.rr.com> — 1999-12-09T04:15:32Z

    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

  3. Re: [GENERAL] get the previous assigned sequence value

    Richard Huxton <dev@archonet.com> — 1999-12-09T10:36:40Z

    > >
> > 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

  4. Re: [GENERAL] get the previous assigned sequence value

    Stuart Rison <rison@biochemistry.ucl.ac.uk> — 1999-12-09T12:49:24Z

    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

  5. Re: [GENERAL] get the previous assigned sequence value

    Adriaan Joubert <a.joubert@albourne.com> — 1999-12-09T13:22:56Z

    > > 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

  6. Re: [GENERAL] get the previous assigned sequence value

    Nikolay Mijaylov <nmmm@nmmm.nu> — 1999-12-09T14:35:35Z

    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
>
> ************

  7. Re: [GENERAL] get the previous assigned sequence value

    Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu> — 1999-12-09T16:35:32Z

    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
> 
> 
> ************
>

  8. Re: [GENERAL] get the previous assigned sequence value

    Ed Loehr <eloehr@austin.rr.com> — 1999-12-09T17:57:32Z

    "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

  9. Re: [GENERAL] get the previous assigned sequence value

    Bruce Momjian <pgman@candle.pha.pa.us> — 1999-12-09T18:23:44Z

    > 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

  10. Re: [GENERAL] get the previous assigned sequence value

    kaiq@realtyideas.com — 1999-12-09T19:26:32Z

    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
> 
> 
> 
> 
> 
> ************
>