Re: [GENERAL] all serial type was changed to 1
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
Re: [GENERAL] all serial type was changed to 1
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 | xx_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:melvin6...@gmail.com]
Sent: Tuesday, 2 May 2017 9:49 AM
To: Adrian Klaver <adrian.kla...@aklaver.com>
Cc: Max Wang <mw...@1080agile.com>; Amitabh Kant <amitabhk...@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.kla...@aklaver.com<mailto:adrian.kla...@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.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com>]
Sent: Tuesday, 2 May 2017 9:31 AM
To: Max Wang <mw...@1080agile.com<mailto:mw...@1080agile.com>>; Amitabh Kant
<amitabhk...@gmail.com<mailto:amitabhk...@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.kla...@aklaver.com<mailto:adrian.kla...@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]
Re: [GENERAL] all serial type was changed to 1
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.kla...@aklaver.com] Sent: Tuesday, 2 May 2017 9:31 AM To: Max Wang <mw...@1080agile.com>; Amitabh Kant <amitabhk...@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.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
Re: [GENERAL] all serial type was changed to 1
Hi Adrian, I checked and found is_cycled | f 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
Re: [GENERAL] all serial type was changed to 1
Sorry. I mean all tables’ id column were reset to 1. Thanks. From: Melvin Davidson [mailto:melvin6...@gmail.com] Sent: Tuesday, 2 May 2017 9:14 AM To: Max Wang <mw...@1080agile.com> Cc: Adrian Klaver <adrian.kla...@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 <mw...@1080agile.com<mailto:mw...@1080agile.com>> wrote: Hi Adrian, Only sequences (id) reset to 1. Regards, Max -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com>] Sent: Monday, 1 May 2017 11:30 PM To: Max Wang <mw...@1080agile.com<mailto:mw...@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.kla...@aklaver.com<mailto:adrian.kla...@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]
Re: [GENERAL] all serial type was changed to 1
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:amitabhk...@gmail.com] Sent: Monday, 1 May 2017 7:58 PM To: Max Wang <mw...@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 <mw...@1080agile.com<mailto:mw...@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
Re: [GENERAL] all serial type was changed to 1
Hi Adrian, Only sequences (id) reset to 1. Regards, Max -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Monday, 1 May 2017 11:30 PM To: Max Wang <mw...@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.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
Re: [GENERAL] all serial type was changed to 1
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.row...@2ndquadrant.com]
Sent: Monday, 1 May 2017 11:05 PM
To: Max Wang <mw...@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 <mw...@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
[GENERAL] all serial type was changed to 1
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
