weird pg_statistic problem - Mailing list pgsql-general

From Enrico Sirola
Subject weird pg_statistic problem
Date
Msg-id DE58B340-58CF-40A5-BF4C-178A4F44BE9A@gmail.com
Whole thread Raw
Responses Re: weird pg_statistic problem  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
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



pgsql-general by date:

Previous
From: Dmitriy Igrishin
Date:
Subject: Re: PQexecParams with binary resultFormat vs BINARY CURSOR
Next
From: Gregg Jaskiewicz
Date:
Subject: FK dissapearing