Re: BUG #14874: Dublicate values in primary key

Hello again,

With the uncleaned files left in the tablespaces after database restore
operation, this let us perform few futher tests.
We tried to duplicate manually data files used by one table and restart
the cluster, the reindex of the table has failed with duplicate key error.
As a conclusion, PostgreSQL does not check data integrity during cluster
restart, and if data files are altered, the database is corrupted
without any notice.
It would be better that PostgreSQL would report error related to data
integrity issue during database lifetime in the futur versions (this is
not implemented in version 9.6).

Here below the tests we have perfomed in detail:

[postgres] $ ls -lh
/u05/pgsql/9.4/data/mydb001/tbs_ges_dat/PG_9.4_201409291/17273
total 1,1G
-rw------- 1 postgres postgres 1,0G 31 oct.  13:48 17274
-rw------- 1 postgres postgres  11M 31 oct.  13:48 17274.1
-rw------- 1 postgres postgres 280K 31 oct.  13:46 17274_fsm

[postgres] mydb=# select count(*) from anomalie;
  count
----------
 18000000

[postgres] mydb=# \d+
                               Liste des relations
 Schéma |        Nom         |   Type   | Propriétaire | Taille   |
Description
------------+--------------------+----------+--------------+------------+-------------
 public | anomalie           | table    | postgres     | 1034 MB    |
 public | anomalie_id_seq    | séquence | postgres     | 8192 bytes |
 public | pg_buffercache     | vue      | postgres     | 0 bytes    |
 public | pg_stat_statements | vue      | postgres     | 0 bytes    |

[postgres] mydb=# reindex table anomalie;
REINDEX

[postgres] $ pg_ctl stop ...

[postgres] $ cd
/u05/pgsql/9.4/data/mydb001/tbs_ges_dat/PG_9.4_201409291/17273
[postgres] $ cp 17274.1 17274.2
[postgres] $ cp 17274 17274.1

[postgres] $ pg_ctl start ...

[postgres] $ ls -lh
/u05/pgsql/9.4/data/mydb001/tbs_ges_dat/PG_9.4_201409291/17273
total 2,1G
-rw------- 1 postgres postgres 1,0G 31 oct.  13:58 17274
-rw------- 1 postgres postgres 1,0G 31 oct.  13:59 17274.1
-rw------- 1 postgres postgres  11M 31 oct.  13:59 17274.2
-rw------- 1 postgres postgres 280K 31 oct.  13:46 17274_fsm

[postgres] mydb=# select count(*) from anomalie;
  count
----------
 35825792

[postgres] mydb=# \d+
                               Liste des relations
 Schéma |        Nom         |   Type   | Propriétaire | Taille   |
Description
--------+--------------------+----------+--------------+------------+-------------
 public | anomalie           | table    | postgres     | 2058 MB    |
 public | anomalie_id_seq    | séquence | postgres     | 8192 bytes |
 public | pg_buffercache     | vue      | postgres     | 0 bytes    |
 public | pg_stat_statements | vue      | postgres     | 0 bytes    |

[postgres] mydb=# reindex table anomalie;
ERROR:  could not create unique index "anomalie_pk"
DÉTAIL : Key (id_anomalie)=(1) is duplicated.

Regards
Henri