Re: [GENERAL] all serial type was changed to 1 - Mailing list pgsql-general

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.klaver@aklaver.com]
> Sent: Tuesday, 2 May 2017 9:16 AM
> To: Max Wang <mwang@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
ascendingor descending sequence respectively. If the limit is reached, the next number generated will be the minvalue
ormaxvalue, 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.klaver@aklaver.com