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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
