Re: [GENERAL] performance issue using DBI

If you need to get the actual "current value" without incrementing, try:

SELECT last_value FROM <sequence_name>;

This will return the last value returned from a nextval command directly

from the sequence properties in the system tables.


-----Original Message-----
From: Dan Langille [mailto:dan@langille.org]
Sent: Thursday, June 06, 2002 12:34 PM
To: Nicolas Nolst
Cc: pgsql-admin@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [ADMIN] [GENERAL] performance issue using DBI


On 6 Jun 2002 at 17:14, Oliver Elphick wrote:

> On Thu, 2002-06-06 at 14:55, Joshua b. Jore wrote:u
> >       Don't use currval since some other process might alter the
> > sequence between the time you call nextval and currval.
>
> This is wrong.  currval() will always return the last serial assigned
in
> *the*same*session*.  It is not affected by other users at all.

Folks, here's a short test which might help.  Note the BEGIN.

$ psql testing
testing=# create sequence test;
CREATE
testing=# select currval('test');
ERROR:  test.currval is not yet defined in this session
testing=# select setval('test', 1);
 setval
--------
      1
(1 row)

testing=# select currval('test');
 currval
---------
       1
(1 row)

testing=# select currval('test');
 currval
---------
       1
(1 row)

testing=# select currval('test');
 currval
---------
       1
(1 row)

testing=# select currval('test');
 currval
---------
       1
(1 row)

testing=#

Then, in another window, I did this:

$ psql testing
# select nextval('test');
 nextval
---------
       2
(1 row)

testing=# select nextval('test');
 nextval
---------
       3
(1 row)

testing=# select nextval('test');
 nextval
---------
       4
(1 row)

testing=#

Then back to the other window:

testing=# select currval('test');
 currval
---------
       1
(1 row)

testing=# select nextval('test');
 nextval
---------
       5
(1 row)

testing=#

cheers

FWIW: I always use nextval when looking for a new ID.
--
Dan Langille


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster