From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> |
Cc: | Aleksander Łukasz <allllllx(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Table with seemingly duplicated primary key values |
Date: | 2015-12-23 02:50:46 |
Message-ID: | CANu8FiwoQHuEKvwSmjC-2OpFpSizMgS=di9pFccCgGdH915KPw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | oleg yusim | 2015-12-23 02:58:24 | Re: Shared system resources |
Previous Message | Jim Nasby | 2015-12-23 02:48:02 | Re: Shared system resources |