Re: Table has duplicate keys, what did I do

On Jan 28, 2008 4:26 PM, John Gateley <gateley@jriver.com> wrote:
> On Mon, 28 Jan 2008 14:11:21 -0800
> "Dann Corbit" <DCorbit@connx.com> wrote:
>
> > > -----Original Message-----
> > > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> > > owner@postgresql.org] On Behalf Of John Gateley
> > > Sent: Monday, January 28, 2008 2:04 PM
> > > To: pgsql-general@postgresql.org
> > > Subject: [GENERAL] Table has duplicate keys, what did I do
> > >
> > > Somehow I have managed to have two tables with duplicate keys.
> > > In both tables, the key is an integer, filled from a sequence.
> > > There is only 1 duplicated entry in each table: in the first
> > > table, there are two ID "1"s, and in the second table there are
> > > two ID "123456"s (the second table entry is linked to the first
> > > table's ID 1).
> >
> > Because of the nature of the values of the id's (1 and 123456) it sounds
> > very much like a manual insertion.  Is there a unique index on the
> > column?  It definitely sounds like there should be.  At any rate, I
> > guess that someone manually inserted the data.  Without a unique index
> > on the column, there is no protection against this.
>
> Yes, the id 1 definitely indicates to me that I did something.
> However, there is an index on the column: it's the primary key
> for the table. I'm not sure how I could manually insert it if
> there were an existing index, or later create the index if it
> didn't exist when I did the insert.

Are you running with fsync=off and / or hardware that lies about fsync
(ATA / SATA are notorious for this) and possibly having an emergency
power outage of some kind?  That's the most common cause of such
problems.