8

To my big (and worrying) surprise I just realized that my table with millions of rows, has a few handful of rows, where the primary key (ID) is a duplicate! I don't understand how this can ever happen, and how I might prevent it in the future?

The column holding the primary key is, and has always been, subject to a CONSTRAINT fruits_pkey PRIMARY KEY(id);

I'm running postgreql 9.3.4 on ubuntu.

UPDATE

@Mat: Datatype is integer

@ypercube: Yes, select count(*) from (select count(*) from fruits group by id having count(*) > 1) as t1 returns 41.

@Craig: Yes, I have done a failover before, and my slave is actually 9.3.3

Improve this question
12
  • 2
    So, does the query SELECT id, COUNT(*) AS cnt FROM tableName GROUP BY id HAVING COUNT(*)>1; actually return rows?
    – ypercubeᵀᴹ
    Commented Apr 7, 2014 at 12:25
  • 3
    Have you failed over to a streaming replica slave while running an earlier version of 9.3? (This symptom means index corruption; there was a known bug in 9.3 before 9.3.4 that could cause problems with replicas.) Also, go read wiki.postgresql.org/wiki/Corruption
    – Craig Ringer
    Commented Apr 7, 2014 at 12:35
  • 1
    What's ID's datatype and how did you find those duplicates?
    – Mat
    Commented Apr 7, 2014 at 12:35
  • 2
    @ypercube Even if it doesn't, they might still be there. You'd need to force a seqscan (set enable_indexscan = off; set enable_indexonlyscan = false;) to reliably see what's in the heap.
    – Craig Ringer
    Commented Apr 7, 2014 at 12:38
  • 1
    You need to upgrade your slave to 9.3.4 and then take a new base backup (i.e. re-initialize the slave) if I'm not mistaken.
    – user1822
    Commented Apr 7, 2014 at 14:00

3 Answers 3

Reset to default
6

It was all caused by the bug described and fixed in this release: http://www.postgresql.org/about/news/1506/

It caused us a lot of trouble!

Improve this answer
1

There is a free utility here for fixing duplicate keys that violate PRIMARY KEY or UNIQUE constraints in PostgreSQL as a result of bug #11141. It works by removing duplicates that are not accessible from the constraint index.

Improve this answer
0

I had a similar issue right now. Duplicate rows despite a PK index. I could also not rebuild the index due to that.

I fixed it by making a group select with count(*) over the primary key columns with a where statement that forced a sequential table scan.

This gave me all duplicates and I could delete them to finally reindex the primary key. Hope this never happens ever again.

SELECT pk_field_1, pk_field_2, count(*)
FROM the_table
WHERE some_non_empty_other_column > ''
GROUP BY pk_field_1, pk_field_2
ORDER BY count DESC
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.