all serial type was changed to 1

On Sun, Apr 30, 2017 at 10:51 PM, Max Wang <mwang@1080agile.com> wrote:

We have a PostgreSQL database. There are 26 tables and we use serial type as
primary key. We had a insert error as “duplicate key value violates unique
constraint, DETAIL: Key (id)=(1) already exists.” one weeks ago. I checked
and found all tables’ id were reset to 1.

I've heard of this happening before. I never determined what the cause was.

--
Peter Geoghegan

VMware vCenter Server
https://www.vmware.com/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, May 1, 2017 at 11:21 AM, Max Wang <mwang@1080agile.com> wrote:

Hi All,

We have a PostgreSQL database. There are 26 tables and we use serial type
as primary key. We had a insert error as “duplicate key value violates
unique constraint, DETAIL: Key (id)=(1) already exists.” one weeks ago. I
checked and found all tables’ id were reset to 1.

I checked database log and did not find any useful information. I am not
sure why this happen. The only script which connect to this database is a
Python script and only do normal insert/update/delete actions.

Please give me some suggestions if you happen to know something about this
issue. I appreciate any feedback you might have.

I am very new to PostgreSQL and this mail list. Please let me know if I
did not something wrong.

Thank you.

Regards,

Max

By any chance, has it to do anything with the Cycle option of sequences:

https://www.postgresql.org/docs/9.5/static/sql-createsequence.html

On 1 May 2017 at 17:51, Max Wang <mwang@1080agile.com> wrote:

We have a PostgreSQL database. There are 26 tables and we use serial type as
primary key. We had a insert error as “duplicate key value violates unique
constraint, DETAIL: Key (id)=(1) already exists.” one weeks ago. I checked
and found all tables’ id were reset to 1.

Sounds like something that might happen if you'd just bulk loaded the
data and didn't set the sequences.

If you really did use serial types then you could set all these to the
max value of the column which they belong to.

The following will give you a list of commands to execute:

