BUG #17384: ERROR: missing chunk number 0 for toast value 152073604 in pg_toast_2619

On Thu, Jan 27, 2022 at 08:57:28PM +0000, PG Bug reporting form wrote:

Issue: ERROR: missing chunk number 0 for toast value 152073604 in
pg_toast_2619
we are facing issue with mentioned short description error while trying any
operation on database.

We execute the various commands recommended in various blogs , given
below-
REINDEX table mytable;
REINDEX table pg_toast.pg_toast_40948;
VACUUM analyze mytable;

Even after successful execution of the above commands, still when execute
the Vacuum full its give error.

Earlier response will be appreciable.

It sounds like the statistics for a catalog table are causing an error.

From my notes from a handful of years ago, I think you'll have luck doing some
things like this:

REINDEX TABLE pg_statistic;
VACUUM VERBOSE pg_statistic;
VACUUM FULL VERBOSE pg_statistic;
REINDEX INDEX pg_toast.pg_toast_2619_index;
REINDEX SCHEMA pg_toast;
REINDEX SYSTEM postgres; -- substitute the database name for postgres

BTW, what version postgres is this ?

How large are those tables?
SELECT pg_relation_size('pg_statistic'), pg_relation_size('pg_toast.pg_toast_2619_index');

--
Justin

Justin Pryzby <pryzby@telsasoft.com> writes:

On Thu, Jan 27, 2022 at 08:57:28PM +0000, PG Bug reporting form wrote:

Issue: ERROR: missing chunk number 0 for toast value 152073604 in
pg_toast_2619
we are facing issue with mentioned short description error while trying any
operation on database.

From my notes from a handful of years ago, I think you'll have luck doing some
things like this:

The good news is that that's the toast table for pg_statistic,
which means that there is exactly no irreplaceable data in it.
If all else fails, you can just truncate pg_statistic and then
re-ANALYZE the whole database to rebuild it.

(I say "just" truncate pg_statistic, but you'll have to do that
in a standalone backend started with the -O option, else there
are permissions checks that will prevent it.)

The bad news is that there might be more corruption lurking
elsewhere. What event precipitated this?

I think we have seen some bugs that would preferentially affect
pg_statistic, but not recently. If you're not running a current
minor release, an update is advisable.

regards, tom lane

Hi,

On 2022-02-02 23:50:41 -0500, Tom Lane wrote:

(I say "just" truncate pg_statistic, but you'll have to do that
in a standalone backend started with the -O option, else there
are permissions checks that will prevent it.)

FWIW, in recent releases you can set allow_system_table_mods to on as a
superuser.

postgres[62458][1]=# TRUNCATE pg_statistic;
ERROR: 42501: permission denied: "pg_statistic" is a system catalog
LOCATION: truncate_check_rel, tablecmds.c:2159
postgres[62458][1]=# SET allow_system_table_mods = true;
SET
Time: 0.548 ms
postgres[62458][1]=# TRUNCATE pg_statistic;
TRUNCATE TABLE
postgres[62458][1]=# RESET allow_system_table_mods;
RESET

The bad news is that there might be more corruption lurking
elsewhere. What event precipitated this?

I think we have seen some bugs that would preferentially affect
pg_statistic, but not recently. If you're not running a current
minor release, an update is advisable.

Yea, hard to point somewhere concretely without more details.

Greetings,

Andres Freund