Primary key duplicates

From: Zeki Mokhtarzada <zeki(at)freewebz(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Primary key duplicates
Date: 2004-08-13 02:53:20
Message-ID: Pine.LNX.4.44.0408122234060.4697-100000@freewebz.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I have a very strange bug with postgres 7.4.3. I have a table with about
15 million rows and recently, duplicate rows have started appearing.

For simplicity, let's say my table is as follows:

files
-------
fileid integer -- Primary key generated by a sequence
userid integer -- id of user that owns the file
filename character varying(255) -- name of file

Indexes: "files_pkey" primary key, bree (fileid)
"files_userid" hash (userid)

When I do:

select fileid, userid from files where userid = 1898598 order by fileid;

I get:

fileid | userid
---------+---------
3787433 | 1898598
3787433 | 1898598
3787563 | 1898598
9554275 | 1898598

Notice that 3787433 is duplicated. How could this have happened if that
column is flagged as the primary key. Even more interesting:

select oid, fileid, userid from files where userid = 1898598 order by
fileid;

oid | fileid | userid
----------+---------+---------
1573737 | 3787433 | 1898598
1573737 | 3787433 | 1898598
1573820 | 3787563 | 1898598
18612041 | 9554275 | 1898598

The rows have the same OID! So my question is how do I delete the
duplicate row. If I execute

select fileid, userid from files where fileid = 1573737;

I get:

fileid | userid
---------+---------
1573737 | 1008628

Similarly, if I try to delete both of the rows, only one of them gets
deleted, then when I select by userid, I get the other remaining one
listed. But if I select by fileid I get no rows returned.

I suspect a corrupt index is at fault here. If that's the case, a reindex
will take quite some time and will lock the table causing a long period of
downtime. Is that my only option? Any other ideas?

-Zeki

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2004-08-13 03:29:16 Re: BUG #1217: wrong date->number of week conversion
Previous Message PostgreSQL Bugs List 2004-08-12 23:33:29 BUG #1217: wrong date->number of week conversion