From e429c3cecb4ac55d997acea0f76c5f06d6cb0ab3 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Mon, 24 Nov 2025 19:33:51 +0900 Subject: Move isolation test index-killtuples to src/test/modules/index/ index-killtuples test depends on the contrib modules btree_gin and btree_gist, which would not be installed in a temporary installation with an execution of the main isolation test suite like this one: make -C src/test/isolation/ check src/test/isolation/ should not depend on contrib/, and EXTRA_INSTALL has no effect in this case as this test suite uses its own Makefile rules. This commit moves index-killtuples into its new module, called "index", whose name looks like the best fit there can be as it depends on more than one index AM. btree_gin and btree_gist are now pulled in the temporary installation with EXTRA_INSTALL. The test is renamed to "killtuples", for simplicity. Author: Nazir Bilal Yavuz Suggested-by: Andres Freund Suggested-by: Michael Paquier Discussion: https://postgr.es/m/aKJsWedftW7UX1WM@paquier.xyz --- src/test/isolation/expected/index-killtuples.out | 355 ----------------------- src/test/isolation/isolation_schedule | 1 - src/test/isolation/specs/index-killtuples.spec | 127 -------- src/test/modules/Makefile | 1 + src/test/modules/index/.gitignore | 6 + src/test/modules/index/Makefile | 16 + src/test/modules/index/expected/killtuples.out | 355 +++++++++++++++++++++++ src/test/modules/index/meson.build | 12 + src/test/modules/index/specs/killtuples.spec | 127 ++++++++ src/test/modules/meson.build | 1 + 10 files changed, 518 insertions(+), 483 deletions(-) delete mode 100644 src/test/isolation/expected/index-killtuples.out delete mode 100644 src/test/isolation/specs/index-killtuples.spec create mode 100644 src/test/modules/index/.gitignore create mode 100644 src/test/modules/index/Makefile create mode 100644 src/test/modules/index/expected/killtuples.out create mode 100644 src/test/modules/index/meson.build create mode 100644 src/test/modules/index/specs/killtuples.spec (limited to 'src') diff --git a/src/test/isolation/expected/index-killtuples.out b/src/test/isolation/expected/index-killtuples.out deleted file mode 100644 index be7ddd756ef..00000000000 --- a/src/test/isolation/expected/index-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/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/isolation/specs/index-killtuples.spec b/src/test/isolation/specs/index-killtuples.spec deleted file mode 100644 index 77fe8c689a7..00000000000 --- a/src/test/isolation/specs/index-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/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/modules/index/expected/killtuples.out b/src/test/modules/index/expected/killtuples.out new file mode 100644 index 00000000000..be7ddd756ef --- /dev/null +++ b/src/test/modules/index/expected/killtuples.out @@ -0,0 +1,355 @@ +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 new file mode 100644 index 00000000000..1b3c42d8a2f --- /dev/null +++ b/src/test/modules/index/meson.build @@ -0,0 +1,12 @@ +# Copyright (c) 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/modules/index/specs/killtuples.spec b/src/test/modules/index/specs/killtuples.spec new file mode 100644 index 00000000000..77fe8c689a7 --- /dev/null +++ b/src/test/modules/index/specs/killtuples.spec @@ -0,0 +1,127 @@ +# 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/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') -- cgit v1.2.3