ERROR: missing chunk number 0 for toast value 10775681 in pg_toast_10749695

TimescaleDB and PostgreSQL
1

Hi
I had error during making backup

pg_dump: Dumping the contents of table “_hyper_3_833_chunk” failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: missing chunk number 0 for toast value 10775681 in pg_toast_10749695
pg_dump: The command was: COPY _timescaledb_internal._hyper_3_833_chunk (request_time, request_id, widget_id, dashboard_id, username, query, result_size) TO stdout;
pg_dumpall: pg_dump failed on database “some_db”, exiting

vacuum full doesnt repair
INFO: vacuuming “_timescaledb_internal._hyper_3_833_chunk”
ERROR: missing chunk number 0 for toast value 10775681 in pg_toast_10749695

According from this script GitHub - xyhtac/postgres_table_recovery: Tool for quick search and fix of corrupted fields in large Postgres tables it is nessecary to find corupted data and delete it.
I found these entries which was problematic and I’ve deleted it.
Unfortunatelly still has error during selecting data in this area

select * from cache_read where request_time > ‘2023-01-24 14:41:02.559 +0100’ and request_time < ‘2023-01-24 14:52:38.831 +0100’;

ERROR: missing chunk number 0 for toast value 10775681 in pg_toast_10749695

Any ideas how to resolve this problem?
PostgreSQL: 11.16
timescaledb: 1.6.0

I can recovery data from backup but firstly I want to repair

Edited: I tried reindex without success

2

Hello @Pawel_S, here is a small function that can potentially help you to find the corrupted records:

CREATE OR REPLACE FUNCTION cache_read_corruption_check()
RETURNS bool LANGUAGE plpgsql AS $$
DECLARE
    v_row record;
    found_error bool = false;
BEGIN
    FOR v_row IN SELECT * FROM cache_read order by 1 asc
    LOOP
       BEGIN
            perform length(v_row.bio); -- using the toast triggers the error
       EXCEPTION WHEN internal_error OR data_corrupted OR index_corrupted THEN
           RAISE NOTICE 'failed to work with the toast of id: % - %', v_row.id, v_row.ctid;
           found_error = true;
       END;
    END LOOP;
    RETURN found_error;
END $$;

It can probably help you to find the remaining records.

1 Like
3

SQL Error [42703]: ERROR: record “v_row” has no field “bio”
Where: SQL statement “SELECT length(v_row.bio)”
PL/pgSQL function cache_read_corruption_check() line 9 at PERFORM

FYI. cache_read has 12909888 records

corupted data are in this segment request_time > ‘2023-01-24 14:41:02.559 +0100’ and request_time < ‘2023-01-24 14:52:38.831 +0100’;

4

Recently I’ve have found another corrupted data and delete it.
Quering suspicious area of data without error
select * from cache_read where request_time > ‘2023-01-23 14:41:02.559 +0100’
backup has been successfuly created. It seems that issue was not related with timescaledb. Abut anyway thanks for help. Closing …

1 Like
5

Probably the bio should be replaced by your text/toast column.

Happy to know that it worked for you Paweł!

6

Thanks a ton! The option saved my life and lot of time

1 Like
7

Here is a utility that solves this problem:

8

Hey @dokalovd , thanks for sharing. It seems it’s your new project. Is it full compatible with Timescaledb?

9

Hello!
I haven’t tried my chunky tool on Timesscaledb, and I haven’t had to.
Since Timescaledb is an extension for Postgres, I think that my utility will also work with Timescaledb.