Re: Table with seemingly duplicated primary key values

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(dot)Nasby(at)bluetreble(dot)com> 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.