Re: nextval(), and serial updates

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(at)ic(dot)ac(dot)uk smacedo(at)inescn(dot)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