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:[email protected]] Sent: Tuesday, 2 May 2017 9:49 AM To: Adrian Klaver <[email protected]> Cc: Max Wang <[email protected]>; Amitabh Kant <[email protected]>; [email protected] Subject: Re: [GENERAL] all serial type was changed to 1 On Mon, May 1, 2017 at 7:45 PM, Adrian Klaver <[email protected]<mailto:[email protected]>> 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:[email protected]<mailto:[email protected]>] Sent: Tuesday, 2 May 2017 9:31 AM To: Max Wang <[email protected]<mailto:[email protected]>>; Amitabh Kant <[email protected]<mailto:[email protected]>> Cc: [email protected]<mailto:[email protected]> 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 [email protected]<mailto:[email protected]> -- Sent via pgsql-general mailing list ([email protected]<mailto:[email protected]>) 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. [http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]
