Re: BUG #7760: records with duplicate primary keys

On 20/12/2012 1:22 AM, acummings(at)aperiogroup(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7760
> Logged by: Alan Cummings
> Email address: acummings(at)aperiogroup(dot)com
> PostgreSQL version: 9.1.7
> Operating system: Ubuntu Linux 10.04.1
> Description:
>
> We found 6 duplicated primary keys in a table. All fields were identical in
> the duplicated records, including the primary key. (Total records ~ 22,000).
> We recently updated from 9.1.2 to 9.1.7, so the issue may have been present
> in 9.1.2.
Have you had your database running with fsync=off at any point?

What storage system is the database on? RAID array? If so, details -
drive types, controller if any, writeback/writethrough, BBU enabled,etc?
SSD, and if so which SSD?

Has the DB host unexpectedly powered off or rebooted at any point? This
should never cause a problem unless you run with unsafe settings like
fsync=off, but is important to know.

Did you REINDEX as part of the upgrade to 9.1.7? A corrupt unique index
could hide duplicate records, and a reindex might well cause the problem
to become apparent.

> We have fixed the issue in our table by backing up the table in pgAdmin to a
> plain file, deleting the duplicated records in the text file, then restoring
> the table.
>
> I can send the original backup with the duplicated records if that would be
> helpful -- it is about 11MB.
Is the backup a `pg_dump`? Or a raw copy of the database directory?

The pg_dump output won't usually provide much information, it's the
actual database on disk that's significant.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services