SELECT 'select setval(''' || c.relname || ''', max(' ||
quote_ident(a.attname) || ')) from ' || d.refobjid::regclass || ';'
FROM pg_depend d
INNER JOIN pg_class c ON d.objid = c.oid
INNER JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum =
d.refobjsubid
WHERE c.relkind = 'S' AND d.refclassid = 1259;

You may like to check that returns 26 rows as you expect and verify
that all those sequences do need reset before running the command.

If you're running Postgres 9.6 and using psql, you can execute the
above then execute \gexec which will execute the previous result set
as commands.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/30/2017 10:51 PM, Max Wang wrote:

Hi All,

We have a PostgreSQL database. There are 26 tables and we use serial
type as primary key. We had a insert error as �duplicate key value
violates unique constraint, DETAIL: Key (id)=(1) already exists.� one
weeks ago. I checked and found all tables� id were reset to 1.

So to be clear:

Every row in each of the 26 tables has an id of 1?

or

Do you mean the sequences where reset to 1?

I checked database log and did not find any useful information. I am
not sure why this happen. The only script which connect to this database
is a Python script and only do normal insert/update/delete actions.

Please give me some suggestions if you happen to know something about
this issue. I appreciate any feedback you might have.

I am very new to PostgreSQL and this mail list. Please let me know if I
did not something wrong.

Thank you.

Regards,

Max

--
Adrian Klaver
adrian.klaver@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

Hi David,

Thanks for suggestion. We use PostgreSQL 9.5. And we did not import or bulk loaded data to these tables.

Regards,
Max

-----Original Message-----
From: David Rowley [mailto:david.rowley@2ndquadrant.com]
Sent: Monday, 1 May 2017 11:05 PM
To: Max Wang <mwang@1080agile.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1

On 1 May 2017 at 17:51, Max Wang <mwang@1080agile.com> wrote:

We have a PostgreSQL database. There are 26 tables and we use serial
type as primary key. We had a insert error as “duplicate key value
violates unique constraint, DETAIL: Key (id)=(1) already exists.” one
weeks ago. I checked and found all tables’ id were reset to 1.

Sounds like something that might happen if you'd just bulk loaded the data and didn't set the sequences.

If you really did use serial types then you could set all these to the max value of the column which they belong to.

The following will give you a list of commands to execute:

SELECT 'select setval(''' || c.relname || ''', max(' ||
quote_ident(a.attname) || ')) from ' || d.refobjid::regclass || ';'
FROM pg_depend d
INNER JOIN pg_class c ON d.objid = c.oid INNER JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid WHERE c.relkind = 'S' AND d.refclassid = 1259;

You may like to check that returns 26 rows as you expect and verify that all those sequences do need reset before running the command.

If you're running Postgres 9.6 and using psql, you can execute the above then execute \gexec which will execute the previous result set as commands.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Adrian,

Only sequences (id) reset to 1.

Regards,
Max

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Monday, 1 May 2017 11:30 PM
To: Max Wang <mwang@1080agile.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1

On 04/30/2017 10:51 PM, Max Wang wrote:

Hi All,

We have a PostgreSQL database. There are 26 tables and we use serial
type as primary key. We had a insert error as "duplicate key value
violates unique constraint, DETAIL: Key (id)=(1) already exists." one
weeks ago. I checked and found all tables' id were reset to 1.

So to be clear:

Every row in each of the 26 tables has an id of 1?

or

Do you mean the sequences where reset to 1?

I checked database log and did not find any useful information. I am
not sure why this happen. The only script which connect to this
database is a Python script and only do normal insert/update/delete actions.

Please give me some suggestions if you happen to know something about
this issue. I appreciate any feedback you might have.

I am very new to PostgreSQL and this mail list. Please let me know if
I did not something wrong.

Thank you.

Regards,

Max

--
Adrian Klaver
adrian.klaver@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

Hi Amitabh,

Thank you for suggestion. We did not reach the limit of serial type. Some tables only have hundreds of rows.

Regards,
Max

From: Amitabh Kant [mailto:amitabhkant@gmail.com]
Sent: Monday, 1 May 2017 7:58 PM
To: Max Wang <mwang@1080agile.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1

On Mon, May 1, 2017 at 11:21 AM, Max Wang <mwang@1080agile.com<mailto:mwang@1080agile.com>> wrote:
Hi All,

We have a PostgreSQL database. There are 26 tables and we use serial type as primary key. We had a insert error as “duplicate key value violates unique constraint, DETAIL: Key (id)=(1) already exists.” one weeks ago. I checked and found all tables’ id were reset to 1.

I checked database log and did not find any useful information. I am not sure why this happen. The only script which connect to this database is a Python script and only do normal insert/update/delete actions.

Please give me some suggestions if you happen to know something about this issue. I appreciate any feedback you might have.

I am very new to PostgreSQL and this mail list. Please let me know if I did not something wrong.

Thank you.

Regards,
Max

By any chance, has it to do anything with the Cycle option of sequences:

https://www.postgresql.org/docs/9.5/static/sql-createsequence.html

On Mon, May 1, 2017 at 7:08 PM, Max Wang <mwang@1080agile.com> wrote:

Hi Adrian,

Only sequences (id) reset to 1.

Regards,
Max

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Monday, 1 May 2017 11:30 PM
To: Max Wang <mwang@1080agile.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1

On 04/30/2017 10:51 PM, Max Wang wrote:

Hi All,

We have a PostgreSQL database. There are 26 tables and we use serial
type as primary key. We had a insert error as "duplicate key value
violates unique constraint, DETAIL: Key (id)=(1) already exists." one
weeks ago. I checked and found all tables' id were reset to 1.

So to be clear:

Every row in each of the 26 tables has an id of 1?

or

Do you mean the sequences where reset to 1?

I checked database log and did not find any useful information. I am
not sure why this happen. The only script which connect to this
database is a Python script and only do normal insert/update/delete

actions.

Please give me some suggestions if you happen to know something about
this issue. I appreciate any feedback you might have.

I am very new to PostgreSQL and this mail list. Please let me know if
I did not something wrong.

Thank you.

Regards,

Max

--
Adrian Klaver
adrian.klaver@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

*But as Adrian asked, was the sequence reset to 1 for ALL tables sequences
or just 1?*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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.klaver@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

Sorry. I mean all tables’ id column were reset to 1.

Thanks.

From: Melvin Davidson [mailto:melvin6925@gmail.com]
Sent: Tuesday, 2 May 2017 9:14 AM
To: Max Wang <mwang@1080agile.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] all serial type was changed to 1

On Mon, May 1, 2017 at 7:08 PM, Max Wang <mwang@1080agile.com<mailto:mwang@1080agile.com>> wrote:
Hi Adrian,

Only sequences (id) reset to 1.

Regards,
Max

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com>]
Sent: Monday, 1 May 2017 11:30 PM
To: Max Wang <mwang@1080agile.com<mailto:mwang@1080agile.com>>; pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>
Subject: Re: [GENERAL] all serial type was changed to 1
On 04/30/2017 10:51 PM, Max Wang wrote:

Hi All,

We have a PostgreSQL database. There are 26 tables and we use serial
type as primary key. We had a insert error as "duplicate key value
violates unique constraint, DETAIL: Key (id)=(1) already exists." one
weeks ago. I checked and found all tables' id were reset to 1.

So to be clear:

Every row in each of the 26 tables has an id of 1?

or

Do you mean the sequences where reset to 1?

I checked database log and did not find any useful information. I am
not sure why this happen. The only script which connect to this
database is a Python script and only do normal insert/update/delete actions.

Please give me some suggestions if you happen to know something about
this issue. I appreciate any feedback you might have.

I am very new to PostgreSQL and this mail list. Please let me know if
I did not something wrong.

Thank you.

Regards,

Max

--
Adrian Klaver
adrian.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

But as Adrian asked, was the sequence reset to 1 for ALL tables sequences or just 1?
--
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]

Every row?

Hi Adrian,

I checked and found

is_cycled | f

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 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.klaver@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

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.klaver@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

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.

Thanks.

Regards,
Max

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Tuesday, 2 May 2017 9:31 AM
To: Max Wang <mwang@1080agile.com>; Amitabh Kant <amitabhkant@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.klaver@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

On Mon, May 1, 2017 at 7:31 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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.klaver@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

*Just to help clarify things, please execute and attach the output from the
following query.SELECT sequence_schema,
sequence_name, start_value,
maximum_value, cycle_option FROM
information_schema.sequences ORDER BY 1, 2; *

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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.klaver@aklaver.com]
Sent: Tuesday, 2 May 2017 9:31 AM
To: Max Wang <mwang@1080agile.com>; Amitabh Kant <amitabhkant@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.klaver@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

On Mon, May 1, 2017 at 7:45 PM, Adrian Klaver <adrian.klaver@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.klaver@aklaver.com]
Sent: Tuesday, 2 May 2017 9:31 AM
To: Max Wang <mwang@1080agile.com>; Amitabh Kant <amitabhkant@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.klaver@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.

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 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.klaver@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

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:melvin6925@gmail.com]
Sent: Tuesday, 2 May 2017 9:49 AM
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Max Wang <mwang@1080agile.com>; Amitabh Kant <amitabhkant@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.klaver@aklaver.com<mailto:adrian.klaver@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.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com>]
Sent: Tuesday, 2 May 2017 9:31 AM
To: Max Wang <mwang@1080agile.com<mailto:mwang@1080agile.com>>; Amitabh Kant <amitabhkant@gmail.com<mailto:amitabhkant@gmail.com>>
Cc: pgsql-general@postgresql.org<mailto: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.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org<mailto: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. [http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]