Postgres Pro
Downloads
Home   >   mailing lists

Thread: RE: [GENERAL] How to get seq after insert

RE: [GENERAL] How to get seq after insert

From
Michael J Davis
Date:
If two users insert into the same table at the same time, one of the users
will get the wrong currval back.  I still believe that getting the nextval
first is the safest way.  It may create holes when an insert fails, but this
is better than getting back the wrong curval.

    -----Original Message-----
    From:    Herouth Maoz [SMTP:herouth@oumail.openu.ac.il]
    Sent:    Tuesday, April 20, 1999 5:04 AM
    To:    Michael Davis; 'Brian'; pgsql-general@postgreSQL.org
    Subject:    RE: [GENERAL] How to get seq after insert

    At 00:39 +0300 on 15/04/1999, Michael Davis wrote:


    > The safest way is to select the nextval('seq_name') and then
insert using
    > this value.

    No, actually, this is the unsafest way. This means that the logic is
in the
    frontend, not the backend. Besides, one can define the sequence as
    read-only for the user who uses the database, but write for the one
who
    created the table that uses it, so that the user can't change the
sequence
    out of line.

    To make a long story short, the best way is to let the insert
statement use
    the defaulet, and then use currval( 'seq_name' ). This gives you the
last
    value given in the current session. It is multiuser-safe, etc.

    This was on the SQL list a couple of weeks ago. And by the way, the
SQL
    list is the proper list for this issue.

    Herouth

    --
    Herouth Maoz, Internet developer.
    Open University of Israel - Telem project
    http://telem.openu.ac.il/~herutma

Re: [GENERAL] How to get seq after insert

From
"Ross J. Reedstrom"
Date:
Michael J Davis wrote:
>
> If two users insert into the same table at the same time, one of the users
> will get the wrong currval back.  I still believe that getting the nextval
> first is the safest way.  It may create holes when an insert fails, but this
> is better than getting back the wrong curval.

No, as Herouth pointed out, currval is multiuser-safe: it returns the
last value given in the current session, and every user get's their own
session. I just tried it out in two psql sessions to a test sequence -
no matter how many calls to nextval I do in one window, the currval in
each gives the write answer.

Ross

>
>         -----Original Message-----
>         From:   Herouth Maoz [SMTP:herouth@oumail.openu.ac.il]>
>         To make a long story short, the best way is to let the insert
> statement use
>         the defaulet, and then use currval( 'seq_name' ). This gives you the
> last
>         value given in the current session. It is multiuser-safe, etc.
>

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

Re: [GENERAL] How to get seq after insert

From
"Ross J. Reedstrom"
Date:
Hmm, Ross needs to break for lunch - his brain is runing out of glucose!

Ross J. Reedstrom wrote:

>
> No, as Herouth pointed out, currval is multiuser-safe: it returns the
> last value given in the current session, and every user get's their own
gets
> session. I just tried it out in two psql sessions to a test sequence -
> no matter how many calls to nextval I do in one window, the currval in
> each gives the write answer.
right or correct

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