Hi Adrian, Thank you. The strange things is we only use Python do insert/update/delete and do not run other SQL command.
Regards, Max -----Original Message----- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Tuesday, 2 May 2017 9:55 AM To: Max Wang <mw...@1080agile.com>; pgsql-general@postgresql.org Subject: Re: [GENERAL] all serial type was changed to 1 On 05/01/2017 04:29 PM, Max Wang wrote: > Hi Adrian, > > I checked and found > > is_cycled | f Hmm. A possible cause: test=# insert into serial_test (fld_1) values ('test'); INSERT 0 1 test=# insert into serial_test (fld_1) values ('test2'); INSERT 0 1 test=# insert into serial_test (fld_1) values ('test3'); INSERT 0 1 test=# select * from serial_test_id_seq ; -[ RECORD 1 ]-+-------------------- sequence_name | serial_test_id_seq last_value | 3 start_value | 1 increment_by | 1 max_value | 9223372036854775807 min_value | 1 cache_value | 1 log_cnt | 30 is_cycled | f is_called | t test=# select setval('serial_test_id_seq', 1, false); -[ RECORD 1 ] setval | 1 *** The above would not show up in the logs unless you had log_statement set to 'all' in postgresql.conf ****** 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 test=# insert into serial_test (fld_1) values ('test3'); ERROR: duplicate key value violates unique constraint "serial_test_pkey" DETAIL: Key (id)=(1) already exists. > > Regards, > Max > > -----Original Message----- > From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] > Sent: Tuesday, 2 May 2017 9:16 AM > To: Max Wang <mw...@1080agile.com>; pgsql-general@postgresql.org > Subject: Re: [GENERAL] all serial type was changed to 1 > > On 05/01/2017 04:08 PM, Max Wang wrote: >> Hi Adrian, >> >> Only sequences (id) reset to 1. > > Then per Amitabh Kant's suggestion take a look at the cycle setting for the > sequences. > > For sequence named ts_stamp_test_id_seq: > > test=# select * from ts_stamp_test_id_seq ; -[ RECORD 1 > ]-+--------------------- sequence_name | ts_stamp_test_id_seq > last_value | 6 > start_value | 1 > increment_by | 1 > max_value | 9223372036854775807 > min_value | 1 > cache_value | 1 > log_cnt | 0 > is_cycled | f > is_called | t > > > You are looking for whether is_cycled = t > > Per the docs: > > https://www.postgresql.org/docs/9.6/static/sql-createsequence.html > > "CYCLE > NO CYCLE > > The CYCLE option allows the sequence to wrap around when the maxvalue or > minvalue has been reached by an ascending or descending sequence > respectively. If the limit is reached, the next number generated will be the > minvalue or maxvalue, respectively. > > If NO CYCLE is specified, any calls to nextval after the sequence has > reached its maximum value will return an error. If neither CYCLE or NO CYCLE > are specified, NO CYCLE is the default. > " > >> >> 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