Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJmBR-00000000QYI-1rME for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 06:21:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xJmBO-00000009A7F-37DS for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 06:21:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJ0ip-0000000AJei-119q for pgsql-bugs@lists.postgresql.org; Tue, 20 Oct 2026 03:41:07 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJ0in-00000002u8a-026S for pgsql-bugs@lists.postgresql.org; Tue, 20 Oct 2026 03:41:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=ro63D8ONOdIyvhqQZACWDh4VJiNWlZYhdX8AQ/7zaco=; b=233/yfvAVYnfvLdTJ+TzC2acaR GLWbWdgqVF3g29pURQDC1ekBPsUi3r/LiGjwEIgVGIQ5Jl0v+8J/D5fm3ZHbSShO98xvWNj0riqQZ Ji8nd9+2bMAYXA5+UpMP/+RPTzrWwensMCZNEo6w5Lj+kad9NjTHC+apyrm8gXLiejXpchEnLlTXr 2XDYeCNA9bNoMjO8jSXLgFe2CK5Nso2DKvKbL0nMR9jgcxykLv51Zr/0WaeDwMJt79PDjIYJLlnmt mXOw0J4nxryhh8Y817xZtw5iyMYj/q2rbbzuA5rZOjir56joM4ULcpr5vaRt+4igfqio72Y6jnDkT C723bx5w==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJ0im-00000008RUU-2Iln for pgsql-bugs@lists.postgresql.org; Tue, 20 Oct 2026 03:41:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xJ0il-00000007cgM-1zMm for pgsql-bugs@lists.postgresql.org; Tue, 20 Oct 2026 03:41:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19775: Incremental backup without data checksums restores a stale visibility map page after VACUUM truncati To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: kehan5800@gmail.com Reply-To: kehan5800@gmail.com, pgsql-bugs@lists.postgresql.org Date: Tue, 20 Oct 2026 03:40:15 +0000 Message-ID: <19775-087aebfa1a709b09@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19775 Logged by: Ke Email address: kehan5800@gmail.com PostgreSQL version: 18.6 Operating system: Ubuntu 22.04 (Linux 5.15, x86_64) Description: =20 With data checksums and wal_log_hints both off, an incremental backup taken after VACUUM truncated a table restores the table's last visibility map page from the prior backup. In that copy, the bits of the truncated heap pages are still set. When the restored table grows again, the new heap pages inherit all-visible/all-frozen bits. VACUUM then skips those pages and advances relfrozenxid past tuples it never froze, and on 18.6 index-only scans return deleted rows. pg_verifybackup reports both input backups as valid. Reproduced on master (45fc51fcbf) and 18.6, built from source with ./configure --without-icu (contrib pg_visibility and amcheck installed). An earlier run on 17.11 gave the same result as 18.6. With data checksums enabled (the initdb default since 18), the same steps restore correctly (checked on master). ---- repro.sh: PGBIN=3D/usr/local/pgsql/bin sh repro.sh /tmp/vmtrunc ---- #!/bin/sh set -e W=3D$1; P=3D5450 rm -rf "$W"; mkdir -p "$W" "$PGBIN/initdb" -D "$W/src" -A trust --no-locale --no-data-checksums >/dev/null cat >> "$W/src/postgresql.conf" </dev/null q() { "$PGBIN/psql" -X -h "$W" -p "$1" -d postgres -Atq -c "$2"; } q $P "CREATE EXTENSION pg_visibility; CREATE EXTENSION amcheck" q $P "CREATE TABLE t (id int PRIMARY KEY, pad text)" q $P "INSERT INTO t SELECT g, repeat('h', 20) FROM generate_series(1, 10064) g" # 74 full heap pages q $P "INSERT INTO t SELECT 1000000 + g, repeat('x', 20) FROM generate_series(1, 20000) g" q $P "DELETE FROM t WHERE id > 1000000" q $P "VACUUM (FREEZE, TRUNCATE false) t" # empty tail pages, all-visible and all-frozen q $P "CHECKPOINT" echo "before full backup: heap blocks $(q $P "SELECT pg_relation_size('t') / 8192"), all-frozen $(q $P "SELECT count(*) FROM pg_visibility_map('t') WHERE all_frozen")" "$PGBIN/pg_basebackup" -h "$W" -p $P -D "$W/full" -c fast q $P "VACUUM t" # truncates the heap to 74 blocks echo "after VACUUM: heap blocks $(q $P "SELECT pg_relation_size('t') / 8192")" "$PGBIN/pg_basebackup" -h "$W" -p $P -D "$W/incr" -c fast --incremental "$W/full/backup_manifest" REL=3D$(q $P "SELECT pg_relation_filepath('t')") "$PGBIN/pg_ctl" -D "$W/src" -w stop >/dev/null set -- $(od -A n -t u4 -N 12 "$W/incr/$(dirname $REL)/INCREMENTAL.$(basename $REL)_vm") echo "incr/$(dirname $REL)/INCREMENTAL.$(basename $REL)_vm: $2 blocks, truncation_block_length $3" "$PGBIN/pg_combinebackup" -o "$W/restored" "$W/full" "$W/incr" echo "port =3D $((P + 1))" >> "$W/restored/postgresql.conf" "$PGBIN/pg_ctl" -D "$W/restored" -l "$W/restored.log" -w start >/dev/null P=3D$((P + 1)) q $P "INSERT INTO t SELECT 2000000 + g, 'new' FROM generate_series(1, 5000) g" q $P "VACUUM (FREEZE) t" echo "restored, after INSERT + VACUUM (FREEZE): heap blocks $(q $P "SELECT pg_relation_size('t') / 8192"), all-frozen $(q $P "SELECT count(*) FROM pg_visibility_map('t') WHERE all_frozen")" echo " relfrozenxid $(q $P "SELECT relfrozenxid FROM pg_class WHERE relname =3D 't'"), min xmin of new rows $(q $P "SELECT min(xmin::text::bigint) FROM= t WHERE id > 2000000")" echo " pages with VM all-frozen set but PD_ALL_VISIBLE clear: $(q $P "SELECT count(*) FROM pg_visibility('t') WHERE all_frozen AND NOT pd_all_visible")" echo " verify_heapam: $(q $P "SELECT count(*) || ' errors, first: ' || coalesce(min(msg), '-') FROM verify_heapam('t')")" q $P "DELETE FROM t WHERE id > 2000000" q $P "VACUUM t" echo "after DELETE of the new rows + VACUUM:" echo " seq scan: $(q $P "SET enable_indexscan =3D off; SET enable_indexonlyscan =3D off; SET enable_bitmapscan =3D off; SELECT count(*) FROM t WHERE id > 2000000")" echo " index-only scan: $(q $P "SET enable_seqscan =3D off; SET enable_bitmapscan =3D off; SET enable_indexscan =3D off; SELECT count(*) FR= OM t WHERE id > 2000000")" "$PGBIN/pg_ctl" -D "$W/restored" -w stop >/dev/null ---- end ---- Output on 18.6: before full backup: heap blocks 222, all-frozen 222 after VACUUM: heap blocks 74 incr/base/5/INCREMENTAL.16403_vm: 0 blocks, truncation_block_length 1 WARNING: page is not marked all-visible but visibility map bit is set in relation "t" page 96 restored, after INSERT + VACUUM (FREEZE): heap blocks 97, all-frozen 96 relfrozenxid 759, min xmin of new rows 758 pages with VM all-frozen set but PD_ALL_VISIBLE clear: 22 verify_heapam: 4972 errors, first: xmin 758 precedes relation freeze threshold 0:759 after DELETE of the new rows + VACUUM: seq scan: 0 index-only scan: 4972 Output on master (the 23 WARNING lines, all "page is not marked all-visible but visibility map bit is set", are shortened to one): before full backup: heap blocks 222, all-frozen 222 after VACUUM: heap blocks 74 incr/base/5/INCREMENTAL.16403_vm: 0 blocks, truncation_block_length 1 WARNING: page is not marked all-visible but visibility map bit is set restored, after INSERT + VACUUM (FREEZE): heap blocks 97, all-frozen 97 relfrozenxid 671, min xmin of new rows 670 pages with VM all-frozen set but PD_ALL_VISIBLE clear: 0 verify_heapam: 4972 errors, first: xmin 670 precedes relation freeze threshold 0:671 after DELETE of the new rows + VACUUM: seq scan: 0 index-only scan: 0 On master, the checks added by 4f7ecca84d ("Detect and fix visibility map corruption in more cases") warn and clear the stale bits, so the index-only scan is right. By then, though, VACUUM (FREEZE) has already advanced relfrozenxid past the 4972 unfrozen tuples. Expected: verify_heapam reports no errors, and both scans return 0. Cause: when VACUUM truncates the heap, visibilitymap_prepare_truncate() clears the bits of the removed heap pages in the last remaining VM page. It WAL-logs that page only if XLogHintBitIsNeeded(): https://github.com/postgres/postgres/blob/45fc51fcbf4534ea2f6f85e36f8155dbd= 5ba9851/src/backend/access/heap/visibilitymap.c#L482-L533 For XLOG_SMGR_TRUNCATE, the WAL summarizer records only the limit block of the VM fork, visibilitymap_truncation_length(), which still includes that page, so the page is never marked modified: https://github.com/postgres/postgres/blob/45fc51fcbf4534ea2f6f85e36f8155dbd= 5ba9851/src/backend/postmaster/walsummarizer.c#L1456-L1462 The incremental backup therefore sends INCREMENTAL._vm with no blocks and truncation_block_length 1. pg_combinebackup takes VM page 0 from the full backup, where the bits for heap blocks 74..221 are still set, and the restored cluster does not replay the truncation record, because that record precedes the incremental backup's start LSN. Crash recovery and plain base backups are not affected, because they replay the truncation record and so clear the bits again. One possible fix is to have the summarizer mark the last remaining VM page as modified when it summarizes the truncation. With this patch, the script restores correctly on master (verify_heapam: 0 errors, both scans 0): --- a/src/backend/postmaster/walsummarizer.c +++ b/src/backend/postmaster/walsummarizer.c @@ -1457,9 +1457,22 @@ SummarizeSmgrRecord(XLogReaderState *xlogreader, BlockRefTable *brtab) BlockRefTableSetLimitBlock(brtab, &xlrec->rlocator, MAIN_FORKNUM, xlrec->blkno); if ((xlrec->flags & SMGR_TRUNCATE_VM) !=3D 0) + { + BlockNumber vmlimit =3D visibilitymap_truncation_length(xlrec->blkno); + BlockRefTableSetLimitBlock(brtab, &xlrec->rlocator, - VISIBILITYMAP_FORKNUM, - visibilitymap_truncation_length(xlrec->blkno)); + VISIBILITYMAP_FORKNUM, vmlimit); + + /* + * visibilitymap_prepare_truncate() clears the tail bits of the + * last remaining VM page without registering it in WAL (unless + * XLogHintBitIsNeeded()), so record that page as modified. + */ + if (vmlimit > 0) + BlockRefTableMarkBlockModified(brtab, &xlrec->rlocator, + VISIBILITYMAP_FORKNUM, + vmlimit - 1); + } } } The other option is to always WAL-log that page in visibilitymap_prepare_truncate(). That question came up in the pgsql-hackers thread "Why clearing the VM doesn't require registering vm buffer in wal record" (Andres Freund, 2026-03-12, and Robert Haas, 2026-03-17, https://www.postgresql.org/message-id/t7xd6gjxdxpzdzr2uamfbyi5rlkdsikiywmtm= gwrwcsr77xyw7@i3qhbzy5rpzx). The fixes committed from that thread (ed62d26cac and later) cover VM clears in heap records, not the truncation path. Either way, summaries and backups that already exist would still be affected.