6

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

  • 2
    So, does the query SELECT id, COUNT(*) AS cnt FROM tableName GROUP BY id HAVING COUNT(*)>1; actually return rows? – ypercubeᵀᴹ Apr 7 '14 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 Apr 7 '14 at 12:35
  • 1
    What's ID's datatype and how did you find those duplicates? – Mat Apr 7 '14 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 Apr 7 '14 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. – a_horse_with_no_name Apr 7 '14 at 14:00
4

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!

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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