Обсуждение: weird pg_statistic problem

Hello,
this morning I experienced a weird problem with our pgsql database (9.0.3):

while performing a simple query, I receive the following error:

Nov 11 10:24:09 <host> postgres[23395]: [7-1] ERROR:  missing chunk number 0 for toast value 550556127 in pg_toast_2619

so I tried to find which relation is corrupted with the following query:

<DB>=# select * from pg_class pg1 inner join pg_class pg2 on pg1.oid=pg2.reltoastrelid where
pg1.relname='pg_toast_2619';
-[ RECORD 1 ]---+----------------------------
relname         | pg_toast_2619
relnamespace    | 99
reltype         | 10949
reloftype       | 0
relowner        | 10
relam           | 0
relfilenode     | 11583
reltablespace   | 0
relpages        | 137
reltuples       | 343
reltoastrelid   | 0
reltoastidxid   | 2841
relhasindex     | t
relisshared     | f
relistemp       | f
relkind         | t
relnatts        | 3
relchecks       | 0
relhasoids      | f
relhaspkey      | f
relhasexclusion | f
relhasrules     | f
relhastriggers  | f
relhassubclass  | f
relfrozenxid    | 949968032
relacl          |
reloptions      |
relname         | pg_statistic
relnamespace    | 11
reltype         | 10730
reloftype       | 0
relowner        | 10
relam           | 0
relfilenode     | 11581
reltablespace   | 0
relpages        | 550
reltuples       | 3084
reltoastrelid   | 2840
reltoastidxid   | 0
relhasindex     | t
relisshared     | f
relistemp       | f
relkind         | r
relnatts        | 22
relchecks       | 0
relhasoids      | f
relhaspkey      | f
relhasexclusion | f
relhasrules     | f
relhastriggers  | f
relhassubclass  | f
relfrozenxid    | 949968032
relacl          | {postgres=arwdDxt/postgres}
reloptions      |

apparently, the pg_statistic is having issues. Then, I performed an analyze verbose on the whole DB to reset the
statistics,and, after a while, I obtained an error: 

ERROR:  duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"
DETAIL:  Key (starelid, staattnum, stainherit)=(531526103, 7, f) already exists.

It seems analyze is violating the primary in the pg_statistic table:

<DB>=# \d pg_statistic_relid_att_inh_index
Index "pg_catalog.pg_statistic_relid_att_inh_index"
   Column   |   Type   | Definition
------------+----------+------------
 starelid   | oid      | starelid
 staattnum  | smallint | staattnum
 stainherit | boolean  | stainherit
unique, btree, for table "pg_catalog.pg_statistic"

<DB>=# \d+ pg_statistic
               Table "pg_catalog.pg_statistic"
   Column    |   Type   | Modifiers | Storage  | Description
-------------+----------+-----------+----------+-------------
 starelid    | oid      | not null  | plain    |
 staattnum   | smallint | not null  | plain    |
 stainherit  | boolean  | not null  | plain    |
 stanullfrac | real     | not null  | plain    |
 stawidth    | integer  | not null  | plain    |
 stadistinct | real     | not null  | plain    |
 stakind1    | smallint | not null  | plain    |
 stakind2    | smallint | not null  | plain    |
 stakind3    | smallint | not null  | plain    |
 stakind4    | smallint | not null  | plain    |
 staop1      | oid      | not null  | plain    |
 staop2      | oid      | not null  | plain    |
 staop3      | oid      | not null  | plain    |
 staop4      | oid      | not null  | plain    |
 stanumbers1 | real[]   |           | extended |
 stanumbers2 | real[]   |           | extended |
 stanumbers3 | real[]   |           | extended |
 stanumbers4 | real[]   |           | extended |
 stavalues1  | anyarray |           | extended |
 stavalues2  | anyarray |           | extended |
 stavalues3  | anyarray |           | extended |
 stavalues4  | anyarray |           | extended |
Indexes:
    "pg_statistic_relid_att_inh_index" UNIQUE, btree (starelid, staattnum, stainherit)
Has OIDs: no

at this point, I'm stuck. How should I proceed? Is it possible to drop/recreate the pg_statistic table? What else could
Itry? 
Thanks a lot for your help,
Enrico

Enrico Sirola <enrico.sirola@gmail.com> writes:
> this morning I experienced a weird problem with our pgsql database (9.0.3):
> while performing a simple query, I receive the following error:
> Nov 11 10:24:09 <host> postgres[23395]: [7-1] ERROR:  missing chunk number 0 for toast value 550556127 in
pg_toast_2619

Was this a transient error, or repeatable?

If it was transient, it's probably a recently-fixed issue:
http://archives.postgresql.org/pgsql-hackers/2011-10/msg01366.php
http://archives.postgresql.org/pgsql-committers/2011-11/msg00014.php

> ERROR:  duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"
> DETAIL:  Key (starelid, staattnum, stainherit)=(531526103, 7, f) already exists.

This seems unrelated.  Can you repeat this one?  If so, try REINDEX'ing
that index and see if the problem goes away.

It'd be worth your while to update to 9.0.5 --- we fixed a fair number
of potential data-corruption issues since January.

            regards, tom lane

Hello Tom,

Il giorno 11/nov/2011, alle ore 22.05, Tom Lane ha scritto:

> Enrico Sirola <enrico.sirola@gmail.com> writes:
>> this morning I experienced a weird problem with our pgsql database (9.0.3):
>> while performing a simple query, I receive the following error:
>> Nov 11 10:24:09 <host> postgres[23395]: [7-1] ERROR:  missing chunk number 0 for toast value 550556127 in
pg_toast_2619
>
> Was this a transient error, or repeatable?
>
> If it was transient, it's probably a recently-fixed issue:
> http://archives.postgresql.org/pgsql-hackers/2011-10/msg01366.php
> http://archives.postgresql.org/pgsql-committers/2011-11/msg00014.php

apparently, it's transient

>> ERROR:  duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"
>> DETAIL:  Key (starelid, staattnum, stainherit)=(531526103, 7, f) already exists.
>
> This seems unrelated.  Can you repeat this one?  If so, try REINDEX'ing
> that index and see if the problem goes away.
>
> It'd be worth your while to update to 9.0.5 --- we fixed a fair number
> of potential data-corruption issues since January.

while examinigs the db state, I found many other inconsistencies here and there (e.g. tables with multiple records with
thesame PK), at the end I restored a backup.  
I'll be installing today 9.0.5 and let you know if it happens again. Thanks a lot for your help,
Enrico

P.S.
by the way, I have a streaming replica server that has been corrupted as well.