Обсуждение: [BUGS] BUG #14874: Dublicate values in primary key

The following bug has been logged on the website:

Bug reference:      14874
Logged by:          Henri Ky
Email address:      sideuxb-ky.consultant@dgfip.finances.gouv.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
NOCYCLE
 
; 

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




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

On Thu, Oct 26, 2017 at 8:16 AM,
<sideuxb-ky.consultant@dgfip.finances.gouv.fr> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      14874
> Logged by:          Henri Ky
> Email address:      sideuxb-ky.consultant@dgfip.finances.gouv.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.

That's a very old point release. Many 9.4 bugs were fixed after the
9.4.4 point release, including ones that could account for this. You
ought to be trying to stay on the latest point release.

You'll clearly need to fix the problem by hand, resolving which rows
to keep. Rows can be deleted by using the hidden ctid column.

I also recommend using this tool to isolate the corruption as you fix it:

https://github.com/petergeoghegan/amcheck

There are CentOS packages available from the PGDG yum repository.

-- 
Peter Geoghegan


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