Thread: nextval(), and serial updates
Normally, currval() points to the index sequence for the last inserted
record.
However, after I call
select nextval('i_seq'::text)
on the serial data tyep i_seq, the counter gets incremented by 1. The next
INSERT skips a number in the sequence. Is this by design? I was a bit
surprized because the currval() gets incremented also, and you no longer
point to the last inserted record.
puzzled,
krishna
concrete example:
create table t1 ( i serial, s varchar(20) ); -- create table
insert into t1 (s) values ('first line'); -- insert 1 record
select * from t1; -- gives (1
row)
select currval('t1_i_seq'); -- gives 1 as
currval .. ok
select nextval('t1_i_seq'); -- gives 2 as
nextval .. ok so far
select currval('t1_i_seq'); -- gives 2 as
currval ... should this be 1?
insert into t1 (s) values ('second line'); -- inserts second line,
serial id set to 3
select * from t1; -- (2 rows)
i is now 3, should be 2
Krishna,
In your example, by calling the "nextval()" function, you have just
incremented the sequence. Every call to "nextval('sequence_name')", by
definition, increments sequence_name by one so, again referring to your
example, the next call to "currval()" correctly returns 2.
Phil Culberson
DAT Services
-----Original Message-----
From: Sampath, Krishna [mailto:KSampath@ekmail.com]
Sent: Tuesday, April 04, 2000 7:32 AM
To: pgsql-general
Subject: [GENERAL] nextval(), and serial updates
Normally, currval() points to the index sequence for the last inserted
record.
However, after I call
select nextval('i_seq'::text)
on the serial data tyep i_seq, the counter gets incremented by 1. The next
INSERT skips a number in the sequence. Is this by design? I was a bit
surprized because the currval() gets incremented also, and you no longer
point to the last inserted record.
puzzled,
krishna
concrete example:
create table t1 ( i serial, s varchar(20) ); -- create table
insert into t1 (s) values ('first line'); -- insert 1 record
select * from t1; -- gives (1
row)
select currval('t1_i_seq'); -- gives 1 as
currval .. ok
select nextval('t1_i_seq'); -- gives 2 as
nextval .. ok so far
select currval('t1_i_seq'); -- gives 2 as
currval ... should this be 1?
insert into t1 (s) values ('second line'); -- inserts second line,
serial id set to 3
select * from t1; -- (2 rows)
i is now 3, should be 2
Hi!
In my opinion there is absolutely no doubt that this is the intended
behavior.
When you do select nextval you are saying:
INCREMENT THE COUNTER TO THE NEXT VALUE.
When you do currval, currval should (and does) give you the most up to
date value in YOUR session.
I guess that when an INSERT in a table with a sequence field is processed,
postgres will internally execute select nextval() - like a trigger.
And, also, the implementation of serial values probably assumes that you
don't call nextval yourself, unless you really know what you are doing...
Regards, Silvio
On Tue, 4 Apr 2000, Sampath, Krishna wrote:
>
> Normally, currval() points to the index sequence for the last inserted
> record.
>
> However, after I call
> select nextval('i_seq'::text)
> on the serial data tyep i_seq, the counter gets incremented by 1. The next
> INSERT skips a number in the sequence. Is this by design? I was a bit
> surprized because the currval() gets incremented also, and you no longer
> point to the last inserted record.
>
> puzzled,
> krishna
>
> concrete example:
> create table t1 ( i serial, s varchar(20) ); -- create table
> insert into t1 (s) values ('first line'); -- insert 1 record
> select * from t1; -- gives (1
> row)
> select currval('t1_i_seq'); -- gives 1 as
> currval .. ok
> select nextval('t1_i_seq'); -- gives 2 as
> nextval .. ok so far
> select currval('t1_i_seq'); -- gives 2 as
> currval ... should this be 1?
> insert into t1 (s) values ('second line'); -- inserts second line,
> serial id set to 3
> select * from t1; -- (2 rows)
> i is now 3, should be 2
>
>
>
``````````` Silvio Emanuel Nunes Barbosa de Macedo (PhD Std) '''''''''''''
smacedo@ic.ac.uk smacedo@inescn.pt
Intelligent and Interactive Systems Telecom. and Multimedia
Imperial College, University of London INESC Porto
Exhibition Road, Pc da Republica, 93
London SW7 2AZ, England 4050-497 Porto Portugal
Tel:+44 171 5946323 Tel:+351 22 2094220