Обсуждение: [GENERAL] 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/

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

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

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.refobjsubidWHERE 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
runningthe command.
 

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

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

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

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

Every row?

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

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
ordescending 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

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

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
keyvalue 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
tableshave 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

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
duplicatekey 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
tableshave 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

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

On 05/01/2017 04:55 PM, Max Wang 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.

I would grep for setval in any of the code you have touching this
database. Also are you using any third party code?

>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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.klaver@aklaver.com]
Sent: Tuesday, 2 May 2017 9:55 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: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

On 05/01/2017 05:04 PM, Max Wang wrote:
> Hi Adrian,
>
> Thank you.  The strange things is we only use Python do insert/update/delete and do not run other SQL command.

The evidence says otherwise, which leads to:

So nothing ever pulls data out of the database with SELECTS?

There is no monitoring software in the mix?

There is no application using the database?



>
> Regards,
> Max

--
Adrian Klaver
adrian.klaver@aklaver.com