Re: [GENERAL] all serial type was changed to 1

2017-05-01 Thread Adrian Klaver 

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

2017-05-01 Thread Max Wang 
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

2017-05-01 Thread Melvin Davidson 
On Mon, May 1, 2017 at 7:55 PM, Max Wang <mw...@1080agile.com> 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.
>
>
>
> 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>
> 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]
> 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
>
>
>
> *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. *
>


*>I already reset id to correct value after this happen. *
*Then as Adrian or I suggested, someone did a manual*  *"SELECT
setval('seqname', 1);"  For every sequence. *

*Since you have reset to correct values already, I seriously doubt we can
trace this any furthur.*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] all serial type was changed to 1

2017-05-01 Thread Adrian Klaver 

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

2017-05-01 Thread Max Wang 
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

2017-05-01 Thread Adrian Klaver 

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

2017-05-01 Thread Melvin Davidson 
On Mon, May 1, 2017 at 7:45 PM, Adrian Klaver <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]
>> 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
>


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

Re: [GENERAL] all serial type was changed to 1

2017-05-01 Thread Adrian Klaver 

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

2017-05-01 Thread Max Wang 
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

2017-05-01 Thread Melvin Davidson 
On Mon, May 1, 2017 at 7:31 PM, Adrian Klaver 
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.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
>










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

Re: [GENERAL] all serial type was changed to 1

2017-05-01 Thread Adrian Klaver 

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

2017-05-01 Thread Max Wang 
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

2017-05-01 Thread Rob Sargent 

Every row?


On 05/01/2017 05:17 PM, Max Wang wrote:


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

Re: [GENERAL] all serial type was changed to 1

2017-05-01 Thread Max Wang 
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

2017-05-01 Thread Adrian Klaver 

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

2017-05-01 Thread Melvin Davidson 
On Mon, May 1, 2017 at 7:08 PM, Max Wang <mw...@1080agile.com> wrote:

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



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

Re: [GENERAL] all serial type was changed to 1

2017-05-01 Thread Max Wang 
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

2017-05-01 Thread Max Wang 
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

2017-05-01 Thread Max Wang 
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

Re: [GENERAL] all serial type was changed to 1

2017-05-01 Thread Adrian Klaver 

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

2017-05-01 Thread David Rowley 
On 1 May 2017 at 17:51, Max Wang  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

Re: [GENERAL] all serial type was changed to 1

2017-05-01 Thread Amitabh Kant 
On Mon, May 1, 2017 at 11:21 AM, 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.
>
>
>
> 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

2017-05-01 Thread Peter Geoghegan 
On Sun, Apr 30, 2017 at 10:51 PM, Max Wang  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

[GENERAL] all serial type was changed to 1

2017-04-30 Thread Max Wang 
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