diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build index b6ac9dd07a7..6ee9462bef0 100644 --- a/src/test/recovery/meson.build +++ b/src/test/recovery/meson.build @@ -69,6 +69,7 @@ tests += { 't/058_shutdown_crash_restart.pl', 't/059_remote_apply_status_interval.pl', 't/060_multixact_crashed_updater.pl', + 't/062_skip_fpi.pl', ], }, } diff --git a/src/test/recovery/t/062_skip_fpi.pl b/src/test/recovery/t/062_skip_fpi.pl new file mode 100644 index 00000000000..fb2793ea02d --- /dev/null +++ b/src/test/recovery/t/062_skip_fpi.pl @@ -0,0 +1,96 @@ + +# Copyright (c) 2026, PostgreSQL Global Development Group + +# Checks that recovery prefetch skips block references that follow a +# not-yet-replayed full page image (skip_fpi), and does not skip them once +# that image has been replayed. +use strict; +use warnings FATAL => 'all'; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +my $primary = PostgreSQL::Test::Cluster->new('primary'); +$primary->init(has_archiving => 1, allows_streaming => 1); +$primary->append_conf('postgresql.conf', "autovacuum = off\n"); +$primary->start; + +# recovery_prefetch = on is rejected where read-ahead advice is unavailable. +my $ret = $primary->psql('postgres', 'ALTER SYSTEM SET recovery_prefetch = on', + on_error_stop => 0, stderr => \my $err); +plan skip_all => 'prefetching is not supported on this platform' if $ret != 0; +$primary->reload; + +# One tuple per page, with room for one more tuple version on the same page. +my $nrows = 100; +$primary->safe_psql( + 'postgres', qq( + CREATE TABLE t (id int, v int, pad text) WITH (fillfactor = 50); + ALTER TABLE t ALTER COLUMN pad SET STORAGE PLAIN; + INSERT INTO t SELECT i, 0, repeat('x', 2700) FROM generate_series(1, $nrows) i; + CREATE TABLE u (id int, v int, pad text) WITH (fillfactor = 50); + ALTER TABLE u ALTER COLUMN pad SET STORAGE PLAIN; + INSERT INTO u SELECT i, 0, repeat('x', 2700) FROM generate_series(1, 4 * $nrows) i; + CHECKPOINT;)); +$primary->backup('backup'); + +# Phase 1: the first change to each page after the backup writes a full page +# image, and the next change to the same page follows it right away. +$primary->safe_psql( + 'postgres', qq( + DO \$\$ BEGIN + FOR i IN 1..$nrows LOOP + UPDATE t SET v = v + 1 WHERE id = i; + UPDATE t SET v = v + 1 WHERE id = i; + END LOOP; + END \$\$;)); + +# Phase 2: lots of unrelated WAL, then touch the same pages of t again, while +# first changes to the pages of u keep new images pending. The images of t +# were replayed long ago, so these references must not be skipped. +$primary->safe_psql( + 'postgres', qq( + CREATE TABLE filler (a int, b text); + INSERT INTO filler SELECT i, md5(i::text) FROM generate_series(1, 200000) i; + DO \$\$ BEGIN + FOR r IN 0..3 LOOP + FOR i IN 1..$nrows LOOP + UPDATE t SET v = v + 1 WHERE id = i; + UPDATE u SET v = v + 1 WHERE id = r * $nrows + i; + END LOOP; + END LOOP; + END \$\$;)); +my $expected = $primary->safe_psql('postgres', 'SELECT sum(v) FROM t'); +$primary->safe_psql('postgres', 'SELECT pg_switch_wal()'); +my $walfile = $primary->safe_psql('postgres', + 'SELECT pg_walfile_name(pg_current_wal_lsn() - 1)'); +$primary->poll_query_until('postgres', + "SELECT last_archived_wal >= '$walfile' FROM pg_stat_archiver") + or die "timed out waiting for WAL to be archived"; +my $end_lsn = $primary->safe_psql('postgres', 'SELECT pg_current_wal_lsn()'); + +my $standby = PostgreSQL::Test::Cluster->new('standby'); +$standby->init_from_backup($primary, 'backup', has_restoring => 1, standby => 1); +$standby->append_conf('postgresql.conf', qq( +recovery_prefetch = on +maintenance_io_concurrency = 32 +shared_buffers = 1MB +)); +$standby->start; +$standby->poll_query_until('postgres', + "SELECT pg_last_wal_replay_lsn() >= '$end_lsn'") + or die "timed out waiting for replay"; + +is($standby->safe_psql('postgres', 'SELECT sum(v) FROM t'), + $expected, 'replayed data matches the primary'); + +my $skip_fpi = $standby->safe_psql('postgres', + 'SELECT skip_fpi FROM pg_stat_recovery_prefetch'); +note "skip_fpi = $skip_fpi"; +# Phase 1 gives well under one skipped reference per row. Phase 2 would add +# about 4 * $nrows if stale history entries were still honored. +cmp_ok($skip_fpi, '>=', $nrows / 2, 'references after a pending FPI are skipped'); +cmp_ok($skip_fpi, '<=', $nrows, 'references after a replayed FPI are not skipped'); + +done_testing();