Re: Table with seemingly duplicated primary key values

[Melvin Davidson <melvin6925@xxxxxxxxx>]

BTW, Jim is referring to the O/S logs for hardware errors, not the PostgreSQL logs.

Also, another way of deleting the bad row would be

DELETE FROM some_table where ctid = '(79664,59)';

or

DELETE FROM some_table where ctid = '(79772,23)';

On Tue, Dec 22, 2015 at 9:44 PM, Jim Nasby <Jim.Nasby@xxxxxxxxxxxxxx> wrote:

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

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.