Re: Table with seemingly duplicated primary key values

On 12/22/15 1:31 PM, Melvin Davidson wrote:
> The fact that you have rows with an identical id of 2141750 verifies a
> corrupted primary index.
> To correct it, you need to decide which row to keep.
>
> So review the results of
> SELECT * FROM some_table WHERE ctid = (79664,59) OR ctid = (79772,23)
>
> DELETE FROM some_table
> WHERE id = 2147150
> AND field1 = {value for field1 of row to delete}
> AND field2 = {value for field2 of row to delete}
> ...
> ...
> AND fieldn = {value for fieldn of row to delete};
>
> Then
> REINDEX TABLE some_table;

And most importantly: review your logs for hardware errors and your
Postgres and filesystem settings for anything dangerous. Index
corruption is not normal and indicates the underlying hardware or OS is
faulty (or maybe a bug in Postgres, but that's very unlikely).

You should also consider turning on page checksums if you haven't already.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com