Обсуждение: 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? Regards Tina
> -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tina Messmann > Sent: Thursday, May 23, 2002 7:40 AM > To: pgsql-general@postgresql.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@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant
Joel Burton wrote: [snip..] >pg_dump output should contain a setval() at the end to fix this -- did you >take this out? > Thank you for the clarification. No, i didn't take anything out from my dump and there is no setval at the end of my dump file.... Regards Tina > > >HTH. > >Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton >Knowledge Management & Technology Consultant >
If she had never called nextval, then the sequence would still be on its first value, would that be why the dump did not include the setval statement??? Just an idea... Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tina Messmann > Sent: Thursday, May 23, 2002 9:38 AM > To: Joel Burton; pgsql-general@postgresql.org > Subject: Re: [GENERAL] sequence / last_value problem > > > Joel Burton wrote: > [snip..] > > >pg_dump output should contain a setval() at the end to fix > this -- did you > >take this out? > > > Thank you for the clarification. > No, i didn't take anything out from my dump and there is no setval at > the end of my dump file.... > > Regards > Tina > > > > > > >HTH. > > > >Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton > >Knowledge Management & Technology Consultant > > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
terry@greatgulfhomes.com wrote: >If she had never called nextval, then the sequence would still be on its >first value, would that be why the dump did not include the setval >statement??? > >Just an idea... > to clarify: i never called nextval in the pgsql7.2.1 database. the dump is from a pgsql7.1.3 database and there the value of 'last_value' is correctly set to ~2 000 000 Regards Tina > >Terry Fielder >Network Engineer >Great Gulf Homes / Ashton Woods Homes >terry@greatgulfhomes.com > >>-----Original Message----- >>From: pgsql-general-owner@postgresql.org >>[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tina Messmann >>Sent: Thursday, May 23, 2002 9:38 AM >>To: Joel Burton; pgsql-general@postgresql.org >>Subject: Re: [GENERAL] sequence / last_value problem >> >> >>Joel Burton wrote: >>[snip..] >> >>>pg_dump output should contain a setval() at the end to fix >>> >>this -- did you >> >>>take this out? >>> >>Thank you for the clarification. >>No, i didn't take anything out from my dump and there is no setval at >>the end of my dump file.... >> >>Regards >>Tina >> >>> >>>HTH. >>> >>>Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton >>>Knowledge Management & Technology Consultant >>> >> >> >> >>---------------------------(end of >>broadcast)--------------------------- >>TIP 6: Have you searched our list archives? >> >>http://archives.postgresql.org >>