Debian Codegraph @

src/test/recovery/t/018_wal_optimize.pl

raw
1
2 # Copyright (c) 2021-2025, PostgreSQL Global Development Group
3
4 # Test WAL replay when some operation has skipped WAL.
5 #
6 # These tests exercise code that once violated the mandate described in
7 # src/backend/access/transam/README section "Skipping WAL for New
8 # RelFileLocator". The tests work by committing some transactions, initiating an
9 # immediate shutdown, and confirming that the expected data survives recovery.
10 # For many years, individual commands made the decision to skip WAL, hence the
11 # frequent appearance of COPY in these tests.
12 use strict;
13 use warnings FATAL => 'all';
14
15 use PostgreSQL::Test::Cluster;
16 use PostgreSQL::Test::Utils;
17 use Test::More;
18
19 sub check_orphan_relfilenodes
20 {
21 local $Test::Builder::Level = $Test::Builder::Level + 1;
22
23 my ($node, $test_name) = @_;
24
25 my $db_oid = $node->safe_psql('postgres',
26 "SELECT oid FROM pg_database WHERE datname = 'postgres'");
27 my $prefix = "base/$db_oid/";
28 my $filepaths_referenced = $node->safe_psql(
29 'postgres', "
30 SELECT pg_relation_filepath(oid) FROM pg_class
31 WHERE reltablespace = 0 AND relpersistence <> 't' AND
32 pg_relation_filepath(oid) IS NOT NULL;");
33 is_deeply(
34 [
35 sort(map { "$prefix$_" }
36 grep(/^[0-9]+$/, slurp_dir($node->data_dir . "/$prefix")))
37 ],
38 [ sort split /\n/, $filepaths_referenced ],
39 $test_name);
40 return;
41 }
42
43 # We run this same test suite for both wal_level=minimal and replica.
44 sub run_wal_optimize
45 {
46 my $wal_level = shift;
47
48 my $node = PostgreSQL::Test::Cluster->new("node_$wal_level");
49 $node->init;
50 $node->append_conf(
51 'postgresql.conf', qq(
52 wal_level = $wal_level
53 max_prepared_transactions = 1
54 wal_log_hints = on
55 wal_skip_threshold = 0
56 #wal_debug = on
57 ));
58 $node->start;
59
60 # Setup
61 my $tablespace_dir = $node->basedir . '/tablespace_other';
62 mkdir($tablespace_dir);
63 my $result;
64
65 # Test redo of CREATE TABLESPACE.
66 $node->safe_psql(
67 'postgres', "
68 CREATE TABLE moved (id int);
69 INSERT INTO moved VALUES (1);
70 CREATE TABLESPACE other LOCATION '$tablespace_dir';
71 BEGIN;
72 ALTER TABLE moved SET TABLESPACE other;
73 CREATE TABLE originated (id int);
74 INSERT INTO originated VALUES (1);
75 CREATE UNIQUE INDEX ON originated(id) TABLESPACE other;
76 COMMIT;");
77 $node->stop('immediate');
78 $node->start;
79 $result = $node->safe_psql('postgres', "SELECT count(*) FROM moved;");
80 is($result, qq(1), "wal_level = $wal_level, CREATE+SET TABLESPACE");
81 $result = $node->safe_psql(
82 'postgres', "
83 INSERT INTO originated VALUES (1) ON CONFLICT (id)
84 DO UPDATE set id = originated.id + 1
85 RETURNING id;");
86 is($result, qq(2),
87 "wal_level = $wal_level, CREATE TABLESPACE, CREATE INDEX");
88
89 # Test direct truncation optimization. No tuples.
90 $node->safe_psql(
91 'postgres', "
92 BEGIN;
93 CREATE TABLE trunc (id serial PRIMARY KEY);
94 TRUNCATE trunc;
95 COMMIT;");
96 $node->stop('immediate');
97 $node->start;
98 $result = $node->safe_psql('postgres', "SELECT count(*) FROM trunc;");
99 is($result, qq(0), "wal_level = $wal_level, TRUNCATE with empty table");
100
101 # Test truncation with inserted tuples within the same transaction.
102 # Tuples inserted after the truncation should be seen.
103 $node->safe_psql(
104 'postgres', "
105 BEGIN;
106 CREATE TABLE trunc_ins (id serial PRIMARY KEY);
107 INSERT INTO trunc_ins VALUES (DEFAULT);
108 TRUNCATE trunc_ins;
109 INSERT INTO trunc_ins VALUES (DEFAULT);
110 COMMIT;");
111 $node->stop('immediate');
112 $node->start;
113 $result = $node->safe_psql('postgres',
114 "SELECT count(*), min(id) FROM trunc_ins;");
115 is($result, qq(1|2), "wal_level = $wal_level, TRUNCATE INSERT");
116
117 # Same for prepared transaction.
118 # Tuples inserted after the truncation should be seen.
119 $node->safe_psql(
120 'postgres', "
121 BEGIN;
122 CREATE TABLE twophase (id serial PRIMARY KEY);
123 INSERT INTO twophase VALUES (DEFAULT);
124 TRUNCATE twophase;
125 INSERT INTO twophase VALUES (DEFAULT);
126 PREPARE TRANSACTION 't';
127 COMMIT PREPARED 't';");
128 $node->stop('immediate');
129 $node->start;
130 $result = $node->safe_psql('postgres',
131 "SELECT count(*), min(id) FROM trunc_ins;");
132 is($result, qq(1|2), "wal_level = $wal_level, TRUNCATE INSERT PREPARE");
133
134 # Writing WAL at end of xact, instead of syncing.
135 $node->safe_psql(
136 'postgres', "
137 SET wal_skip_threshold = '1GB';
138 BEGIN;
139 CREATE TABLE noskip (id serial PRIMARY KEY);
140 INSERT INTO noskip (SELECT FROM generate_series(1, 20000) a) ;
141 COMMIT;");
142 $node->stop('immediate');
143 $node->start;
144 $result = $node->safe_psql('postgres', "SELECT count(*) FROM noskip;");
145 is($result, qq(20000), "wal_level = $wal_level, end-of-xact WAL");
146
147 # Data file for COPY query in subsequent tests
148 my $basedir = $node->basedir;
149 my $copy_file = "$basedir/copy_data.txt";
150 PostgreSQL::Test::Utils::append_to_file(
151 $copy_file, qq(20000,30000
152 20001,30001
153 20002,30002));
154
155 # Test truncation with inserted tuples using both INSERT and COPY. Tuples
156 # inserted after the truncation should be seen.
157 $node->safe_psql(
158 'postgres', "
159 BEGIN;
160 CREATE TABLE ins_trunc (id serial PRIMARY KEY, id2 int);
161 INSERT INTO ins_trunc VALUES (DEFAULT, generate_series(1,10000));
162 TRUNCATE ins_trunc;
163 INSERT INTO ins_trunc (id, id2) VALUES (DEFAULT, 10000);
164 COPY ins_trunc FROM '$copy_file' DELIMITER ',';
165 INSERT INTO ins_trunc (id, id2) VALUES (DEFAULT, 10000);
166 COMMIT;");
167 $node->stop('immediate');
168 $node->start;
169 $result = $node->safe_psql('postgres', "SELECT count(*) FROM ins_trunc;");
170 is($result, qq(5), "wal_level = $wal_level, TRUNCATE COPY INSERT");
171
172 # Test truncation with inserted tuples using COPY. Tuples copied after
173 # the truncation should be seen.
174 $node->safe_psql(
175 'postgres', "
176 BEGIN;
177 CREATE TABLE trunc_copy (id serial PRIMARY KEY, id2 int);
178 INSERT INTO trunc_copy VALUES (DEFAULT, generate_series(1,3000));
179 TRUNCATE trunc_copy;
180 COPY trunc_copy FROM '$copy_file' DELIMITER ',';
181 COMMIT;");
182 $node->stop('immediate');
183 $node->start;
184 $result =
185 $node->safe_psql('postgres', "SELECT count(*) FROM trunc_copy;");
186 is($result, qq(3), "wal_level = $wal_level, TRUNCATE COPY");
187
188 # Like previous test, but rollback SET TABLESPACE in a subtransaction.
189 $node->safe_psql(
190 'postgres', "
191 BEGIN;
192 CREATE TABLE spc_abort (id serial PRIMARY KEY, id2 int);
193 INSERT INTO spc_abort VALUES (DEFAULT, generate_series(1,3000));
194 TRUNCATE spc_abort;
195 SAVEPOINT s;
196 ALTER TABLE spc_abort SET TABLESPACE other; ROLLBACK TO s;
197 COPY spc_abort FROM '$copy_file' DELIMITER ',';
198 COMMIT;");
199 $node->stop('immediate');
200 $node->start;
201 $result = $node->safe_psql('postgres', "SELECT count(*) FROM spc_abort;");
202 is($result, qq(3),
203 "wal_level = $wal_level, SET TABLESPACE abort subtransaction");
204
205 # in different subtransaction patterns
206 $node->safe_psql(
207 'postgres', "
208 BEGIN;
209 CREATE TABLE spc_commit (id serial PRIMARY KEY, id2 int);
210 INSERT INTO spc_commit VALUES (DEFAULT, generate_series(1,3000));
211 TRUNCATE spc_commit;
212 SAVEPOINT s; ALTER TABLE spc_commit SET TABLESPACE other; RELEASE s;
213 COPY spc_commit FROM '$copy_file' DELIMITER ',';
214 COMMIT;");
215 $node->stop('immediate');
216 $node->start;
217 $result =
218 $node->safe_psql('postgres', "SELECT count(*) FROM spc_commit;");
219 is($result, qq(3),
220 "wal_level = $wal_level, SET TABLESPACE commit subtransaction");
221
222 $node->safe_psql(
223 'postgres', "
224 BEGIN;
225 CREATE TABLE spc_nest (id serial PRIMARY KEY, id2 int);
226 INSERT INTO spc_nest VALUES (DEFAULT, generate_series(1,3000));
227 TRUNCATE spc_nest;
228 SAVEPOINT s;
229 ALTER TABLE spc_nest SET TABLESPACE other;
230 SAVEPOINT s2;
231 ALTER TABLE spc_nest SET TABLESPACE pg_default;
232 ROLLBACK TO s2;
233 SAVEPOINT s2;
234 ALTER TABLE spc_nest SET TABLESPACE pg_default;
235 RELEASE s2;
236 ROLLBACK TO s;
237 COPY spc_nest FROM '$copy_file' DELIMITER ',';
238 COMMIT;");
239 $node->stop('immediate');
240 $node->start;
241 $result = $node->safe_psql('postgres', "SELECT count(*) FROM spc_nest;");
242 is($result, qq(3),
243 "wal_level = $wal_level, SET TABLESPACE nested subtransaction");
244
245 $node->safe_psql(
246 'postgres', "
247 CREATE TABLE spc_hint (id int);
248 INSERT INTO spc_hint VALUES (1);
249 BEGIN;
250 ALTER TABLE spc_hint SET TABLESPACE other;
251 CHECKPOINT;
252 SELECT * FROM spc_hint; -- set hint bit
253 INSERT INTO spc_hint VALUES (2);
254 COMMIT;");
255 $node->stop('immediate');
256 $node->start;
257 $result = $node->safe_psql('postgres', "SELECT count(*) FROM spc_hint;");
258 is($result, qq(2), "wal_level = $wal_level, SET TABLESPACE, hint bit");
259
260 $node->safe_psql(
261 'postgres', "
262 BEGIN;
263 CREATE TABLE idx_hint (c int PRIMARY KEY);
264 SAVEPOINT q; INSERT INTO idx_hint VALUES (1); ROLLBACK TO q;
265 CHECKPOINT;
266 INSERT INTO idx_hint VALUES (1); -- set index hint bit
267 INSERT INTO idx_hint VALUES (2);
268 COMMIT;");
269 $node->stop('immediate');
270 $node->start;
271 $result = $node->psql('postgres',);
272 my ($ret, $stdout, $stderr) =
273 $node->psql('postgres', "INSERT INTO idx_hint VALUES (2);");
274 is($ret, qq(3), "wal_level = $wal_level, unique index LP_DEAD");
275 like(
276 $stderr,
277 qr/violates unique/,
278 "wal_level = $wal_level, unique index LP_DEAD message");
279
280 # UPDATE touches two buffers for one row.
281 $node->safe_psql(
282 'postgres', "
283 BEGIN;
284 CREATE TABLE upd (id serial PRIMARY KEY, id2 int);
285 INSERT INTO upd (id, id2) VALUES (DEFAULT, generate_series(1,10000));
286 COPY upd FROM '$copy_file' DELIMITER ',';
287 UPDATE upd SET id2 = id2 + 1;
288 DELETE FROM upd;
289 COMMIT;");
290 $node->stop('immediate');
291 $node->start;
292 $result = $node->safe_psql('postgres', "SELECT count(*) FROM upd;");
293 is($result, qq(0),
294 "wal_level = $wal_level, UPDATE touches two buffers for one row");
295
296 # Test consistency of COPY with INSERT for table created in the same
297 # transaction.
298 $node->safe_psql(
299 'postgres', "
300 BEGIN;
301 CREATE TABLE ins_copy (id serial PRIMARY KEY, id2 int);
302 INSERT INTO ins_copy VALUES (DEFAULT, 1);
303 COPY ins_copy FROM '$copy_file' DELIMITER ',';
304 COMMIT;");
305 $node->stop('immediate');
306 $node->start;
307 $result = $node->safe_psql('postgres', "SELECT count(*) FROM ins_copy;");
308 is($result, qq(4), "wal_level = $wal_level, INSERT COPY");
309
310 # Test consistency of COPY that inserts more to the same table using
311 # triggers. If the INSERTS from the trigger go to the same block data
312 # is copied to, and the INSERTs are WAL-logged, WAL replay will fail when
313 # it tries to replay the WAL record but the "before" image doesn't match,
314 # because not all changes were WAL-logged.
315 $node->safe_psql(
316 'postgres', "
317 BEGIN;
318 CREATE TABLE ins_trig (id serial PRIMARY KEY, id2 text);
319 CREATE FUNCTION ins_trig_before_row_trig() RETURNS trigger
320 LANGUAGE plpgsql as \$\$
321 BEGIN
322 IF new.id2 NOT LIKE 'triggered%' THEN
323 INSERT INTO ins_trig
324 VALUES (DEFAULT, 'triggered row before' || NEW.id2);
325 END IF;
326 RETURN NEW;
327 END; \$\$;
328 CREATE FUNCTION ins_trig_after_row_trig() RETURNS trigger
329 LANGUAGE plpgsql as \$\$
330 BEGIN
331 IF new.id2 NOT LIKE 'triggered%' THEN
332 INSERT INTO ins_trig
333 VALUES (DEFAULT, 'triggered row after' || NEW.id2);
334 END IF;
335 RETURN NEW;
336 END; \$\$;
337 CREATE TRIGGER ins_trig_before_row_insert
338 BEFORE INSERT ON ins_trig
339 FOR EACH ROW EXECUTE PROCEDURE ins_trig_before_row_trig();
340 CREATE TRIGGER ins_trig_after_row_insert
341 AFTER INSERT ON ins_trig
342 FOR EACH ROW EXECUTE PROCEDURE ins_trig_after_row_trig();
343 COPY ins_trig FROM '$copy_file' DELIMITER ',';
344 COMMIT;");
345 $node->stop('immediate');
346 $node->start;
347 $result = $node->safe_psql('postgres', "SELECT count(*) FROM ins_trig;");
348 is($result, qq(9), "wal_level = $wal_level, COPY with INSERT triggers");
349
350 # Test consistency of INSERT, COPY and TRUNCATE in same transaction block
351 # with TRUNCATE triggers.
352 $node->safe_psql(
353 'postgres', "
354 BEGIN;
355 CREATE TABLE trunc_trig (id serial PRIMARY KEY, id2 text);
356 CREATE FUNCTION trunc_trig_before_stat_trig() RETURNS trigger
357 LANGUAGE plpgsql as \$\$
358 BEGIN
359 INSERT INTO trunc_trig VALUES (DEFAULT, 'triggered stat before');
360 RETURN NULL;
361 END; \$\$;
362 CREATE FUNCTION trunc_trig_after_stat_trig() RETURNS trigger
363 LANGUAGE plpgsql as \$\$
364 BEGIN
365 INSERT INTO trunc_trig VALUES (DEFAULT, 'triggered stat before');
366 RETURN NULL;
367 END; \$\$;
368 CREATE TRIGGER trunc_trig_before_stat_truncate
369 BEFORE TRUNCATE ON trunc_trig
370 FOR EACH STATEMENT EXECUTE PROCEDURE trunc_trig_before_stat_trig();
371 CREATE TRIGGER trunc_trig_after_stat_truncate
372 AFTER TRUNCATE ON trunc_trig
373 FOR EACH STATEMENT EXECUTE PROCEDURE trunc_trig_after_stat_trig();
374 INSERT INTO trunc_trig VALUES (DEFAULT, 1);
375 TRUNCATE trunc_trig;
376 COPY trunc_trig FROM '$copy_file' DELIMITER ',';
377 COMMIT;");
378 $node->stop('immediate');
379 $node->start;
380 $result =
381 $node->safe_psql('postgres', "SELECT count(*) FROM trunc_trig;");
382 is($result, qq(4),
383 "wal_level = $wal_level, TRUNCATE COPY with TRUNCATE triggers");
384
385 # Test redo of temp table creation.
386 $node->safe_psql(
387 'postgres', "
388 CREATE TEMP TABLE temp (id serial PRIMARY KEY, id2 text);");
389 $node->stop('immediate');
390 $node->start;
391 check_orphan_relfilenodes($node,
392 "wal_level = $wal_level, no orphan relfilenode remains");
393
394 return;
395 }
396
397 # Run same test suite for multiple wal_level values.
398 run_wal_optimize("minimal");
399 run_wal_optimize("replica");
400
401 done_testing();