diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/isolation/expected/vacuum-no-cleanup-lock.out | 189 | ||||
| -rw-r--r-- | src/test/isolation/expected/vacuum-reltuples.out | 67 | ||||
| -rw-r--r-- | src/test/isolation/isolation_schedule | 2 | ||||
| -rw-r--r-- | src/test/isolation/specs/vacuum-no-cleanup-lock.spec | 150 | ||||
| -rw-r--r-- | src/test/isolation/specs/vacuum-reltuples.spec | 49 |
5 files changed, 340 insertions, 117 deletions
diff --git a/src/test/isolation/expected/vacuum-no-cleanup-lock.out b/src/test/isolation/expected/vacuum-no-cleanup-lock.out new file mode 100644 index 00000000000..f7bc93e8f1c --- /dev/null +++ b/src/test/isolation/expected/vacuum-no-cleanup-lock.out @@ -0,0 +1,189 @@ +Parsed test spec with 4 sessions + +starting permutation: vacuumer_pg_class_stats dml_insert vacuumer_nonaggressive_vacuum vacuumer_pg_class_stats +step vacuumer_pg_class_stats: + SELECT relpages, reltuples FROM pg_class WHERE oid = 'smalltbl'::regclass; + +relpages|reltuples +--------+--------- + 1| 20 +(1 row) + +step dml_insert: + INSERT INTO smalltbl SELECT max(id) + 1 FROM smalltbl; + +step vacuumer_nonaggressive_vacuum: + VACUUM smalltbl; + +step vacuumer_pg_class_stats: + SELECT relpages, reltuples FROM pg_class WHERE oid = 'smalltbl'::regclass; + +relpages|reltuples +--------+--------- + 1| 21 +(1 row) + + +starting permutation: vacuumer_pg_class_stats dml_insert pinholder_cursor vacuumer_nonaggressive_vacuum vacuumer_pg_class_stats pinholder_commit +step vacuumer_pg_class_stats: + SELECT relpages, reltuples FROM pg_class WHERE oid = 'smalltbl'::regclass; + +relpages|reltuples +--------+--------- + 1| 20 +(1 row) + +step dml_insert: + INSERT INTO smalltbl SELECT max(id) + 1 FROM smalltbl; + +step pinholder_cursor: + BEGIN; + DECLARE c1 CURSOR FOR SELECT 1 AS dummy FROM smalltbl; + FETCH NEXT FROM c1; + +dummy +----- + 1 +(1 row) + +step vacuumer_nonaggressive_vacuum: + VACUUM smalltbl; + +step vacuumer_pg_class_stats: + SELECT relpages, reltuples FROM pg_class WHERE oid = 'smalltbl'::regclass; + +relpages|reltuples +--------+--------- + 1| 21 +(1 row) + +step pinholder_commit: + COMMIT; + + +starting permutation: vacuumer_pg_class_stats pinholder_cursor dml_insert dml_delete dml_insert vacuumer_nonaggressive_vacuum vacuumer_pg_class_stats pinholder_commit +step vacuumer_pg_class_stats: + SELECT relpages, reltuples FROM pg_class WHERE oid = 'smalltbl'::regclass; + +relpages|reltuples +--------+--------- + 1| 20 +(1 row) + +step pinholder_cursor: + BEGIN; + DECLARE c1 CURSOR FOR SELECT 1 AS dummy FROM smalltbl; + FETCH NEXT FROM c1; + +dummy +----- + 1 +(1 row) + +step dml_insert: + INSERT INTO smalltbl SELECT max(id) + 1 FROM smalltbl; + +step dml_delete: + DELETE FROM smalltbl WHERE id = (SELECT min(id) FROM smalltbl); + +step dml_insert: + INSERT INTO smalltbl SELECT max(id) + 1 FROM smalltbl; + +step vacuumer_nonaggressive_vacuum: + VACUUM smalltbl; + +step vacuumer_pg_class_stats: + SELECT relpages, reltuples FROM pg_class WHERE oid = 'smalltbl'::regclass; + +relpages|reltuples +--------+--------- + 1| 21 +(1 row) + +step pinholder_commit: + COMMIT; + + +starting permutation: vacuumer_pg_class_stats dml_insert dml_delete pinholder_cursor dml_insert vacuumer_nonaggressive_vacuum vacuumer_pg_class_stats pinholder_commit +step vacuumer_pg_class_stats: + SELECT relpages, reltuples FROM pg_class WHERE oid = 'smalltbl'::regclass; + +relpages|reltuples +--------+--------- + 1| 20 +(1 row) + +step dml_insert: + INSERT INTO smalltbl SELECT max(id) + 1 FROM smalltbl; + +step dml_delete: + DELETE FROM smalltbl WHERE id = (SELECT min(id) FROM smalltbl); + +step pinholder_cursor: + BEGIN; + DECLARE c1 CURSOR FOR SELECT 1 AS dummy FROM smalltbl; + FETCH NEXT FROM c1; + +dummy +----- + 1 +(1 row) + +step dml_insert: + INSERT INTO smalltbl SELECT max(id) + 1 FROM smalltbl; + +step vacuumer_nonaggressive_vacuum: + VACUUM smalltbl; + +step vacuumer_pg_class_stats: + SELECT relpages, reltuples FROM pg_class WHERE oid = 'smalltbl'::regclass; + +relpages|reltuples +--------+--------- + 1| 21 +(1 row) + +step pinholder_commit: + COMMIT; + + +starting permutation: dml_begin dml_other_begin dml_key_share dml_other_key_share vacuumer_nonaggressive_vacuum pinholder_cursor dml_other_update dml_commit dml_other_commit vacuumer_nonaggressive_vacuum pinholder_commit vacuumer_nonaggressive_vacuum +step dml_begin: BEGIN; +step dml_other_begin: BEGIN; +step dml_key_share: SELECT id FROM smalltbl WHERE id = 3 FOR KEY SHARE; +id +-- + 3 +(1 row) + +step dml_other_key_share: SELECT id FROM smalltbl WHERE id = 3 FOR KEY SHARE; +id +-- + 3 +(1 row) + +step vacuumer_nonaggressive_vacuum: + VACUUM smalltbl; + +step pinholder_cursor: + BEGIN; + DECLARE c1 CURSOR FOR SELECT 1 AS dummy FROM smalltbl; + FETCH NEXT FROM c1; + +dummy +----- + 1 +(1 row) + +step dml_other_update: UPDATE smalltbl SET t = 'u' WHERE id = 3; +step dml_commit: COMMIT; +step dml_other_commit: COMMIT; +step vacuumer_nonaggressive_vacuum: + VACUUM smalltbl; + +step pinholder_commit: + COMMIT; + +step vacuumer_nonaggressive_vacuum: + VACUUM smalltbl; + diff --git a/src/test/isolation/expected/vacuum-reltuples.out b/src/test/isolation/expected/vacuum-reltuples.out deleted file mode 100644 index ce55376e7f2..00000000000 --- a/src/test/isolation/expected/vacuum-reltuples.out +++ /dev/null @@ -1,67 +0,0 @@ -Parsed test spec with 2 sessions - -starting permutation: modify vac stats -step modify: - insert into smalltbl select max(id)+1 from smalltbl; - -step vac: - vacuum smalltbl; - -step stats: - select relpages, reltuples from pg_class - where oid='smalltbl'::regclass; - -relpages|reltuples ---------+--------- - 1| 21 -(1 row) - - -starting permutation: modify open fetch1 vac close stats -step modify: - insert into smalltbl select max(id)+1 from smalltbl; - -step open: - begin; - declare c1 cursor for select 1 as dummy from smalltbl; - -step fetch1: - fetch next from c1; - -dummy ------ - 1 -(1 row) - -step vac: - vacuum smalltbl; - -step close: - commit; - -step stats: - select relpages, reltuples from pg_class - where oid='smalltbl'::regclass; - -relpages|reltuples ---------+--------- - 1| 21 -(1 row) - - -starting permutation: modify vac stats -step modify: - insert into smalltbl select max(id)+1 from smalltbl; - -step vac: - vacuum smalltbl; - -step stats: - select relpages, reltuples from pg_class - where oid='smalltbl'::regclass; - -relpages|reltuples ---------+--------- - 1| 21 -(1 row) - diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index 00749a40bdd..a48caae228e 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -84,7 +84,7 @@ test: alter-table-4 test: create-trigger test: sequence-ddl test: async-notify -test: vacuum-reltuples +test: vacuum-no-cleanup-lock test: timeouts test: vacuum-concurrent-drop test: vacuum-conflict diff --git a/src/test/isolation/specs/vacuum-no-cleanup-lock.spec b/src/test/isolation/specs/vacuum-no-cleanup-lock.spec new file mode 100644 index 00000000000..a88be66de5f --- /dev/null +++ b/src/test/isolation/specs/vacuum-no-cleanup-lock.spec @@ -0,0 +1,150 @@ +# Test for vacuum's reduced processing of heap pages (used for any heap page +# where a cleanup lock isn't immediately available) +# +# Debugging tip: Change VACUUM to VACUUM VERBOSE to get feedback on what's +# really going on + +# Use name type here to avoid TOAST table: +setup +{ + CREATE TABLE smalltbl AS SELECT i AS id, 't'::name AS t FROM generate_series(1,20) i; + ALTER TABLE smalltbl SET (autovacuum_enabled = off); + ALTER TABLE smalltbl ADD PRIMARY KEY (id); +} +setup +{ + VACUUM ANALYZE smalltbl; +} + +teardown +{ + DROP TABLE smalltbl; +} + +# This session holds a pin on smalltbl's only heap page: +session pinholder +step pinholder_cursor +{ + BEGIN; + DECLARE c1 CURSOR FOR SELECT 1 AS dummy FROM smalltbl; + FETCH NEXT FROM c1; +} +step pinholder_commit +{ + COMMIT; +} + +# This session inserts and deletes tuples, potentially affecting reltuples: +session dml +step dml_insert +{ + INSERT INTO smalltbl SELECT max(id) + 1 FROM smalltbl; +} +step dml_delete +{ + DELETE FROM smalltbl WHERE id = (SELECT min(id) FROM smalltbl); +} +step dml_begin { BEGIN; } +step dml_key_share { SELECT id FROM smalltbl WHERE id = 3 FOR KEY SHARE; } +step dml_commit { COMMIT; } + +# Needed for Multixact test: +session dml_other +step dml_other_begin { BEGIN; } +step dml_other_key_share { SELECT id FROM smalltbl WHERE id = 3 FOR KEY SHARE; } +step dml_other_update { UPDATE smalltbl SET t = 'u' WHERE id = 3; } +step dml_other_commit { COMMIT; } + +# This session runs non-aggressive VACUUM, but with maximally aggressive +# cutoffs for tuple freezing (e.g., FreezeLimit == OldestXmin): +session vacuumer +setup +{ + SET vacuum_freeze_min_age = 0; + SET vacuum_multixact_freeze_min_age = 0; +} +step vacuumer_nonaggressive_vacuum +{ + VACUUM smalltbl; +} +step vacuumer_pg_class_stats +{ + SELECT relpages, reltuples FROM pg_class WHERE oid = 'smalltbl'::regclass; +} + +# Test VACUUM's reltuples counting mechanism. +# +# Final pg_class.reltuples should never be affected by VACUUM's inability to +# get a cleanup lock on any page, except to the extent that any cleanup lock +# contention changes the number of tuples that remain ("missed dead" tuples +# are counted in reltuples, much like "recently dead" tuples). + +# Easy case: +permutation + vacuumer_pg_class_stats # Start with 20 tuples + dml_insert + vacuumer_nonaggressive_vacuum + vacuumer_pg_class_stats # End with 21 tuples + +# Harder case -- count 21 tuples at the end (like last time), but with cleanup +# lock contention this time: +permutation + vacuumer_pg_class_stats # Start with 20 tuples + dml_insert + pinholder_cursor + vacuumer_nonaggressive_vacuum + vacuumer_pg_class_stats # End with 21 tuples + pinholder_commit # order doesn't matter + +# Same as "harder case", but vary the order, and delete an inserted row: +permutation + vacuumer_pg_class_stats # Start with 20 tuples + pinholder_cursor + dml_insert + dml_delete + dml_insert + vacuumer_nonaggressive_vacuum + # reltuples is 21 here again -- "recently dead" tuple won't be included in + # count here: + vacuumer_pg_class_stats + pinholder_commit # order doesn't matter + +# Same as "harder case", but initial insert and delete before cursor: +permutation + vacuumer_pg_class_stats # Start with 20 tuples + dml_insert + dml_delete + pinholder_cursor + dml_insert + vacuumer_nonaggressive_vacuum + # reltuples is 21 here again -- "missed dead" tuple ("recently dead" when + # concurrent activity held back VACUUM's OldestXmin) won't be included in + # count here: + vacuumer_pg_class_stats + pinholder_commit # order doesn't matter + +# Test VACUUM's mechanism for skipping MultiXact freezing. +# +# This provides test coverage for code paths that are only hit when we need to +# freeze, but inability to acquire a cleanup lock on a heap page makes +# freezing some XIDs/XMIDs < FreezeLimit/MultiXactCutoff impossible (without +# waiting for a cleanup lock, which non-aggressive VACUUM is unwilling to do). +permutation + dml_begin + dml_other_begin + dml_key_share + dml_other_key_share + # Will get cleanup lock, can't advance relminmxid yet: + # (though will usually advance relfrozenxid by ~2 XIDs) + vacuumer_nonaggressive_vacuum + pinholder_cursor + dml_other_update + dml_commit + dml_other_commit + # Can't cleanup lock, so still can't advance relminmxid here: + # (relfrozenxid held back by XIDs in MultiXact too) + vacuumer_nonaggressive_vacuum + pinholder_commit + # Pin was dropped, so will advance relminmxid, at long last: + # (ditto for relfrozenxid advancement) + vacuumer_nonaggressive_vacuum diff --git a/src/test/isolation/specs/vacuum-reltuples.spec b/src/test/isolation/specs/vacuum-reltuples.spec deleted file mode 100644 index a2a461f2f5d..00000000000 --- a/src/test/isolation/specs/vacuum-reltuples.spec +++ /dev/null @@ -1,49 +0,0 @@ -# Test for vacuum's handling of reltuples when pages are skipped due -# to page pins. We absolutely need to avoid setting reltuples=0 in -# such cases, since that interferes badly with planning. -# -# Expected result for all three permutation is 21 tuples, including -# the second permutation. VACUUM is able to count the concurrently -# inserted tuple in its final reltuples, even when a cleanup lock -# cannot be acquired on the affected heap page. - -setup { - create table smalltbl - as select i as id from generate_series(1,20) i; - alter table smalltbl set (autovacuum_enabled = off); -} -setup { - vacuum analyze smalltbl; -} - -teardown { - drop table smalltbl; -} - -session worker -step open { - begin; - declare c1 cursor for select 1 as dummy from smalltbl; -} -step fetch1 { - fetch next from c1; -} -step close { - commit; -} -step stats { - select relpages, reltuples from pg_class - where oid='smalltbl'::regclass; -} - -session vacuumer -step vac { - vacuum smalltbl; -} -step modify { - insert into smalltbl select max(id)+1 from smalltbl; -} - -permutation modify vac stats -permutation modify open fetch1 vac close stats -permutation modify vac stats |