BUG #14874: Dublicate values in primary key

From: sideuxb-ky(dot)consultant(at)dgfip(dot)finances(dot)gouv(dot)fr
To: pgsql-bugs(at)postgresql(dot)org
Cc: sideuxb-ky(dot)consultant(at)dgfip(dot)finances(dot)gouv(dot)fr
Subject: BUG #14874: Dublicate values in primary key
Date: 2017-10-26 15:16:11
Message-ID: 20171026151611.1471.44846@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14874
Logged by: Henri Ky
Email address: sideuxb-ky(dot)consultant(at)dgfip(dot)finances(dot)gouv(dot)fr
PostgreSQL version: 9.4.4
Operating system: CentOS release 6.5
Description:

We have serious issue with duplicate values in primary key with our database
running in PostgreSQL 9.4.4.
This only occured during about 19 hours and in on table. There were no
restart of the base before and after the issue, and no error found in
PostgreSQL logs.

We use sequence to generate automatically the ID of the PK for each data
row.
Only the ID of the PK are duplicated, whereas the data of their rows are
different.

We have tried to reindex the PK, but failed the following error:

reindex index anomalie_pk;
ERROR: could not create unique index "anomalie_pk"
D√ČTAIL : Key (id_anomalie)=(xxxxxxx) is duplicated.

And we have about 260000 data records with duplicated key.

Could you help us to fix the issue?

We create the table and the sequence with the following commandes:

CREATE TABLE anomalie
(
id_anomalie BIGINT NOT NULL,
id_fichier_collecte BIGINT NOT NULL,
id_anomalie_reference BIGINT NOT NULL,
nombre INTEGER
)
TABLESPACE ges_dat;

CREATE SEQUENCE anomalie_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1
NO CYCLE
;

ALTER SEQUENCE anomalie_id_seq OWNED BY anomalie.id_anomalie;
ALTER TABLE ONLY anomalie ALTER COLUMN id_anomalie SET DEFAULT
nextval('anomalie_id_seq');
ALTER TABLE anomalie ADD CONSTRAINT anomalie_pk PRIMARY KEY (id_anomalie)
USING INDEX TABLESPACE ges_idx;

Regards

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Geoghegan 2017-10-26 16:53:55 Re: BUG #14874: Dublicate values in primary key
Previous Message Kyotaro HORIGUCHI 2017-10-26 10:05:51 Re: [BUGS] Bug in Physical Replication Slots (at least 9.5)?