Postgres Pro
Downloads
Home   >   mailing lists

Thread: VACUUM FULL missing chunk number 0 for toast value

VACUUM FULL missing chunk number 0 for toast value

From
Yi Sun
Date:
Hi All,

OS: CentOS 7.6
PG: 11.11

Once we tried to vacuum full a table, got the error msg "ERROR:  missing chunk number 0 for toast value", there is a doc as below for the select issue, but for our case select is no issue, 
what's the reason caused and how to fix this please? Thanks


1. vacuum full output:
VACUUM FULL VERBOSE ANALYZE application.notes;
INFO:  vacuuming "application.notes"
ERROR:  missing chunk number 0 for toast value 183500290 in pg_toast_16977

2. explain select output:
explain select * from application.notes;
                            QUERY PLAN
------------------------------------------------------------------
 Seq Scan on notes  (cost=0.00..430924.43 rows=412443 width=767)

Thanks and regards
Sun Yi

Re: VACUUM FULL missing chunk number 0 for toast value

From
Tom Lane
Date:
Yi Sun <yinan81@gmail.com> writes:
> Once we tried to vacuum full a table, got the error msg "ERROR:  missing
> chunk number 0 for toast value", there is a doc as below for the select
> issue, but for our case select is no issue,

Hmm, "SELECT *" doesn't throw any error?  That suggests that the
missing-toast-data problem is in a row that SELECT ignores because
it's dead, but VACUUM FULL is trying to copy it anyway, which would
imply that VACUUM thinks it's still visible to some old transaction.
Check to see if you've got old open transactions (particularly
prepared-and-never-committed transactions).

That's just a workaround of course.  It's not very clear whether
there's any actual data corruption here, but if you're not having
SELECT problems, it seems like whatever it is isn't critical.

            regards, tom lane