Re: sequence / last_value problem

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Tina Messmann
> Sent: Thursday, May 23, 2002 7:40 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] sequence / last_value problem
>
>
> Hello List,
> i did this:
> 1. dumped out the schema of a table from pgsql7.1.3 (i had to modify it
> a bit) and inserted the schema in pgsql7.2.1
> The table has a id - field of type serial. (it is the primary key)
> The field 'last_value' of the corresponding sequence has the value '1'.
> 2. dumped out the data of that table from pgsql7.1.3 and inserted the
> data in pgsql7.2.1
>
> The field 'last_value' of the corresponding sequence has still the value
> '1' (the table contains now ~2 000 000 rows).
>
> Is the reason for this that i didnt't call since now the last_value? Is
> this normal and correct behaviour? Do i have to set 'last_value'
> manually with setval? Does this mean that when i insert a row not using
> the default value of the sequence, i have to update 'last_value' of the
> sequence every time manually? Am i missing something?

Yes, the sequence only gets bumped out when called with nextval() (as when
happens during an INSERT when there is no value given for the serial
column). This is a feature, IMHO.

You can:

. stop providing values for the serial column and let nature take its course

. call nextval() yourself with every insert that doesn't do the above

. call setval() at the end to adjust sequence to largest number

pg_dump output should contain a setval() at the end to fix this -- did you
take this out?

HTH.

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant