| 1 |
# Copyright (c) 2025, PostgreSQL Global Development Group |
| 2 |
# |
| 3 |
# Test that vacuum prunes away all dead tuples killed before OldestXmin |
| 4 |
# |
| 5 |
# This test creates a table on a primary, updates the table to generate dead |
| 6 |
# tuples for vacuum, and then, during the vacuum, uses the replica to force |
| 7 |
# GlobalVisState->maybe_needed on the primary to move backwards and precede |
| 8 |
# the value of OldestXmin set at the beginning of vacuuming the table. |
| 9 |
|
| 10 |
use strict; |
| 11 |
use warnings; |
| 12 |
use PostgreSQL::Test::Cluster; |
| 13 |
use Test::More; |
| 14 |
|
| 15 |
# Set up nodes |
| 16 |
my $node_primary = PostgreSQL::Test::Cluster->new('primary'); |
| 17 |
$node_primary->init(allows_streaming => 'physical'); |
| 18 |
|
| 19 |
# io_combine_limit is set to 1 to avoid pinning more than one buffer at a time |
| 20 |
# to ensure test determinism. |
| 21 |
$node_primary->append_conf( |
| 22 |
'postgresql.conf', qq[ |
| 23 |
hot_standby_feedback = on |
| 24 |
autovacuum = off |
| 25 |
log_min_messages = INFO |
| 26 |
maintenance_work_mem = 64 |
| 27 |
io_combine_limit = 1 |
| 28 |
]); |
| 29 |
$node_primary->start; |
| 30 |
|
| 31 |
my $node_replica = PostgreSQL::Test::Cluster->new('standby'); |
| 32 |
|
| 33 |
$node_primary->backup('my_backup'); |
| 34 |
$node_replica->init_from_backup($node_primary, 'my_backup', |
| 35 |
has_streaming => 1); |
| 36 |
|
| 37 |
$node_replica->start; |
| 38 |
|
| 39 |
my $test_db = "test_db"; |
| 40 |
$node_primary->safe_psql('postgres', "CREATE DATABASE $test_db"); |
| 41 |
|
| 42 |
# Save the original connection info for later use |
| 43 |
my $orig_conninfo = $node_primary->connstr(); |
| 44 |
|
| 45 |
my $table1 = "vac_horizon_floor_table"; |
| 46 |
|
| 47 |
# Long-running Primary Session A |
| 48 |
my $psql_primaryA = |
| 49 |
$node_primary->background_psql($test_db, on_error_stop => 1); |
| 50 |
|
| 51 |
# Long-running Primary Session B |
| 52 |
my $psql_primaryB = |
| 53 |
$node_primary->background_psql($test_db, on_error_stop => 1); |
| 54 |
|
| 55 |
# Our test relies on two rounds of index vacuuming for reasons elaborated |
| 56 |
# later. To trigger two rounds of index vacuuming, we must fill up the |
| 57 |
# TIDStore with dead items partway through a vacuum of the table. The number |
| 58 |
# of rows is just enough to ensure we exceed maintenance_work_mem on all |
| 59 |
# supported platforms, while keeping test runtime as short as we can. |
| 60 |
my $nrows = 2000; |
| 61 |
|
| 62 |
# Because vacuum's first pass, pruning, is where we use the GlobalVisState to |
| 63 |
# check tuple visibility, GlobalVisState->maybe_needed must move backwards |
| 64 |
# during pruning before checking the visibility for a tuple which would have |
| 65 |
# been considered HEAPTUPLE_DEAD prior to maybe_needed moving backwards but |
| 66 |
# HEAPTUPLE_RECENTLY_DEAD compared to the new, older value of maybe_needed. |
| 67 |
# |
| 68 |
# We must not only force the horizon on the primary to move backwards but also |
| 69 |
# force the vacuuming backend's GlobalVisState to be updated. GlobalVisState |
| 70 |
# is forced to update during index vacuuming. |
| 71 |
# |
| 72 |
# _bt_pendingfsm_finalize() calls GetOldestNonRemovableTransactionId() at the |
| 73 |
# end of a round of index vacuuming, updating the backend's GlobalVisState |
| 74 |
# and, in our case, moving maybe_needed backwards. |
| 75 |
# |
| 76 |
# Then vacuum's first (pruning) pass will continue and pruning will find our |
| 77 |
# later inserted and updated tuple HEAPTUPLE_RECENTLY_DEAD when compared to |
| 78 |
# maybe_needed but HEAPTUPLE_DEAD when compared to OldestXmin. |
| 79 |
# |
| 80 |
# Thus, we must force at least two rounds of index vacuuming to ensure that |
| 81 |
# some tuple visibility checks will happen after a round of index vacuuming. |
| 82 |
# To accomplish this, we set maintenance_work_mem to its minimum value and |
| 83 |
# insert and delete enough rows that we force at least one round of index |
| 84 |
# vacuuming before getting to a dead tuple which was killed after the standby |
| 85 |
# is disconnected. |
| 86 |
$node_primary->safe_psql( |
| 87 |
$test_db, qq[ |
| 88 |
CREATE TABLE ${table1}(col1 int) |
| 89 |
WITH (autovacuum_enabled=false, fillfactor=10); |
| 90 |
INSERT INTO $table1 VALUES(7); |
| 91 |
INSERT INTO $table1 SELECT generate_series(1, $nrows) % 3; |
| 92 |
CREATE INDEX on ${table1}(col1); |
| 93 |
DELETE FROM $table1 WHERE col1 = 0; |
| 94 |
INSERT INTO $table1 VALUES(7); |
| 95 |
]); |
| 96 |
|
| 97 |
# We will later move the primary forward while the standby is disconnected. |
| 98 |
# For now, however, there is no reason not to wait for the standby to catch |
| 99 |
# up. |
| 100 |
my $primary_lsn = $node_primary->lsn('flush'); |
| 101 |
$node_primary->wait_for_catchup($node_replica, 'replay', $primary_lsn); |
| 102 |
|
| 103 |
# Test that the WAL receiver is up and running. |
| 104 |
$node_replica->poll_query_until( |
| 105 |
$test_db, qq[ |
| 106 |
SELECT EXISTS (SELECT * FROM pg_stat_wal_receiver);], 't'); |
| 107 |
|
| 108 |
# Set primary_conninfo to something invalid on the replica and reload the |
| 109 |
# config. Once the config is reloaded, the startup process will force the WAL |
| 110 |
# receiver to restart and it will be unable to reconnect because of the |
| 111 |
# invalid connection information. |
| 112 |
$node_replica->safe_psql( |
| 113 |
$test_db, qq[ |
| 114 |
ALTER SYSTEM SET primary_conninfo = ''; |
| 115 |
SELECT pg_reload_conf(); |
| 116 |
]); |
| 117 |
|
| 118 |
# Wait until the WAL receiver has shut down and been unable to start up again. |
| 119 |
$node_replica->poll_query_until( |
| 120 |
$test_db, qq[ |
| 121 |
SELECT EXISTS (SELECT * FROM pg_stat_wal_receiver);], 'f'); |
| 122 |
|
| 123 |
# Now insert and update a tuple which will be visible to the vacuum on the |
| 124 |
# primary but which will have xmax newer than the oldest xmin on the standby |
| 125 |
# that was recently disconnected. |
| 126 |
my $res = $psql_primaryA->query_safe( |
| 127 |
qq[ |
| 128 |
INSERT INTO $table1 VALUES (99); |
| 129 |
UPDATE $table1 SET col1 = 100 WHERE col1 = 99; |
| 130 |
SELECT 'after_update'; |
| 131 |
] |
| 132 |
); |
| 133 |
|
| 134 |
# Make sure the UPDATE finished |
| 135 |
like($res, qr/^after_update$/m, "UPDATE occurred on primary session A"); |
| 136 |
|
| 137 |
# Open a cursor on the primary whose pin will keep VACUUM from getting a |
| 138 |
# cleanup lock on the first page of the relation. We want VACUUM to be able to |
| 139 |
# start, calculate initial values for OldestXmin and GlobalVisState and then |
| 140 |
# be unable to proceed with pruning our dead tuples. This will allow us to |
| 141 |
# reconnect the standby and push the horizon back before we start actual |
| 142 |
# pruning and vacuuming. |
| 143 |
my $primary_cursor1 = "vac_horizon_floor_cursor1"; |
| 144 |
|
| 145 |
# The first value inserted into the table was a 7, so FETCH FORWARD should |
| 146 |
# return a 7. That's how we know the cursor has a pin. |
| 147 |
# Disable index scans so the cursor pins heap pages and not index pages. |
| 148 |
$res = $psql_primaryB->query_safe( |
| 149 |
qq[ |
| 150 |
BEGIN; |
| 151 |
SET enable_bitmapscan = off; |
| 152 |
SET enable_indexscan = off; |
| 153 |
SET enable_indexonlyscan = off; |
| 154 |
DECLARE $primary_cursor1 CURSOR FOR SELECT * FROM $table1 WHERE col1 = 7; |
| 155 |
FETCH $primary_cursor1; |
| 156 |
] |
| 157 |
); |
| 158 |
|
| 159 |
is($res, 7, qq[Cursor query returned $res. Expected value 7.]); |
| 160 |
|
| 161 |
# Get the PID of the session which will run the VACUUM FREEZE so that we can |
| 162 |
# use it to filter pg_stat_activity later. |
| 163 |
my $vacuum_pid = $psql_primaryA->query_safe("SELECT pg_backend_pid();"); |
| 164 |
|
| 165 |
# Now start a VACUUM FREEZE on the primary. It will call vacuum_get_cutoffs() |
| 166 |
# and establish values of OldestXmin and GlobalVisState which are newer than |
| 167 |
# all of our dead tuples. Then it will be unable to get a cleanup lock to |
| 168 |
# start pruning, so it will hang. |
| 169 |
# |
| 170 |
# We use VACUUM FREEZE because it will wait for a cleanup lock instead of |
| 171 |
# skipping the page pinned by the cursor. Note that works because the target |
| 172 |
# tuple's xmax precedes OldestXmin which ensures that lazy_scan_noprune() will |
| 173 |
# return false and we will wait for the cleanup lock. |
| 174 |
# |
| 175 |
# Disable any prefetching, parallelism, or other concurrent I/O by vacuum. The |
| 176 |
# pages of the heap must be processed in order by a single worker to ensure |
| 177 |
# test stability (PARALLEL 0 shouldn't be necessary but guards against the |
| 178 |
# possibility of parallel heap vacuuming). |
| 179 |
$psql_primaryA->{stdin} .= qq[ |
| 180 |
SET maintenance_io_concurrency = 0; |
| 181 |
VACUUM (VERBOSE, FREEZE, PARALLEL 0) $table1; |
| 182 |
\\echo VACUUM |
| 183 |
]; |
| 184 |
|
| 185 |
# Make sure the VACUUM command makes it to the server. |
| 186 |
$psql_primaryA->{run}->pump_nb(); |
| 187 |
|
| 188 |
# Make sure that the VACUUM has already called vacuum_get_cutoffs() and is |
| 189 |
# just waiting on the lock to start vacuuming. We don't want the standby to |
| 190 |
# re-establish a connection to the primary and push the horizon back until |
| 191 |
# we've saved initial values in GlobalVisState and calculated OldestXmin. |
| 192 |
$node_primary->poll_query_until( |
| 193 |
$test_db, |
| 194 |
qq[ |
| 195 |
SELECT count(*) >= 1 FROM pg_stat_activity |
| 196 |
WHERE pid = $vacuum_pid |
| 197 |
AND wait_event = 'BufferPin'; |
| 198 |
], |
| 199 |
't'); |
| 200 |
|
| 201 |
# Ensure the WAL receiver is still not active on the replica. |
| 202 |
$node_replica->poll_query_until( |
| 203 |
$test_db, qq[ |
| 204 |
SELECT EXISTS (SELECT * FROM pg_stat_wal_receiver);], 'f'); |
| 205 |
|
| 206 |
# Allow the WAL receiver connection to re-establish. |
| 207 |
$node_replica->safe_psql( |
| 208 |
$test_db, qq[ |
| 209 |
ALTER SYSTEM SET primary_conninfo = '$orig_conninfo'; |
| 210 |
SELECT pg_reload_conf(); |
| 211 |
]); |
| 212 |
|
| 213 |
# Ensure the new WAL receiver has connected. |
| 214 |
$node_replica->poll_query_until( |
| 215 |
$test_db, qq[ |
| 216 |
SELECT EXISTS (SELECT * FROM pg_stat_wal_receiver);], 't'); |
| 217 |
|
| 218 |
# Once the WAL sender is shown on the primary, the replica should have |
| 219 |
# connected with the primary and pushed the horizon backward. Primary Session |
| 220 |
# A won't see that until the VACUUM FREEZE proceeds and does its first round |
| 221 |
# of index vacuuming. |
| 222 |
$node_primary->poll_query_until( |
| 223 |
$test_db, qq[ |
| 224 |
SELECT EXISTS (SELECT * FROM pg_stat_replication);], 't'); |
| 225 |
|
| 226 |
# Move the cursor forward to the next 7. We inserted the 7 much later, so |
| 227 |
# advancing the cursor should allow vacuum to proceed vacuuming most pages of |
| 228 |
# the relation. Because we set maintanence_work_mem sufficiently low, we |
| 229 |
# expect that a round of index vacuuming has happened and that the vacuum is |
| 230 |
# now waiting for the cursor to release its pin on the last page of the |
| 231 |
# relation. |
| 232 |
$res = $psql_primaryB->query_safe("FETCH $primary_cursor1"); |
| 233 |
is($res, 7, |
| 234 |
qq[Cursor query returned $res from second fetch. Expected value 7.]); |
| 235 |
|
| 236 |
# Prevent the test from incorrectly passing by confirming that we did indeed |
| 237 |
# do a pass of index vacuuming. |
| 238 |
$node_primary->poll_query_until( |
| 239 |
$test_db, qq[ |
| 240 |
SELECT index_vacuum_count > 0 |
| 241 |
FROM pg_stat_progress_vacuum |
| 242 |
WHERE datname='$test_db' AND relid::regclass = '$table1'::regclass; |
| 243 |
], 't'); |
| 244 |
|
| 245 |
# Commit the transaction with the open cursor so that the VACUUM can finish. |
| 246 |
$psql_primaryB->query_until( |
| 247 |
qr/^commit$/m, |
| 248 |
qq[ |
| 249 |
COMMIT; |
| 250 |
\\echo commit |
| 251 |
] |
| 252 |
); |
| 253 |
|
| 254 |
# VACUUM proceeds with pruning and does a visibility check on each tuple. In |
| 255 |
# older versions of Postgres, pruning found our final dead tuple |
| 256 |
# non-removable (HEAPTUPLE_RECENTLY_DEAD) since its xmax is after the new |
| 257 |
# value of maybe_needed. Then heap_prepare_freeze_tuple() would decide the |
| 258 |
# tuple xmax should be frozen because it precedes OldestXmin. Vacuum would |
| 259 |
# then error out in heap_pre_freeze_checks() with "cannot freeze committed |
| 260 |
# xmax". This was fixed by changing pruning to find all |
| 261 |
# HEAPTUPLE_RECENTLY_DEAD tuples with xmaxes preceding OldestXmin |
| 262 |
# HEAPTUPLE_DEAD and removing them. |
| 263 |
|
| 264 |
# With the fix, VACUUM should finish successfully, incrementing the table |
| 265 |
# vacuum_count. |
| 266 |
$node_primary->poll_query_until( |
| 267 |
$test_db, |
| 268 |
qq[ |
| 269 |
SELECT vacuum_count > 0 |
| 270 |
FROM pg_stat_all_tables WHERE relname = '${table1}'; |
| 271 |
] |
| 272 |
, 't'); |
| 273 |
|
| 274 |
$primary_lsn = $node_primary->lsn('flush'); |
| 275 |
|
| 276 |
# Make sure something causes us to flush |
| 277 |
$node_primary->safe_psql($test_db, "INSERT INTO $table1 VALUES (1);"); |
| 278 |
|
| 279 |
# Nothing on the replica should cause a recovery conflict, so this should |
| 280 |
# finish successfully. |
| 281 |
$node_primary->wait_for_catchup($node_replica, 'replay', $primary_lsn); |
| 282 |
|
| 283 |
## Shut down psqls |
| 284 |
$psql_primaryA->quit; |
| 285 |
$psql_primaryB->quit; |
| 286 |
|
| 287 |
$node_replica->stop(); |
| 288 |
$node_primary->stop(); |
| 289 |
|
| 290 |
done_testing(); |