# Copyright (c) 2025, PostgreSQL Global Development Group # # Test that vacuum prunes away all dead tuples killed before OldestXmin # # This test creates a table on a primary, updates the table to generate dead # tuples for vacuum, and then, during the vacuum, uses the replica to force # GlobalVisState->maybe_needed on the primary to move backwards and precede # the value of OldestXmin set at the beginning of vacuuming the table. use strict; use warnings; use PostgreSQL::Test::Cluster; use Test::More; # Set up nodes my $node_primary = PostgreSQL::Test::Cluster->new('primary'); $node_primary->init(allows_streaming => 'physical'); # io_combine_limit is set to 1 to avoid pinning more than one buffer at a time # to ensure test determinism. $node_primary->append_conf( 'postgresql.conf', qq[ hot_standby_feedback = on autovacuum = off log_min_messages = INFO maintenance_work_mem = 64 io_combine_limit = 1 ]); $node_primary->start; my $node_replica = PostgreSQL::Test::Cluster->new('standby'); $node_primary->backup('my_backup'); $node_replica->init_from_backup($node_primary, 'my_backup', has_streaming => 1); $node_replica->start; my $test_db = "test_db"; $node_primary->safe_psql('postgres', "CREATE DATABASE $test_db"); # Save the original connection info for later use my $orig_conninfo = $node_primary->connstr(); my $table1 = "vac_horizon_floor_table"; # Long-running Primary Session A my $psql_primaryA = $node_primary->background_psql($test_db, on_error_stop => 1); # Long-running Primary Session B my $psql_primaryB = $node_primary->background_psql($test_db, on_error_stop => 1); # Our test relies on two rounds of index vacuuming for reasons elaborated # later. To trigger two rounds of index vacuuming, we must fill up the # TIDStore with dead items partway through a vacuum of the table. The number # of rows is just enough to ensure we exceed maintenance_work_mem on all # supported platforms, while keeping test runtime as short as we can. my $nrows = 2000; # Because vacuum's first pass, pruning, is where we use the GlobalVisState to # check tuple visibility, GlobalVisState->maybe_needed must move backwards # during pruning before checking the visibility for a tuple which would have # been considered HEAPTUPLE_DEAD prior to maybe_needed moving backwards but # HEAPTUPLE_RECENTLY_DEAD compared to the new, older value of maybe_needed. # # We must not only force the horizon on the primary to move backwards but also # force the vacuuming backend's GlobalVisState to be updated. GlobalVisState # is forced to update during index vacuuming. # # _bt_pendingfsm_finalize() calls GetOldestNonRemovableTransactionId() at the # end of a round of index vacuuming, updating the backend's GlobalVisState # and, in our case, moving maybe_needed backwards. # # Then vacuum's first (pruning) pass will continue and pruning will find our # later inserted and updated tuple HEAPTUPLE_RECENTLY_DEAD when compared to # maybe_needed but HEAPTUPLE_DEAD when compared to OldestXmin. # # Thus, we must force at least two rounds of index vacuuming to ensure that # some tuple visibility checks will happen after a round of index vacuuming. # To accomplish this, we set maintenance_work_mem to its minimum value and # insert and delete enough rows that we force at least one round of index # vacuuming before getting to a dead tuple which was killed after the standby # is disconnected. $node_primary->safe_psql( $test_db, qq[ CREATE TABLE ${table1}(col1 int) WITH (autovacuum_enabled=false, fillfactor=10); INSERT INTO $table1 VALUES(7); INSERT INTO $table1 SELECT generate_series(1, $nrows) % 3; CREATE INDEX on ${table1}(col1); DELETE FROM $table1 WHERE col1 = 0; INSERT INTO $table1 VALUES(7); ]); # We will later move the primary forward while the standby is disconnected. # For now, however, there is no reason not to wait for the standby to catch # up. my $primary_lsn = $node_primary->lsn('flush'); $node_primary->wait_for_catchup($node_replica, 'replay', $primary_lsn); # Test that the WAL receiver is up and running. $node_replica->poll_query_until( $test_db, qq[ SELECT EXISTS (SELECT * FROM pg_stat_wal_receiver);], 't'); # Set primary_conninfo to something invalid on the replica and reload the # config. Once the config is reloaded, the startup process will force the WAL # receiver to restart and it will be unable to reconnect because of the # invalid connection information. $node_replica->safe_psql( $test_db, qq[ ALTER SYSTEM SET primary_conninfo = ''; SELECT pg_reload_conf(); ]); # Wait until the WAL receiver has shut down and been unable to start up again. $node_replica->poll_query_until( $test_db, qq[ SELECT EXISTS (SELECT * FROM pg_stat_wal_receiver);], 'f'); # Now insert and update a tuple which will be visible to the vacuum on the # primary but which will have xmax newer than the oldest xmin on the standby # that was recently disconnected. my $res = $psql_primaryA->query_safe( qq[ INSERT INTO $table1 VALUES (99); UPDATE $table1 SET col1 = 100 WHERE col1 = 99; SELECT 'after_update'; ] ); # Make sure the UPDATE finished like($res, qr/^after_update$/m, "UPDATE occurred on primary session A"); # Open a cursor on the primary whose pin will keep VACUUM from getting a # cleanup lock on the first page of the relation. We want VACUUM to be able to # start, calculate initial values for OldestXmin and GlobalVisState and then # be unable to proceed with pruning our dead tuples. This will allow us to # reconnect the standby and push the horizon back before we start actual # pruning and vacuuming. my $primary_cursor1 = "vac_horizon_floor_cursor1"; # The first value inserted into the table was a 7, so FETCH FORWARD should # return a 7. That's how we know the cursor has a pin. # Disable index scans so the cursor pins heap pages and not index pages. $res = $psql_primaryB->query_safe( qq[ BEGIN; SET enable_bitmapscan = off; SET enable_indexscan = off; SET enable_indexonlyscan = off; DECLARE $primary_cursor1 CURSOR FOR SELECT * FROM $table1 WHERE col1 = 7; FETCH $primary_cursor1; ] ); is($res, 7, qq[Cursor query returned $res. Expected value 7.]); # Get the PID of the session which will run the VACUUM FREEZE so that we can # use it to filter pg_stat_activity later. my $vacuum_pid = $psql_primaryA->query_safe("SELECT pg_backend_pid();"); # Now start a VACUUM FREEZE on the primary. It will call vacuum_get_cutoffs() # and establish values of OldestXmin and GlobalVisState which are newer than # all of our dead tuples. Then it will be unable to get a cleanup lock to # start pruning, so it will hang. # # We use VACUUM FREEZE because it will wait for a cleanup lock instead of # skipping the page pinned by the cursor. Note that works because the target # tuple's xmax precedes OldestXmin which ensures that lazy_scan_noprune() will # return false and we will wait for the cleanup lock. # # Disable any prefetching, parallelism, or other concurrent I/O by vacuum. The # pages of the heap must be processed in order by a single worker to ensure # test stability (PARALLEL 0 shouldn't be necessary but guards against the # possibility of parallel heap vacuuming). $psql_primaryA->{stdin} .= qq[ SET maintenance_io_concurrency = 0; VACUUM (VERBOSE, FREEZE, PARALLEL 0) $table1; \\echo VACUUM ]; # Make sure the VACUUM command makes it to the server. $psql_primaryA->{run}->pump_nb(); # Make sure that the VACUUM has already called vacuum_get_cutoffs() and is # just waiting on the lock to start vacuuming. We don't want the standby to # re-establish a connection to the primary and push the horizon back until # we've saved initial values in GlobalVisState and calculated OldestXmin. $node_primary->poll_query_until( $test_db, qq[ SELECT count(*) >= 1 FROM pg_stat_activity WHERE pid = $vacuum_pid AND wait_event = 'BufferPin'; ], 't'); # Ensure the WAL receiver is still not active on the replica. $node_replica->poll_query_until( $test_db, qq[ SELECT EXISTS (SELECT * FROM pg_stat_wal_receiver);], 'f'); # Allow the WAL receiver connection to re-establish. $node_replica->safe_psql( $test_db, qq[ ALTER SYSTEM SET primary_conninfo = '$orig_conninfo'; SELECT pg_reload_conf(); ]); # Ensure the new WAL receiver has connected. $node_replica->poll_query_until( $test_db, qq[ SELECT EXISTS (SELECT * FROM pg_stat_wal_receiver);], 't'); # Once the WAL sender is shown on the primary, the replica should have # connected with the primary and pushed the horizon backward. Primary Session # A won't see that until the VACUUM FREEZE proceeds and does its first round # of index vacuuming. $node_primary->poll_query_until( $test_db, qq[ SELECT EXISTS (SELECT * FROM pg_stat_replication);], 't'); # Move the cursor forward to the next 7. We inserted the 7 much later, so # advancing the cursor should allow vacuum to proceed vacuuming most pages of # the relation. Because we set maintanence_work_mem sufficiently low, we # expect that a round of index vacuuming has happened and that the vacuum is # now waiting for the cursor to release its pin on the last page of the # relation. $res = $psql_primaryB->query_safe("FETCH $primary_cursor1"); is($res, 7, qq[Cursor query returned $res from second fetch. Expected value 7.]); # Prevent the test from incorrectly passing by confirming that we did indeed # do a pass of index vacuuming. $node_primary->poll_query_until( $test_db, qq[ SELECT index_vacuum_count > 0 FROM pg_stat_progress_vacuum WHERE datname='$test_db' AND relid::regclass = '$table1'::regclass; ], 't'); # Commit the transaction with the open cursor so that the VACUUM can finish. $psql_primaryB->query_until( qr/^commit$/m, qq[ COMMIT; \\echo commit ] ); # VACUUM proceeds with pruning and does a visibility check on each tuple. In # older versions of Postgres, pruning found our final dead tuple # non-removable (HEAPTUPLE_RECENTLY_DEAD) since its xmax is after the new # value of maybe_needed. Then heap_prepare_freeze_tuple() would decide the # tuple xmax should be frozen because it precedes OldestXmin. Vacuum would # then error out in heap_pre_freeze_checks() with "cannot freeze committed # xmax". This was fixed by changing pruning to find all # HEAPTUPLE_RECENTLY_DEAD tuples with xmaxes preceding OldestXmin # HEAPTUPLE_DEAD and removing them. # With the fix, VACUUM should finish successfully, incrementing the table # vacuum_count. $node_primary->poll_query_until( $test_db, qq[ SELECT vacuum_count > 0 FROM pg_stat_all_tables WHERE relname = '${table1}'; ] , 't'); $primary_lsn = $node_primary->lsn('flush'); # Make sure something causes us to flush $node_primary->safe_psql($test_db, "INSERT INTO $table1 VALUES (1);"); # Nothing on the replica should cause a recovery conflict, so this should # finish successfully. $node_primary->wait_for_catchup($node_replica, 'replay', $primary_lsn); ## Shut down psqls $psql_primaryA->quit; $psql_primaryB->quit; $node_replica->stop(); $node_primary->stop(); done_testing();