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 I try?
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_2619Was 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 the same 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.