| From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | toast table corrupted by vacuum - missing chunk number 0 for toast value |
| Date: | 2026-07-12 18:48:28 |
| Message-ID: | CAFj8pRDcenNE3qUf=6YsrhMLki7BbbM8fLMi9DUzLn_jbLtpGw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
My customer reported corrupted toast tables on PostgreSQL 14.20. He uses
wal archiving - so it was possible to read data from broken pages. The
corrupted rows are detected by SELECT command.
My customer prepared two instances: before corruption, after corruption.
There were more corrupted pages. All checks that I found from extensions
amcheck, pg_visibility are ok on instance before corruption. Corrupted
instances have problems with toast and toast tables have problems with
visibility map. Index on the toast table is ok.
The corrupted toast page contains four tuples. Related main table was not
updated. There was only one operation over the toast page - pruning (forced
by vacuum executed by cron).
WAL: 0000000100000D6E0000008A
rmgr: Heap2 len (rec/tot): 59/ 99, tx: 0, lsn:
D6E/8A8A6AD0, prev D6E/8A8A6A68, desc: PRUNE latestRemovedXid 0 nredirected
0 ndead 4, blkref #0: rel 1663/4267629/16352341 blk 17880358 FPW
These vacuum doesn't reports any errors
2026-04-18 05:03:13 CEST [1688]: [46307-1] user=,db=,app=,client= LOG:
checkpoint starting: time
2026-04-18 05:03:24 CEST [3760026]: [10-1]
user=postgres,db=jira_prod,app=vacuumdb,client=[local] LOG: duration:
15037.899 ms statement: VACUUM (VERBOSE, ANALYZE)
public."AO_544E33_AUDIT_LOG_ENTRY";
2026-04-18 05:03:30 CEST [3760026]: [11-1]
user=postgres,db=jira_prod,app=vacuumdb,client=[local] LOG: duration:
5511.161 ms statement: VACUUM (VERBOSE, ANALYZE) public.jiraaction;
2026-04-18 05:03:40 CEST [3760026]: [12-1]
user=postgres,db=jira_prod,app=vacuumdb,client=[local] LOG: duration:
10052.323 ms statement: VACUUM (VERBOSE, ANALYZE)
public."AO_C5D949_ISSUE_SLA";
After this operation, the toast page had only dead lp pointers and next
select fails on reported error.
Attached some data collected before and after corruption.
Can I collect some other data?
Regards
Pavel
| Attachment | Content-Type | Size |
|---|---|---|
| pg database.txt | text/plain | 1.1 KB |
| pg_waldump.grep-17880358.txt | text/plain | 242 bytes |
| pg_class.txt | text/plain | 1.9 KB |
| investigation.txt | text/plain | 8.7 KB |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | Mario González Troncoso | 2026-07-12 18:48:00 | Re: [PATCH] Add support for INSERT ... SET syntax |