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