weird pg_statistic problem

Started by Enrico Sirolaover 14 years ago3 messagesgeneral
Jump to latest
#1Enrico Sirola
enrico.sirola@gmail.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Enrico Sirola (#1)
Re: weird pg_statistic problem

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

#3Enrico Sirola
enrico.sirola@gmail.com
In reply to: Tom Lane (#2)
Re: weird pg_statistic problem

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 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.