Hi, On Fri, 31 Oct 2025 at 03:42, Michael Paquier <[email protected]> wrote: > > On Thu, Oct 30, 2025 at 11:24:55AM -0400, Andres Freund wrote: > > Well, it tests multiple index types, not just gist. I guess > > test/modules/indexes/ or so should work? Not sure about plural or not. > > test/modules/index/ would work here. If you feel strongly about being > able to do an EXTRA_INSTALL in src/test/isolation/, I have no > objections to that as that would also solve the original problem if > that's the consensus. My personal choice would be a test module for > that, as it feels cleaner.
I have two patches to implement this: one moving the killtuples test under test/modules/index/, and another adding coverage for the recovery path. 0001 moves killtuples test under the test/modules/index/ without any implementation change. 0002 converts the killtuples isolation test to a TAP test to exercise the recovery path. This patch sets up a standby and additionally re-inserts into the table while testing the GiST index to ensure coverage of the gistRedoDeleteRecord() function. -- Regards, Nazir Bilal Yavuz Microsoft
From c665ee5e505ebea4170450ac6ca4351158b8f2a9 Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Mon, 3 Nov 2025 11:44:33 +0300 Subject: [PATCH v1 1/2] Move index-killtuples test to test/modules/index index-killtuples test depends on btree_gin and btree_gist. Since we don't want to depend on contrib/ in the src/test/regress/ tests, this patch moves index-killuples test to test/modules/index/ directory. Also, since the path has 'index' in its name, renaming index-killtuples test as killtuples test. Author: Nazir Bilal Yavuz <[email protected]> Suggested-by: Andres Freund <[email protected]> Suggested-by: Michael Paquier <[email protected]> Discussion: https://postgr.es/m/aKJsWedftW7UX1WM%40paquier.xyz --- src/test/isolation/isolation_schedule | 1 - src/test/modules/Makefile | 1 + src/test/modules/index/.gitignore | 6 ++++++ src/test/modules/index/Makefile | 16 ++++++++++++++++ .../index/expected/killtuples.out} | 0 src/test/modules/index/meson.build | 12 ++++++++++++ .../index/specs/killtuples.spec} | 0 src/test/modules/meson.build | 1 + 8 files changed, 36 insertions(+), 1 deletion(-) create mode 100644 src/test/modules/index/.gitignore create mode 100644 src/test/modules/index/Makefile rename src/test/{isolation/expected/index-killtuples.out => modules/index/expected/killtuples.out} (100%) create mode 100644 src/test/modules/index/meson.build rename src/test/{isolation/specs/index-killtuples.spec => modules/index/specs/killtuples.spec} (100%) diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index 5afae33d370..112f05a3677 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -16,7 +16,6 @@ test: ri-trigger test: partial-index test: two-ids test: multiple-row-versions -test: index-killtuples test: index-only-scan test: index-only-bitmapscan test: predicate-lock-hot-tuple diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile index 902a7954101..d079b91b1a2 100644 --- a/src/test/modules/Makefile +++ b/src/test/modules/Makefile @@ -10,6 +10,7 @@ SUBDIRS = \ delay_execution \ dummy_index_am \ dummy_seclabel \ + index \ libpq_pipeline \ oauth_validator \ plsample \ diff --git a/src/test/modules/index/.gitignore b/src/test/modules/index/.gitignore new file mode 100644 index 00000000000..b4903eba657 --- /dev/null +++ b/src/test/modules/index/.gitignore @@ -0,0 +1,6 @@ +# Generated subdirectories +/log/ +/results/ +/output_iso/ +/tmp_check/ +/tmp_check_iso/ diff --git a/src/test/modules/index/Makefile b/src/test/modules/index/Makefile new file mode 100644 index 00000000000..29047044ede --- /dev/null +++ b/src/test/modules/index/Makefile @@ -0,0 +1,16 @@ +# src/test/modules/index/Makefile + +EXTRA_INSTALL = contrib/btree_gin contrib/btree_gist + +ISOLATION = killtuples + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = src/test/modules/index +top_builddir = ../../../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/src/test/isolation/expected/index-killtuples.out b/src/test/modules/index/expected/killtuples.out similarity index 100% rename from src/test/isolation/expected/index-killtuples.out rename to src/test/modules/index/expected/killtuples.out diff --git a/src/test/modules/index/meson.build b/src/test/modules/index/meson.build new file mode 100644 index 00000000000..15f3734567a --- /dev/null +++ b/src/test/modules/index/meson.build @@ -0,0 +1,12 @@ +# Copyright (c) 2022-2025, PostgreSQL Global Development Group + +tests += { + 'name': 'index', + 'sd': meson.current_source_dir(), + 'bd': meson.current_build_dir(), + 'isolation': { + 'specs': [ + 'killtuples', + ], + }, +} diff --git a/src/test/isolation/specs/index-killtuples.spec b/src/test/modules/index/specs/killtuples.spec similarity index 100% rename from src/test/isolation/specs/index-killtuples.spec rename to src/test/modules/index/specs/killtuples.spec diff --git a/src/test/modules/meson.build b/src/test/modules/meson.build index 14fc761c4cf..f5114469b92 100644 --- a/src/test/modules/meson.build +++ b/src/test/modules/meson.build @@ -6,6 +6,7 @@ subdir('delay_execution') subdir('dummy_index_am') subdir('dummy_seclabel') subdir('gin') +subdir('index') subdir('injection_points') subdir('ldap_password_func') subdir('libpq_pipeline') -- 2.51.0
From 16b7927775007b6dcbb40a8d86d4f7c9dd842cb5 Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <[email protected]> Date: Wed, 5 Nov 2025 15:54:11 +0300 Subject: [PATCH v1 2/2] Convert killtuples isolation test to TAP test This patch converts isolation test to TAP test for exercising the recovery path. There are no implementation changes except covering redo delete case for the gist index by re-inserting to the table. Author: Nazir Bilal Yavuz <[email protected]> Suggested-by: Andres Freund <[email protected]> Discussion: https://postgr.es/m/aKJsWedftW7UX1WM%40paquier.xyz --- src/test/modules/index/.gitignore | 4 - src/test/modules/index/Makefile | 3 +- .../modules/index/expected/killtuples.out | 355 ------------------ src/test/modules/index/meson.build | 6 +- src/test/modules/index/specs/killtuples.spec | 127 ------- src/test/modules/index/t/001_killtuples.pl | 343 +++++++++++++++++ 6 files changed, 347 insertions(+), 491 deletions(-) delete mode 100644 src/test/modules/index/expected/killtuples.out delete mode 100644 src/test/modules/index/specs/killtuples.spec create mode 100644 src/test/modules/index/t/001_killtuples.pl diff --git a/src/test/modules/index/.gitignore b/src/test/modules/index/.gitignore index b4903eba657..716e17f5a2a 100644 --- a/src/test/modules/index/.gitignore +++ b/src/test/modules/index/.gitignore @@ -1,6 +1,2 @@ # Generated subdirectories -/log/ -/results/ -/output_iso/ /tmp_check/ -/tmp_check_iso/ diff --git a/src/test/modules/index/Makefile b/src/test/modules/index/Makefile index 29047044ede..5dc97453cfa 100644 --- a/src/test/modules/index/Makefile +++ b/src/test/modules/index/Makefile @@ -1,8 +1,7 @@ # src/test/modules/index/Makefile EXTRA_INSTALL = contrib/btree_gin contrib/btree_gist - -ISOLATION = killtuples +TAP_TESTS = 1 ifdef USE_PGXS PG_CONFIG = pg_config diff --git a/src/test/modules/index/expected/killtuples.out b/src/test/modules/index/expected/killtuples.out deleted file mode 100644 index be7ddd756ef..00000000000 --- a/src/test/modules/index/expected/killtuples.out +++ /dev/null @@ -1,355 +0,0 @@ -Parsed test spec with 1 sessions - -starting permutation: create_table fill_500 create_btree flush disable_seq disable_bitmap measure access flush result measure access flush result delete flush measure access flush result measure access flush result drop_table -step create_table: CREATE TEMPORARY TABLE kill_prior_tuple(key int not null, cat text not null); -step fill_500: INSERT INTO kill_prior_tuple(key, cat) SELECT g.i, 'a' FROM generate_series(1, 500) g(i); -step create_btree: CREATE INDEX kill_prior_tuple_btree ON kill_prior_tuple USING btree (key); -step flush: SELECT FROM pg_stat_force_next_flush(); -step disable_seq: SET enable_seqscan = false; -step disable_bitmap: SET enable_bitmapscan = false; -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN --------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_btree on kill_prior_tuple (actual rows=1.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN --------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_btree on kill_prior_tuple (actual rows=1.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step delete: DELETE FROM kill_prior_tuple; -step flush: SELECT FROM pg_stat_force_next_flush(); -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN --------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_btree on kill_prior_tuple (actual rows=0.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN --------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_btree on kill_prior_tuple (actual rows=0.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 0 -(1 row) - -step drop_table: DROP TABLE IF EXISTS kill_prior_tuple; - -starting permutation: create_table fill_500 create_ext_btree_gist create_gist flush disable_seq disable_bitmap measure access flush result measure access flush result delete flush measure access flush result measure access flush result drop_table drop_ext_btree_gist -step create_table: CREATE TEMPORARY TABLE kill_prior_tuple(key int not null, cat text not null); -step fill_500: INSERT INTO kill_prior_tuple(key, cat) SELECT g.i, 'a' FROM generate_series(1, 500) g(i); -step create_ext_btree_gist: CREATE EXTENSION btree_gist; -step create_gist: CREATE INDEX kill_prior_tuple_gist ON kill_prior_tuple USING gist (key); -step flush: SELECT FROM pg_stat_force_next_flush(); -step disable_seq: SET enable_seqscan = false; -step disable_bitmap: SET enable_bitmapscan = false; -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN -------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_gist on kill_prior_tuple (actual rows=1.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN -------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_gist on kill_prior_tuple (actual rows=1.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step delete: DELETE FROM kill_prior_tuple; -step flush: SELECT FROM pg_stat_force_next_flush(); -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN -------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_gist on kill_prior_tuple (actual rows=0.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN -------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_gist on kill_prior_tuple (actual rows=0.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 0 -(1 row) - -step drop_table: DROP TABLE IF EXISTS kill_prior_tuple; -step drop_ext_btree_gist: DROP EXTENSION btree_gist; - -starting permutation: create_table fill_10 create_ext_btree_gist create_gist flush disable_seq disable_bitmap measure access flush result measure access flush result delete flush measure access flush result measure access flush result drop_table drop_ext_btree_gist -step create_table: CREATE TEMPORARY TABLE kill_prior_tuple(key int not null, cat text not null); -step fill_10: INSERT INTO kill_prior_tuple(key, cat) SELECT g.i, 'a' FROM generate_series(1, 10) g(i); -step create_ext_btree_gist: CREATE EXTENSION btree_gist; -step create_gist: CREATE INDEX kill_prior_tuple_gist ON kill_prior_tuple USING gist (key); -step flush: SELECT FROM pg_stat_force_next_flush(); -step disable_seq: SET enable_seqscan = false; -step disable_bitmap: SET enable_bitmapscan = false; -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN -------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_gist on kill_prior_tuple (actual rows=1.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN -------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_gist on kill_prior_tuple (actual rows=1.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step delete: DELETE FROM kill_prior_tuple; -step flush: SELECT FROM pg_stat_force_next_flush(); -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN -------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_gist on kill_prior_tuple (actual rows=0.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN -------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_gist on kill_prior_tuple (actual rows=0.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step drop_table: DROP TABLE IF EXISTS kill_prior_tuple; -step drop_ext_btree_gist: DROP EXTENSION btree_gist; - -starting permutation: create_table fill_500 create_hash flush disable_seq disable_bitmap measure access flush result measure access flush result delete flush measure access flush result measure access flush result drop_table -step create_table: CREATE TEMPORARY TABLE kill_prior_tuple(key int not null, cat text not null); -step fill_500: INSERT INTO kill_prior_tuple(key, cat) SELECT g.i, 'a' FROM generate_series(1, 500) g(i); -step create_hash: CREATE INDEX kill_prior_tuple_hash ON kill_prior_tuple USING hash (key); -step flush: SELECT FROM pg_stat_force_next_flush(); -step disable_seq: SET enable_seqscan = false; -step disable_bitmap: SET enable_bitmapscan = false; -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN -------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_hash on kill_prior_tuple (actual rows=1.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN -------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_hash on kill_prior_tuple (actual rows=1.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step delete: DELETE FROM kill_prior_tuple; -step flush: SELECT FROM pg_stat_force_next_flush(); -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN -------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_hash on kill_prior_tuple (actual rows=0.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN -------------------------------------------------------------------------------------- -Index Scan using kill_prior_tuple_hash on kill_prior_tuple (actual rows=0.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(3 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 0 -(1 row) - -step drop_table: DROP TABLE IF EXISTS kill_prior_tuple; - -starting permutation: create_table fill_500 create_ext_btree_gin create_gin flush disable_seq delete flush measure access flush result measure access flush result drop_table drop_ext_btree_gin -step create_table: CREATE TEMPORARY TABLE kill_prior_tuple(key int not null, cat text not null); -step fill_500: INSERT INTO kill_prior_tuple(key, cat) SELECT g.i, 'a' FROM generate_series(1, 500) g(i); -step create_ext_btree_gin: CREATE EXTENSION btree_gin; -step create_gin: CREATE INDEX kill_prior_tuple_gin ON kill_prior_tuple USING gin (key); -step flush: SELECT FROM pg_stat_force_next_flush(); -step disable_seq: SET enable_seqscan = false; -step delete: DELETE FROM kill_prior_tuple; -step flush: SELECT FROM pg_stat_force_next_flush(); -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN --------------------------------------------------------------------------- -Bitmap Heap Scan on kill_prior_tuple (actual rows=0.00 loops=1) - Recheck Cond: (key = 1) - Heap Blocks: exact=1 - -> Bitmap Index Scan on kill_prior_tuple_gin (actual rows=1.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(6 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step measure: UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); -step access: EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; -QUERY PLAN --------------------------------------------------------------------------- -Bitmap Heap Scan on kill_prior_tuple (actual rows=0.00 loops=1) - Recheck Cond: (key = 1) - Heap Blocks: exact=1 - -> Bitmap Index Scan on kill_prior_tuple_gin (actual rows=1.00 loops=1) - Index Cond: (key = 1) - Index Searches: 1 -(6 rows) - -step flush: SELECT FROM pg_stat_force_next_flush(); -step result: SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; -new_heap_accesses ------------------ - 1 -(1 row) - -step drop_table: DROP TABLE IF EXISTS kill_prior_tuple; -step drop_ext_btree_gin: DROP EXTENSION btree_gin; diff --git a/src/test/modules/index/meson.build b/src/test/modules/index/meson.build index 15f3734567a..87b6318dcae 100644 --- a/src/test/modules/index/meson.build +++ b/src/test/modules/index/meson.build @@ -4,9 +4,9 @@ tests += { 'name': 'index', 'sd': meson.current_source_dir(), 'bd': meson.current_build_dir(), - 'isolation': { - 'specs': [ - 'killtuples', + 'tap': { + 'tests': [ + 't/001_killtuples.pl', ], }, } diff --git a/src/test/modules/index/specs/killtuples.spec b/src/test/modules/index/specs/killtuples.spec deleted file mode 100644 index 77fe8c689a7..00000000000 --- a/src/test/modules/index/specs/killtuples.spec +++ /dev/null @@ -1,127 +0,0 @@ -# Basic testing of killtuples / kill_prior_tuples / all_dead testing -# for various index AMs -# -# This tests just enough to ensure that the kill* routines are actually -# executed and does something approximately reasonable. It's *not* sufficient -# testing for adding killitems support to a new AM! -# -# This doesn't really need to be an isolation test, it could be written as a -# regular regression test. However, writing it as an isolation test ends up a -# *lot* less verbose. - -setup -{ - CREATE TABLE counter(heap_accesses int); - INSERT INTO counter(heap_accesses) VALUES (0); -} - -teardown -{ - DROP TABLE counter; -} - -session s1 -# to ensure GUCs are reset -setup { RESET ALL; } - -step disable_seq { SET enable_seqscan = false; } - -step disable_bitmap { SET enable_bitmapscan = false; } - -# use a temporary table to make sure no other session can interfere with -# visibility determinations -step create_table { CREATE TEMPORARY TABLE kill_prior_tuple(key int not null, cat text not null); } - -step fill_10 { INSERT INTO kill_prior_tuple(key, cat) SELECT g.i, 'a' FROM generate_series(1, 10) g(i); } - -step fill_500 { INSERT INTO kill_prior_tuple(key, cat) SELECT g.i, 'a' FROM generate_series(1, 500) g(i); } - -# column-less select to make output easier to read -step flush { SELECT FROM pg_stat_force_next_flush(); } - -step measure { UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); } - -step result { SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses AS new_heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; } - -step access { EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; } - -step delete { DELETE FROM kill_prior_tuple; } - -step drop_table { DROP TABLE IF EXISTS kill_prior_tuple; } - -### steps for testing btree indexes ### -step create_btree { CREATE INDEX kill_prior_tuple_btree ON kill_prior_tuple USING btree (key); } - -### steps for testing gist indexes ### -# Creating the extensions takes time, so we don't want to do so when testing -# other AMs -step create_ext_btree_gist { CREATE EXTENSION btree_gist; } -step drop_ext_btree_gist { DROP EXTENSION btree_gist; } -step create_gist { CREATE INDEX kill_prior_tuple_gist ON kill_prior_tuple USING gist (key); } - -### steps for testing gin indexes ### -# See create_ext_btree_gist -step create_ext_btree_gin { CREATE EXTENSION btree_gin; } -step drop_ext_btree_gin { DROP EXTENSION btree_gin; } -step create_gin { CREATE INDEX kill_prior_tuple_gin ON kill_prior_tuple USING gin (key); } - -### steps for testing hash indexes ### -step create_hash { CREATE INDEX kill_prior_tuple_hash ON kill_prior_tuple USING hash (key); } - - -# test killtuples with btree index -permutation - create_table fill_500 create_btree flush - disable_seq disable_bitmap - # show each access to non-deleted tuple increments heap_blks_* - measure access flush result - measure access flush result - delete flush - # first access after accessing deleted tuple still needs to access heap - measure access flush result - # but after kill_prior_tuple did its thing, we shouldn't access heap anymore - measure access flush result - drop_table - -# Same as first permutation, except testing gist -permutation - create_table fill_500 create_ext_btree_gist create_gist flush - disable_seq disable_bitmap - measure access flush result - measure access flush result - delete flush - measure access flush result - measure access flush result - drop_table drop_ext_btree_gist - -# Test gist, but with fewer rows - shows that killitems doesn't work anymore! -permutation - create_table fill_10 create_ext_btree_gist create_gist flush - disable_seq disable_bitmap - measure access flush result - measure access flush result - delete flush - measure access flush result - measure access flush result - drop_table drop_ext_btree_gist - -# Same as first permutation, except testing hash -permutation - create_table fill_500 create_hash flush - disable_seq disable_bitmap - measure access flush result - measure access flush result - delete flush - measure access flush result - measure access flush result - drop_table - -# # Similar to first permutation, except that gin does not have killtuples support -permutation - create_table fill_500 create_ext_btree_gin create_gin flush - disable_seq - delete flush - measure access flush result - # will still fetch from heap - measure access flush result - drop_table drop_ext_btree_gin diff --git a/src/test/modules/index/t/001_killtuples.pl b/src/test/modules/index/t/001_killtuples.pl new file mode 100644 index 00000000000..eda138a0b32 --- /dev/null +++ b/src/test/modules/index/t/001_killtuples.pl @@ -0,0 +1,343 @@ + +# Copyright (c) 2025, PostgreSQL Global Development Group + +# Basic testing of killtuples / kill_prior_tuples / all_dead testing +# for various index AMs along with the WAL replay functions. +# +# This tests just enough to ensure that the kill* routines are actually +# executed and does something approximately reasonable. It's *not* sufficient +# testing for adding killitems support to a new AM! + +use strict; +use warnings FATAL => 'all'; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +my ($primary, $psql_primary, $standby, $psql_standby) = start_instances(); +create_extensions(); +disable_seq(); +set_bitmap('false'); + +test_btree_index(); +test_gist_index(); +test_gist_index_fewer_rows(); +test_hash_index(); + +set_bitmap('true'); +test_gin_index(); + +stop_instances(); + +# test killtuples with btree index +sub test_btree_index +{ + my $test_name = 'btree'; + prepare_table(); + create_btree(); + + insert_table_n_rows(500); + + do_checks($test_name, 1); + do_checks($test_name, 1); + + delete_table(); + + do_checks($test_name, 1); + do_checks($test_name, 0); + + drop_table(); + check_wal_is_replayed($test_name); +} + +# Same as btree_index, except testing gist +sub test_gist_index +{ + my $test_name = 'gist'; + prepare_table(); + create_btree_gist(); + + insert_table_n_rows(500); + + do_checks($test_name, 1); + do_checks($test_name, 1); + + delete_table(); + + do_checks($test_name, 1); + do_checks($test_name, 0); + + # Re-insert to test redo delete case + insert_table_n_rows(250); + + drop_table(); + check_wal_is_replayed($test_name); +} + +# Same as gist, but with fewer rows - shows that killitems doesn't work anymore! +sub test_gist_index_fewer_rows +{ + my $test_name = 'gist_but_fewer_rows'; + prepare_table(); + create_btree_gist(); + + insert_table_n_rows(10); + + do_checks($test_name, 1); + do_checks($test_name, 1); + + delete_table(); + + do_checks($test_name, 1); + do_checks($test_name, 1); + + drop_table(); + check_wal_is_replayed($test_name); +} + +# Same as btree_index, except testing hash +sub test_hash_index +{ + my $test_name = 'hash'; + prepare_table(); + create_hash(); + + insert_table_n_rows(500); + + do_checks($test_name, 1); + do_checks($test_name, 1); + + delete_table(); + + do_checks($test_name, 1); + do_checks($test_name, 0); + + drop_table(); + check_wal_is_replayed($test_name); +} + +# Similar to btree_index, except that gin does not have killtuples support +sub test_gin_index +{ + my $test_name = 'gin'; + prepare_table(); + create_btree_gin(); + + insert_table_n_rows(500); + + delete_table(); + + do_checks($test_name, 1); + do_checks($test_name, 1); + + drop_table(); + check_wal_is_replayed($test_name); +} + +# Create btree index +sub create_btree +{ + $psql_primary->query_safe( + q( +CREATE INDEX kill_prior_tuple_btree ON kill_prior_tuple USING btree (key); +)); +} + +# Create btree_gist extension and index +sub create_btree_gist +{ + $psql_primary->query_safe( + q( +CREATE INDEX kill_prior_tuple_gist ON kill_prior_tuple USING gist (key); +)); +} + +# Create btree_gin extension and index +sub create_btree_gin +{ + $psql_primary->query_safe( + q( +CREATE INDEX kill_prior_tuple_gin ON kill_prior_tuple USING gin (key); + )); +} + +# Create hash index +sub create_hash +{ + $psql_primary->query_safe( + q( +CREATE INDEX kill_prior_tuple_hash ON kill_prior_tuple USING hash (key); +)); +} + +### General helper functions +sub start_instances +{ + my $backup_name = 'backup'; + my $primary; + my $psql_primary; + my $standby; + my $psql_standby; + + $primary = PostgreSQL::Test::Cluster->new("primary"); + $primary->init(allows_streaming => 1); + $primary->start; + $psql_primary = $primary->background_psql('postgres'); + $primary->backup($backup_name); + + $standby = PostgreSQL::Test::Cluster->new("standby"); + $standby->init_from_backup($primary, $backup_name, has_streaming => 1); + $standby->start; + $psql_standby = $standby->background_psql('postgres'); + + return $primary, $psql_primary, $standby, $psql_standby; +} + +sub create_extensions +{ + $psql_primary->query_safe( + q( +CREATE EXTENSION btree_gist; +CREATE EXTENSION btree_gin; +)); +} + +sub prepare_table +{ + $psql_primary->query_safe( + q( +CREATE TABLE counter(heap_accesses int); +INSERT INTO counter(heap_accesses) VALUES (0); +CREATE TABLE kill_prior_tuple(key int not null, cat text not null); +)); +} + +sub insert_table_n_rows +{ + my $n_rows = shift; + + $psql_primary->query_safe( + qq( +INSERT INTO kill_prior_tuple(key, cat) SELECT g.i, 'a' FROM generate_series(1, $n_rows) g(i); +)); + flush_statistics(); +} + +sub stop_instances +{ + $psql_standby->quit; + $standby->stop; + + $psql_primary->quit; + $primary->stop; +} + +sub drop_table +{ + $psql_primary->query_safe( + q( +DROP TABLE IF EXISTS kill_prior_tuple; +DROP TABLE IF EXISTS counter; +)); +} + +sub delete_table +{ + $psql_primary->query_safe(q(DELETE FROM kill_prior_tuple;)); + flush_statistics(); +} + +sub disable_seq +{ + $psql_primary->query_safe( + q( +SET enable_seqscan = false; +SELECT pg_reload_conf(); +)); +} + +sub set_bitmap +{ + my $val = shift; + + $psql_primary->query_safe( + qq( +SET enable_bitmapscan = $val; +SELECT pg_reload_conf(); +)); +} + +sub flush_statistics +{ + $psql_primary->query_safe(q(SELECT FROM pg_stat_force_next_flush();)); +} + +sub update_heap_accesses +{ + $psql_primary->query_safe( + q( +UPDATE counter SET heap_accesses = (SELECT heap_blks_read + heap_blks_hit FROM pg_statio_all_tables WHERE relname = 'kill_prior_tuple'); +)); +} + +sub check_index_searches +{ + my $test_name = shift; + my $result; + + $result = $psql_primary->query_safe( + q( +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM kill_prior_tuple WHERE key = 1; +)); + + like( + $result, + qr/Index Searches: 1/, + "$test_name: Number of 'Index Searches'should be equal to 1"); +} + +sub check_new_heap_accesses +{ + my ($test_name, $new_heap_accesses) = @_; + my $result; + + $result = $psql_primary->query_safe( + q( +SELECT heap_blks_read + heap_blks_hit - counter.heap_accesses FROM counter, pg_statio_all_tables WHERE relname = 'kill_prior_tuple'; +)); + + cmp_ok($result, '==', $new_heap_accesses, + "$test_name: Number of 'new_heap_accesses' should be equal to $new_heap_accesses" + ); +} + +sub do_checks +{ + my ($test_name, $new_heap_accesses) = @_; + + # First update number of heap accesses + update_heap_accesses(); + # Then check number of index searches in the EXPLAIN output + check_index_searches($test_name); + # Then force flush statistics + flush_statistics(); + # Then check number of *new* heap accesses, it should be equal to + # $new_heap_accesses + check_new_heap_accesses($test_name, $new_heap_accesses); +} + +sub check_wal_is_replayed +{ + my $test_name = shift; + + my $current_lsn = + $psql_primary->query_safe("SELECT pg_current_wal_lsn();"); + my $caughtup_query = + "SELECT '$current_lsn'::pg_lsn <= pg_last_wal_replay_lsn()"; + + $standby->poll_query_until('postgres', $caughtup_query) + or die "$test_name: Timed out while waiting for standby to catch up"; +} + +done_testing(); -- 2.51.0
