On Mon, May 1, 2017 at 7:55 PM, Max Wang <mw...@1080agile.com> wrote:
> Hi Melvin, > > > > I already reset id to correct value after this happen. This is a > production database. We could not stop and wait for trouble shooting. I > manually reset sequence of id to correct value. > > > > Below is current result: > > > > sequence_name | xxxxxx_id_seq > > last_value | 190996 > > start_value | 1 > > increment_by | 1 > > max_value | 9223372036854775807 > > min_value | 1 > > cache_value | 1 > > log_cnt | 29 > > is_cycled | f > > is_called | t > > > > Regards, > > Max > > > > *From:* Melvin Davidson [mailto:melvin6...@gmail.com] > *Sent:* Tuesday, 2 May 2017 9:49 AM > *To:* Adrian Klaver <adrian.kla...@aklaver.com> > *Cc:* Max Wang <mw...@1080agile.com>; Amitabh Kant <amitabhk...@gmail.com>; > pgsql-general@postgresql.org > > *Subject:* Re: [GENERAL] all serial type was changed to 1 > > > > > > > > On Mon, May 1, 2017 at 7:45 PM, Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > > On 05/01/2017 04:36 PM, Max Wang wrote: > > Hi Amitabh, > > I mean the serial sequence that controls the id value has been set to 1 > for all tables. That's why I got the duplicate key value error when I tried > to insert the new record to table. > > > So what does the sequence query show? > > As an example in psql: > > test=# create table serial_test(id serial PRIMARY KEY, fld_1 varchar); > CREATE TABLE > > test=# \d serial_test > Table "public.serial_test" > Column | Type | Modifiers > --------+-------------------+------------------------------- > --------------------------- > id | integer | not null default > nextval('serial_test_id_seq'::regclass) > fld_1 | character varying | > Indexes: > "serial_test_pkey" PRIMARY KEY, btree (id) > > The above shows that the sequence associated with the serial type is: > 'serial_test_id_seq' > > The below shows how to select from that sequence: > > test=# select * from serial_test_id_seq ; > -[ RECORD 1 ]-+-------------------- > sequence_name | serial_test_id_seq > last_value | 1 > start_value | 1 > increment_by | 1 > max_value | 9223372036854775807 > min_value | 1 > cache_value | 1 > log_cnt | 0 > is_cycled | f > is_called | f > > > Can you do that on the serial column from one the affected tables and post > the results here? > > > > > Thanks. > > Regards, > Max > > -----Original Message----- > From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] > Sent: Tuesday, 2 May 2017 9:31 AM > To: Max Wang <mw...@1080agile.com>; Amitabh Kant <amitabhk...@gmail.com> > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] all serial type was changed to 1 > > On 05/01/2017 04:11 PM, Max Wang wrote: > > Hi Amitabh, > > > > Thank you for suggestion. We did not reach the limit of serial type. > Some tables only have hundreds of rows. > > > It would helpful if you ran the query I showed in my previous post on one > the sequences just so we can see. > > From subsequent post of yours: > > "Sorry. I mean all tables’ id column were reset to 1." > > I thought I understood on this, now I am not sure. Do you mean that the > actual values in the id column in all the tables have been set to 1 or that > the serial sequence that controls the id value has been set to 1? > > > > > Regards, > > Max > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > > *Hmmm, it's beginning to look like someone did a "SELECT setval('seqname', > 1);" For every sequence. * > > *Is that a possibility?* > > > -- > > *Melvin Davidson* > > *I reserve the right to fantasize. Whether or not you wish to share my > fantasy is entirely up to you. * > *>I already reset id to correct value after this happen. * *Then as Adrian or I suggested, someone did a manual* *"SELECT setval('seqname', 1);" For every sequence. * *Since you have reset to correct values already, I seriously doubt we can trace this any furthur.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
