Thread: select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
From
leo xu
Date:
hello: i see a lot ,"missing chunk number 0 for toast value 96635 in pg_toast_2619",,,,,," in background aler log.select * from iclock ,no data retrun,indicate missing chunk number 0 for toast value 96635 in pg_toast_2619. i use pg_dump table,then truncate table,then pg_dump it current database,i still return this error, but i pg_dump it to others database,i can work. i deal with pg_dump the whole database,then pg_dump to new database,it can work. i want to know what reason it can happen? and another ways to deal with it ? thanks. -- View this message in context: http://postgresql.1045698.n5.nabble.com/select-table-indicate-missing-chunk-number-0-for-toast-value-96635-in-pg-toast-2619-tp5682176.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
Re: select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
From
Tom Lane
Date:
leo xu <leoxu8703@gmail.com> writes: > i see a lot ,"missing chunk number 0 for toast value 96635 in > pg_toast_2619",,,,,," in background aler log.select * from iclock ,no data > retrun,indicate missing chunk number 0 for toast value 96635 in > pg_toast_2619. There is a known bug that can cause that symptom, but it is fixed in recent update releases. What PG version are you running? If it's not at least one of the releases cited below, update. regards, tom lane Author: Tom Lane <tgl@sss.pgh.pa.us> Branch: master [08e261cbc] 2011-11-01 19:49:58 -0400 Branch: REL9_1_STABLE Release: REL9_1_2 [5e4dd5f63] 2011-11-01 19:48:43 -0400 Branch: REL9_0_STABLE Release: REL9_0_6 [7f797d27f] 2011-11-01 19:48:49 -0400 Branch: REL8_4_STABLE Release: REL8_4_10 [b05ce7550] 2011-11-01 19:48:56 -0400 Branch: REL8_3_STABLE Release: REL8_3_17 [7e03d2849] 2011-11-01 19:49:01 -0400 Branch: REL8_2_STABLE Release: REL8_2_23 [b24e6cafc] 2011-11-01 19:49:06 -0400 Fix race condition with toast table access from a stale syscache entry. If a tuple in a syscache contains an out-of-line toasted field, and we try to fetch that field shortly after some other transaction has committed an update or deletion of the tuple, there is a race condition: vacuum could come along and remove the toast tuples before we can fetch them. This leads to transient failures like "missing chunk number 0 for toast value NNNNN in pg_toast_2619", as seen in recent reports from Andrew Hammond and Tim Uckun. The design idea of syscache is that access to stale syscache entries should be prevented by relation-level locks, but that fails for at least two cases where toasted fields are possible: ANALYZE updates pg_statistic rows without locking out sessions that might want to plan queries on the same table, and CREATE OR REPLACE FUNCTION updates pg_proc rows without any meaningful lock at all. The least risky fix seems to be an idea that Heikki suggested when we were dealing with a related problem back in August: forcibly detoast any out-of-line fields before putting a tuple into syscache in the first place. This avoids the problem because at the time we fetch the parent tuple from the catalog, we should be holding an MVCC snapshot that will prevent removal of the toast tuples, even if the parent tuple is outdated immediately after we fetch it. (Note: I'm not convinced that this statement holds true at every instant where we could be fetching a syscache entry at all, but it does appear to hold true at the times where we could fetch an entry that could have a toasted field. We will need to be a bit wary of adding toast tables to low-level catalogs that don't have them already.) An additional benefit is that subsequent uses of the syscache entry should be faster, since they won't have to detoast the field. Back-patch to all supported versions. The problem is significantly harder to reproduce in pre-9.0 releases, because of their willingness to flush every entry in a syscache whenever the underlying catalog is vacuumed (cf CatalogCacheFlushRelation); but there is still a window for trouble.
Re: select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
From
leo xu
Date:
pg version is 9.1.2 -- View this message in context: http://postgresql.1045698.n5.nabble.com/select-table-indicate-missing-chunk-number-0-for-toast-value-96635-in-pg-toast-2619-tp5682176p5682273.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
Re: Re: select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
From
David Fetter
Date:
Upgrade to 9.1.3 and let us know whether that fixes the problem. Cheers, David. On Wed, May 02, 2012 at 09:58:16PM -0700, leo xu wrote: > pg version is 9.1.2 > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/select-table-indicate-missing-chunk-number-0-for-toast-value-96635-in-pg-toast-2619-tp5682176p5682273.html > Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Re: select table indicate missing chunk number 0 for toast value96635 in pg_toast_2619
From
David Rennalls
Date:
David Fetter <david <at> fetter.org> writes: > > Upgrade to 9.1.3 and let us know whether that fixes the problem. I've run into this issue as well on postgres 8.4.14. Aside from upgrading to a newer release is there any manual fixup that can be done ? Thanks, David
Re: select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
From
Kim Applegate
Date:
I have seen this issue on a slave although it was in version 9.2. I ran this select 2619::regclass; regclass -------------- pg_statistic (1 row) I was able to fix my select issue by running analyze on the database On Thu, Sep 26, 2013 at 11:47 AM, David Rennalls <drennalls@gmail.com>wrote: > David Fetter <david <at> fetter.org> writes: > > > > Upgrade to 9.1.3 and let us know whether that fixes the problem. > > I've run into this issue as well on postgres 8.4.14. Aside from upgrading > to a > newer release is there any manual fixup that can be done ? > > Thanks, > David > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
Re: select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
From
David Rennalls
Date:
On Thu, Sep 26, 2013 at 3:40 PM, Kim Applegate <kapplegate@apsalar.com> wrote: > I have seen this issue on a slave although it was in version 9.2. I ran oh ok. Looks like the issue was fixed in 8.2.23 according to these release notes http://www.postgresql.org/docs/8.2/static/release-8-2-23.html ... o Fix race condition during toast table access from stale syscache entries (Tom Lane) The typical symptom was transient errors like "missing chunk number 0 for toast value NNNNN in pg_toast_2619", where the cited toast table would always belong to a system catalog. .... .. but maybe there's a different flavour of this bug ? > this > > select 2619::regclass; > regclass > -------------- > pg_statistic > (1 row) > > > I was able to fix my select issue by running analyze on the database Yes I tried that but didn't seem to help... mydb=# ANALYZE verbose; INFO: analyzing "pg_catalog.pg_type" INFO: "pg_type": scanned 13 of 13 pages, containing 590 live rows and 0 dead rows; 590 rows in sample, 590 estimated total rows INFO: analyzing "pg_catalog.pg_attribute" INFO: "pg_attribute": scanned 78 of 78 pages, containing 4633 live rows and 0 dead rows; 4633 rows in sample, 4633 estimated total rows INFO: analyzing "information_schema.sql_features" INFO: "sql_features": scanned 7 of 7 pages, containing 649 live rows and 0 dead rows; 649 rows in sample, 649 estimated total rows ERROR: missing chunk number 0 for toast value 33255 in pg_toast_2619 - David
Re: select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
From
David Rennalls
Date:
On Thu, Sep 26, 2013 at 4:19 PM, David Rennalls <drennalls@gmail.com> wrote: > On Thu, Sep 26, 2013 at 3:40 PM, Kim Applegate <kapplegate@apsalar.com> wrote: >> I have seen this issue on a slave although it was in version 9.2. I ran > > oh ok. Looks like the issue was fixed in 8.2.23 according to these > release notes http://www.postgresql.org/docs/8.2/static/release-8-2-23.html > ... > o Fix race condition during toast table access from stale syscache > entries (Tom Lane) > > The typical symptom was transient errors like "missing chunk number > 0 for toast value NNNNN in pg_toast_2619", where the > cited toast table would always belong to a system catalog. > .... > > .. but maybe there's a different flavour of this bug ? Actually forgot to mention in my case there were filesystem issues... so that might explain it. fsck was run and did some repairs after which the error above started happening. >> this >> >> select 2619::regclass; >> regclass >> -------------- >> pg_statistic >> (1 row) >> >> >> I was able to fix my select issue by running analyze on the database > > Yes I tried that but didn't seem to help... > mydb=# ANALYZE verbose; > INFO: analyzing "pg_catalog.pg_type" > INFO: "pg_type": scanned 13 of 13 pages, containing 590 live rows and > 0 dead rows; 590 rows in sample, 590 estimated total rows > INFO: analyzing "pg_catalog.pg_attribute" > INFO: "pg_attribute": scanned 78 of 78 pages, containing 4633 live > rows and 0 dead rows; 4633 rows in sample, 4633 estimated total rows > INFO: analyzing "information_schema.sql_features" > INFO: "sql_features": scanned 7 of 7 pages, containing 649 live rows > and 0 dead rows; 649 rows in sample, 649 estimated total rows > ERROR: missing chunk number 0 for toast value 33255 in pg_toast_2619 ..Tried reindexing pg_statistic based on some other posts I saw, but was getting this error... mydb=# REINDEX TABLE pg_statistic; ERROR: could not create unique index "pg_statistic_relid_att_index" DETAIL: Table contains duplicated values. ..came across this thread http://www.spinics.net/lists/pgsql-admin/msg05911.html and I tried simply deleting all the stuff in pg_statistic, reindex then vacuum and that seems to have worked. mydb=# delete from pg_statistic; DELETE 610 mydb=# reindex table pg_statistic; REINDEX mydb=# vacuum analyze; - David