Duplicate values found when reindexing unique index

From: "Mason Hale" <masonhale(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Duplicate values found when reindexing unique index
Date: 2007-12-30 16:02:53
Message-ID: 8bca3aa10712300802h1c34ce69j1a1ab8b730c002e9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello --

I noticed recently some errors in my postgres log like the following:

ERROR: could not open segment 9 of relation 1663/16830/1384385 (target
block 776929292): No such file or directory

These are occurring at a rate of 1 every 2-3 days. But that rate has been
increasing.

After Googling around, I found this error could indicate index corruption.

I found that the relation with oid 1384385 was the following
index: "index_entry_on_guid_and_feed_id" UNIQUE, btree (guid, feed_id) -- on
a table named entry.

When I tried to rebuild that index, I got the following error:

prod_2=# reindex index index_entry_on_guid_and_feed_id;
ERROR: could not create unique index
DETAIL: Table contains duplicated values.

Now, if that index was in fact corrupted, the perhaps it is not such a
surprise that the duplicate values are there. The thing that makes this more
mysterious is that we have another unique index on entry(feed_id, guid) --
and I have no reason to believe it is corrupted (although I guess it still
could be).

Please note: I have previously reported another issue with duplicate values
making into into a table despite having a unique index in place (bug #3724).
This issue involves a completely different table.

We can stay running despite this error but I would very much like to track
down the cause as soon as possible. To avoid destroying any evidence, I'm
going to leave things intact and await further instructions.

Given my experience, the reliability of unique indexes is becoming somewhat
suspect. Please help. ;-)

thanks in advance,
Mason

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Usama Dar 2007-12-30 16:23:25 Re: BUG #3845: tray icon that service is running
Previous Message Bozhan 2007-12-29 22:51:32 BUG #3845: tray icon that service is running