pg_statistic corruption and duplicated primary keys

On Fri, 2006-09-01 at 10:41, Silvela, Jaime (Exchange) wrote:

Lately my database crashed and I’ve had some strangeness following.

I found that some tables would have two distinct rows with identical
primary key.

Now, VACUUM complains thusly

WARNING: index "pg_statistic_relid_att_index" contains 2984 row
versions, but table contains 2983 row versions

HINT: Rebuild the index with REINDEX.

After trying to reindex, I get

ERROR: could not create unique index

DETAIL: Table contains duplicated values.

I think I’m seeing the same issue – a duplicated row.

I know this should not happen – but does it in fact come to happen
sometimes?

Is there a smarter way of dealing with it than deleting the duplicate?

How can these duplicates get produced?

Are you running with fsync off or on IDE drives (which are known to
lie)??? A crash when writing in that situation could cause this
problem. So could bad hardware in general (cpu, memory, hard drive
write errors, etc...)

And, for pg_statistic in particular, is it safe to muck with it? What
should I do?

You can delete everything in pg_statistic and the next analyze will fill
it right back up.