weird pg_statistic problem

From: Enrico Sirola <enrico(dot)sirola(at)gmail(dot)com>
To: "PostgreSQL General (list)" <pgsql-general(at)postgresql(dot)org>
Subject: weird pg_statistic problem
Date: 2011-11-11 12:03:47
Message-ID: DE58B340-58CF-40A5-BF4C-178A4F44BE9A@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 I try?
Thanks a lot for your help,
Enrico

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gregg Jaskiewicz 2011-11-11 12:25:53 FK dissapearing
Previous Message Dmitriy Igrishin 2011-11-11 11:00:14 Re: PQexecParams with binary resultFormat vs BINARY CURSOR