VACUUM FULL missing chunk number 0 for toast value
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
https://newbiedba.wordpress.com/2015/07/07/postgresql-missing-chunk-0-for-toast-value-in-pg_toast/
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
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