Обсуждение: Table with seemingly duplicated primary key values

Hi,

a table in our database with about 3 million rows ended up in a state

where its seems to have duplicated entries (duplicated primary key values):

# \d some_table;

                                   Table "public.some_table"

 Column |            Type             |                        Modifiers

--------+-----------------------------+---------------------------------------------------------

 id     | integer                     | not null default nextval('some_table_id_seq'::regclass)

 field1 | character varying(40)       |

 field2 | character varying(128)      |

 ts     | timestamp without time zone |

Indexes:

    "some_table_pkey" PRIMARY KEY, btree (id)

    "ix_some_table_field1" btree (field1)

    "ix_some_table_field2" btree (field2)

    "ix_some_table_ts" btree (ts)

# select id, field1, field2 from some_table where field1 is null and field2 is not null;

   id    | field1 |            field2

---------+--------+----------------------------------

 2141750 |        | some_value2

(1 row)

# select id, field1, field2 from some_table where id = 2141750;

   id   | field1       |    field2

---------+-------------+----------------------------------

2141750 | some_value1   | some_value2

(1 row)

Another way this manifests itself it that running this:

# update some_table

   set field2 = field1

where

   id = 2141750;

works perfectly fine (but doesn't change the result of the first two queries above),

but this results in an error:

# update some_table

   set field2 = field1

where

   field1 is not null

   and field2 is null

   and ts between '2015-12-01' and '2015-12-02';

ERROR:  duplicate key value violates unique constraint "some_table_pkey"

DETAIL:  Key (id)=(2141750) already exists.

Do you have any idea what could be happening and what measures should be

undertaken to fix this issue? Thanks.

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