Re: pg_statistic corruption and duplicated primary keys - Mailing list pgsql-general

From Scott Marlowe
Subject Re: pg_statistic corruption and duplicated primary keys
Date
Msg-id 1157135330.4786.11.camel@state.g2switchworks.com
Whole thread Raw
In response to pg_statistic corruption and duplicated primary keys  ("Silvela, Jaime \(Exchange\)" <JSilvela@Bear.com>)
List pgsql-general
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.


pgsql-general by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: Precision of data types and functions
Next
From: "Brandon Aiken"
Date:
Subject: Re: Precision of data types and functions