Re: 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.