Debian Codegraph @

src/test/recovery/t/048_vacuum_horizon_floor.pl

raw
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();