| Lists: | pgsql-general |
|---|
| From: | "Silvela, Jaime \(Exchange\)" <JSilvela(at)Bear(dot)com> |
|---|---|
| To: | <pgsql-general(at)postgresql(dot)org> |
| Subject: | pg_statistic corruption and duplicated primary keys |
| Date: | 2006-09-01 15:41:51 |
| Message-ID: | B0D2EF413B7344489985137E6DDB6C430BF71B@whexchmb14.bsna.bsroot.bear.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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?
And, for pg_statistic in particular, is it safe to muck with it? What
should I do?
Thanks
Jaime
| Attachment | Content-Type | Size |
|---|---|---|
| Disclaimer.txt | text/plain | 965 bytes |
| From: | Scott Marlowe <smarlowe(at)g2switchworks(dot)com> |
|---|---|
| To: | "Silvela, Jaime (Exchange)" <JSilvela(at)Bear(dot)com> |
| Cc: | pgsql general <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: pg_statistic corruption and duplicated primary keys |
| Date: | 2006-09-01 18:28:50 |
| Message-ID: | 1157135330.4786.11.camel@state.g2switchworks.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | 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.