diff options
Diffstat (limited to 'src/test/isolation/specs/stats.spec')
| -rw-r--r-- | src/test/isolation/specs/stats.spec | 760 |
1 files changed, 760 insertions, 0 deletions
diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec new file mode 100644 index 0000000..5b922d7 --- /dev/null +++ b/src/test/isolation/specs/stats.spec @@ -0,0 +1,760 @@ +setup +{ + CREATE TABLE test_stat_oid(name text NOT NULL, oid oid); + + CREATE TABLE test_stat_tab(key text not null, value int); + INSERT INTO test_stat_tab(key, value) VALUES('k0', 1); + INSERT INTO test_stat_oid(name, oid) VALUES('test_stat_tab', 'test_stat_tab'::regclass); + + CREATE FUNCTION test_stat_func() RETURNS VOID LANGUAGE plpgsql AS $$BEGIN END;$$; + INSERT INTO test_stat_oid(name, oid) VALUES('test_stat_func', 'test_stat_func'::regproc); + + CREATE FUNCTION test_stat_func2() RETURNS VOID LANGUAGE plpgsql AS $$BEGIN END;$$; + INSERT INTO test_stat_oid(name, oid) VALUES('test_stat_func2', 'test_stat_func2'::regproc); + + CREATE TABLE test_slru_stats(slru TEXT, stat TEXT, value INT); + + -- calls test_stat_func, but hides error if it doesn't exist + CREATE FUNCTION test_stat_func_ifexists() RETURNS VOID LANGUAGE plpgsql AS $$ + BEGIN + PERFORM test_stat_func(); + EXCEPTION WHEN undefined_function THEN + END;$$; + + SELECT pg_stat_force_next_flush(); +} + +teardown +{ + DROP TABLE test_stat_oid; + DROP TABLE test_slru_stats; + + DROP TABLE IF EXISTS test_stat_tab; + DROP FUNCTION IF EXISTS test_stat_func(); + DROP FUNCTION IF EXISTS test_stat_func2(); + DROP FUNCTION test_stat_func_ifexists(); +} + +session s1 +setup { SET stats_fetch_consistency = 'none'; } +step s1_fetch_consistency_none { SET stats_fetch_consistency = 'none'; } +step s1_fetch_consistency_cache { SET stats_fetch_consistency = 'cache'; } +step s1_fetch_consistency_snapshot { SET stats_fetch_consistency = 'snapshot'; } +step s1_clear_snapshot { SELECT pg_stat_clear_snapshot(); } +step s1_begin { BEGIN; } +step s1_commit { COMMIT; } +step s1_rollback { ROLLBACK; } +step s1_prepare_a { PREPARE TRANSACTION 'a'; } +step s1_commit_prepared_a { COMMIT PREPARED 'a'; } +step s1_rollback_prepared_a { ROLLBACK PREPARED 'a'; } + +# Function stats steps +step s1_ff { SELECT pg_stat_force_next_flush(); } +step s1_track_funcs_all { SET track_functions = 'all'; } +step s1_track_funcs_none { SET track_functions = 'none'; } +step s1_func_call { SELECT test_stat_func(); } +step s1_func_drop { DROP FUNCTION test_stat_func(); } +step s1_func_stats_reset { SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc); } +step s1_func_stats_reset_nonexistent { SELECT pg_stat_reset_single_function_counters(12000); } +step s1_reset { SELECT pg_stat_reset(); } +step s1_func_stats { + SELECT + tso.name, + pg_stat_get_function_calls(tso.oid), + pg_stat_get_function_total_time(tso.oid) > 0 total_above_zero, + pg_stat_get_function_self_time(tso.oid) > 0 self_above_zero + FROM test_stat_oid AS tso + WHERE tso.name = 'test_stat_func' +} +step s1_func_stats2 { + SELECT + tso.name, + pg_stat_get_function_calls(tso.oid), + pg_stat_get_function_total_time(tso.oid) > 0 total_above_zero, + pg_stat_get_function_self_time(tso.oid) > 0 self_above_zero + FROM test_stat_oid AS tso + WHERE tso.name = 'test_stat_func2' +} +step s1_func_stats_nonexistent { + SELECT pg_stat_get_function_calls(12000); +} + +# Relation stats steps +step s1_track_counts_on { SET track_counts = on; } +step s1_track_counts_off { SET track_counts = off; } +step s1_table_select { SELECT * FROM test_stat_tab ORDER BY key, value; } +step s1_table_insert { INSERT INTO test_stat_tab(key, value) VALUES('k1', 1), ('k2', 1), ('k3', 1);} +step s1_table_insert_k1 { INSERT INTO test_stat_tab(key, value) VALUES('k1', 1);} +step s1_table_update_k1 { UPDATE test_stat_tab SET value = value + 1 WHERE key = 'k1';} +step s1_table_update_k2 { UPDATE test_stat_tab SET value = value + 1 WHERE key = 'k2';} +step s1_table_delete_k1 { DELETE FROM test_stat_tab WHERE key = 'k1';} +step s1_table_truncate { TRUNCATE test_stat_tab; } +step s1_table_drop { DROP TABLE test_stat_tab; } + +step s1_table_stats { + SELECT + pg_stat_get_numscans(tso.oid) AS seq_scan, + pg_stat_get_tuples_returned(tso.oid) AS seq_tup_read, + pg_stat_get_tuples_inserted(tso.oid) AS n_tup_ins, + pg_stat_get_tuples_updated(tso.oid) AS n_tup_upd, + pg_stat_get_tuples_deleted(tso.oid) AS n_tup_del, + pg_stat_get_live_tuples(tso.oid) AS n_live_tup, + pg_stat_get_dead_tuples(tso.oid) AS n_dead_tup, + pg_stat_get_vacuum_count(tso.oid) AS vacuum_count + FROM test_stat_oid AS tso + WHERE tso.name = 'test_stat_tab' +} + +# SLRU stats steps +step s1_slru_save_stats { + INSERT INTO test_slru_stats VALUES('Notify', 'blks_zeroed', + (SELECT blks_zeroed FROM pg_stat_slru WHERE name = 'Notify')); +} +step s1_listen { LISTEN stats_test_nothing; } +step s1_big_notify { SELECT pg_notify('stats_test_use', + repeat(i::text, current_setting('block_size')::int / 2)) FROM generate_series(1, 3) g(i); + } + +step s1_slru_check_stats { + SELECT current.blks_zeroed > before.value + FROM test_slru_stats before + INNER JOIN pg_stat_slru current + ON before.slru = current.name + WHERE before.stat = 'blks_zeroed'; + } + + +session s2 +setup { SET stats_fetch_consistency = 'none'; } +step s2_begin { BEGIN; } +step s2_commit { COMMIT; } +step s2_commit_prepared_a { COMMIT PREPARED 'a'; } +step s2_rollback_prepared_a { ROLLBACK PREPARED 'a'; } +step s2_ff { SELECT pg_stat_force_next_flush(); } + +# Function stats steps +step s2_track_funcs_all { SET track_functions = 'all'; } +step s2_track_funcs_none { SET track_functions = 'none'; } +step s2_func_call { SELECT test_stat_func() } +step s2_func_call_ifexists { SELECT test_stat_func_ifexists(); } +step s2_func_call2 { SELECT test_stat_func2() } +step s2_func_stats { + SELECT + tso.name, + pg_stat_get_function_calls(tso.oid), + pg_stat_get_function_total_time(tso.oid) > 0 total_above_zero, + pg_stat_get_function_self_time(tso.oid) > 0 self_above_zero + FROM test_stat_oid AS tso + WHERE tso.name = 'test_stat_func' +} + +# Relation stats steps +step s2_table_select { SELECT * FROM test_stat_tab ORDER BY key, value; } +step s2_table_update_k1 { UPDATE test_stat_tab SET value = value + 1 WHERE key = 'k1';} + +# SLRU stats steps +step s2_big_notify { SELECT pg_notify('stats_test_use', + repeat(i::text, current_setting('block_size')::int / 2)) FROM generate_series(1, 3) g(i); + } + + +###################### +# Function stats tests +###################### + +# check that stats are collected iff enabled +permutation + s1_track_funcs_none s1_func_stats s1_func_call s1_func_call s1_ff s1_func_stats +permutation + s1_track_funcs_all s1_func_stats s1_func_call s1_func_call s1_ff s1_func_stats + +# multiple function calls are accurately reported, across separate connections +permutation + s1_track_funcs_all s2_track_funcs_all s1_func_stats s2_func_stats + s1_func_call s2_func_call s1_func_call s2_func_call s2_func_call s1_ff s2_ff s1_func_stats s2_func_stats +permutation + s1_track_funcs_all s2_track_funcs_all s1_func_stats s2_func_stats + s1_func_call s1_ff s2_func_call s2_func_call s2_ff s1_func_stats s2_func_stats +permutation + s1_track_funcs_all s2_track_funcs_all s1_func_stats s2_func_stats + s1_begin s1_func_call s1_func_call s1_commit s1_ff s1_func_stats s2_func_stats + + +### Check interaction between dropping and stats reporting + +# Some of these tests try to test behavior in cases where no invalidation +# processing is triggered. To prevent output changes when +# debug_discard_caches, CATCACHE_FORCE_RELEASE or RELCACHE_FORCE_RELEASE are +# used (which trigger invalidation processing in paths that normally don't), +# test_stat_func_ifexists() can be used, which tries to call test_stat_func(), +# but doesn't raise an error if the function doesn't exist. + +# dropping a table remove stats iff committed +permutation + s1_track_funcs_all s2_track_funcs_all s1_func_stats s2_func_stats + s1_begin s1_func_call s2_func_call s1_func_drop s2_func_call s2_ff s2_func_stats s1_commit s1_ff s1_func_stats s2_func_stats +permutation + s1_track_funcs_all s2_track_funcs_all s1_func_stats s2_func_stats + s1_begin s1_func_call s2_func_call s1_func_drop s2_func_call s2_ff s2_func_stats s1_rollback s1_ff s1_func_stats s2_func_stats + +# Verify that pending stats from before a drop do not lead to +# reviving stats for a dropped object +permutation + s1_track_funcs_all s2_track_funcs_all + s2_func_call s2_ff # this access increments refcount, preventing the shared entry from being dropped + s2_begin s2_func_call_ifexists s1_func_drop s1_func_stats s2_commit s2_ff s1_func_stats s2_func_stats +permutation + s1_track_funcs_all s2_track_funcs_all + s2_begin s2_func_call_ifexists s1_func_drop s1_func_stats s2_commit s2_ff s1_func_stats s2_func_stats +permutation + s1_track_funcs_all s2_track_funcs_all + s1_func_call s2_begin s2_func_call_ifexists s1_func_drop s2_func_call_ifexists s2_commit s2_ff s1_func_stats s2_func_stats + +# Function calls don't necessarily trigger cache invalidation processing. The +# default handling of dropped stats could therefore end up with stats getting +# revived by a function call done after stats processing - but +# pgstat_init_function_usage() protects against that if track_functions is +# on. Verify that the stats are indeed dropped, and document the behavioral +# difference between track_functions settings. +permutation + s1_track_funcs_all s2_track_funcs_none + s1_func_call s2_begin s2_func_call_ifexists s1_ff s1_func_stats s1_func_drop s2_track_funcs_none s1_func_stats s2_func_call_ifexists s2_commit s2_ff s1_func_stats s2_func_stats +permutation + s1_track_funcs_all s2_track_funcs_none + s1_func_call s2_begin s2_func_call_ifexists s1_ff s1_func_stats s1_func_drop s2_track_funcs_all s1_func_stats s2_func_call_ifexists s2_commit s2_ff s1_func_stats s2_func_stats + +# test pg_stat_reset_single_function_counters +permutation + s1_track_funcs_all s2_track_funcs_all + s1_func_call + s2_func_call + s2_func_call2 + s1_ff s2_ff + s1_func_stats + s2_func_call s2_func_call2 s2_ff + s1_func_stats s1_func_stats2 s1_func_stats + s1_func_stats_reset + s1_func_stats s1_func_stats2 s1_func_stats + +# test pg_stat_reset_single_function_counters of non-existing function +permutation + s1_func_stats_nonexistent + s1_func_stats_reset_nonexistent + s1_func_stats_nonexistent + +# test pg_stat_reset +permutation + s1_track_funcs_all s2_track_funcs_all + s1_func_call + s2_func_call + s2_func_call2 + s1_ff s2_ff + s1_func_stats s1_func_stats2 s1_func_stats + s1_reset + s1_func_stats s1_func_stats2 s1_func_stats + + +### Check the different snapshot consistency models + +# First just some dead-trivial test verifying each model doesn't crash +permutation + s1_track_funcs_all s1_fetch_consistency_none s1_func_call s1_ff s1_func_stats +permutation + s1_track_funcs_all s1_fetch_consistency_cache s1_func_call s1_ff s1_func_stats +permutation + s1_track_funcs_all s1_fetch_consistency_snapshot s1_func_call s1_ff s1_func_stats + +# with stats_fetch_consistency=none s1 should see flushed changes in s2, despite being in a transaction +permutation + s1_track_funcs_all s2_track_funcs_all + s1_fetch_consistency_none + s2_func_call s2_ff + s1_begin + s1_func_stats + s2_func_call s2_ff + s1_func_stats + s1_commit + +# with stats_fetch_consistency=cache s1 should not see concurrent +# changes to the same object after the first access, but a separate +# object should show changes +permutation + s1_track_funcs_all s2_track_funcs_all + s1_fetch_consistency_cache + s2_func_call s2_func_call2 s2_ff + s1_begin + s1_func_stats + s2_func_call s2_func_call2 s2_ff + s1_func_stats s1_func_stats2 + s1_commit + +# with stats_fetch_consistency=snapshot s1 should not see any +# concurrent changes after the first access +permutation + s1_track_funcs_all s2_track_funcs_all + s1_fetch_consistency_snapshot + s2_func_call s2_func_call2 s2_ff + s1_begin + s1_func_stats + s2_func_call s2_func_call2 s2_ff + s1_func_stats s1_func_stats2 + s1_commit + +# Check access to non-existing stats works correctly and repeatedly +permutation + s1_fetch_consistency_none + s1_begin + s1_func_stats_nonexistent + s1_func_stats_nonexistent + s1_commit +permutation + s1_fetch_consistency_cache + s1_begin + s1_func_stats_nonexistent + s1_func_stats_nonexistent + s1_commit +permutation + s1_fetch_consistency_snapshot + s1_begin + s1_func_stats_nonexistent + s1_func_stats_nonexistent + s1_commit + + +### Check 2PC handling of stat drops + +# S1 prepared, S1 commits prepared +permutation + s1_track_funcs_all s2_track_funcs_all + s1_begin + s1_func_call + s2_func_call + s1_func_drop + s2_func_call + s2_ff + s1_prepare_a + s2_func_call + s2_ff + s1_func_call + s1_ff + s1_func_stats + s1_commit_prepared_a + s1_func_stats + +# S1 prepared, S1 aborts prepared +permutation + s1_track_funcs_all s2_track_funcs_all + s1_begin + s1_func_call + s2_func_call + s1_func_drop + s2_func_call + s2_ff + s1_prepare_a + s2_func_call + s2_ff + s1_func_call + s1_ff + s1_func_stats + s1_rollback_prepared_a + s1_func_stats + +# S1 prepares, S2 commits prepared +permutation + s1_track_funcs_all s2_track_funcs_all + s1_begin + s1_func_call + s2_func_call + s1_func_drop + s2_func_call + s2_ff + s1_prepare_a + s2_func_call + s2_ff + s1_func_call + s1_ff + s1_func_stats + s2_commit_prepared_a + s1_func_stats + +# S1 prepared, S2 aborts prepared +permutation + s1_track_funcs_all s2_track_funcs_all + s1_begin + s1_func_call + s2_func_call + s1_func_drop + s2_func_call + s2_ff + s1_prepare_a + s2_func_call + s2_ff + s1_func_call + s1_ff + s1_func_stats + s2_rollback_prepared_a + s1_func_stats + + +###################### +# Table stats tests +###################### + +# Most of the stats handling mechanism has already been tested in the function +# stats tests above - that's cheaper than testing with relations. But +# particularly for 2PC there are special cases + + +### Verify that pending stats from before a drop do not lead to reviving +### of stats for a dropped object + +permutation + s1_table_select + s1_table_insert + s2_table_select + s2_table_update_k1 + s1_ff + s2_table_update_k1 + s1_table_drop + s2_ff + s1_table_stats + +permutation + s1_table_select + s1_table_insert + s2_table_select + s2_table_update_k1 + s2_table_update_k1 + s1_table_drop + s1_table_stats + + +### Check that we don't count changes with track counts off, but allow access +### to prior stats + +# simple read access with stats off +permutation + s1_track_counts_off + s1_table_stats + s1_track_counts_on + +# simple read access with stats off, previously accessed +permutation + s1_table_select + s1_track_counts_off + s1_ff + s1_table_stats + s1_track_counts_on +permutation + s1_table_select + s1_ff + s1_track_counts_off + s1_table_stats + s1_track_counts_on + +# ensure we don't count anything with stats off +permutation + s1_track_counts_off + s1_table_select + s1_table_insert_k1 + s1_table_update_k1 + s2_table_select + s1_track_counts_on + s1_ff s2_ff + s1_table_stats + # but can count again after + s1_table_select + s1_table_update_k1 + s1_ff + s1_table_stats +permutation + s1_table_select + s1_table_insert_k1 + s1_table_delete_k1 + s1_track_counts_off + s1_table_select + s1_table_insert_k1 + s1_table_update_k1 + s2_table_select + s1_track_counts_on + s1_ff s2_ff + s1_table_stats + s1_table_select + s1_table_update_k1 + s1_ff + s1_table_stats + + +### 2PC: transactional and non-transactional counters work correctly + +# S1 prepares, S2 commits prepared +permutation + s1_begin + s1_table_insert s1_table_update_k1 s1_table_update_k1 s1_table_update_k2 s1_table_update_k2 s1_table_update_k2 s1_table_delete_k1 + s1_table_select + s1_prepare_a + s1_table_select + s1_commit_prepared_a + s1_table_select + s1_ff + s1_table_stats + +# S1 prepares, S2 commits prepared +permutation + s1_begin + s1_table_insert s1_table_update_k1 s1_table_update_k1 s1_table_update_k2 s1_table_update_k2 s1_table_update_k2 s1_table_delete_k1 + s1_table_select + s1_prepare_a + s1_table_select + s2_commit_prepared_a + s1_table_select + s1_ff s2_ff + s1_table_stats + +# S1 prepares, S2 commits prepared +permutation + s1_begin + s1_table_insert s1_table_update_k1 s1_table_update_k1 s1_table_update_k2 s1_table_update_k2 s1_table_update_k2 s1_table_delete_k1 + s1_table_select + s1_prepare_a + s1_table_select + s1_rollback_prepared_a + s1_table_select + s1_ff + s1_table_stats + +# S1 prepares, S1 aborts prepared +permutation + s1_begin + s1_table_insert s1_table_update_k1 s1_table_update_k1 s1_table_update_k2 s1_table_update_k2 s1_table_update_k2 s1_table_delete_k1 + s1_table_select + s1_prepare_a + s1_table_select + s2_rollback_prepared_a + s1_table_select + s1_ff s2_ff + s1_table_stats + + +### 2PC: truncate handling + +# S1 prepares, S1 commits prepared +permutation + s1_table_insert + s1_begin + s1_table_update_k1 # should *not* be counted, different rel + s1_table_update_k1 # dito + s1_table_truncate + s1_table_insert_k1 # should be counted + s1_table_update_k1 # dito + s1_prepare_a + s1_commit_prepared_a + s1_ff + s1_table_stats + +# S1 prepares, S2 commits prepared +permutation + s1_table_insert + s1_begin + s1_table_update_k1 # should *not* be counted, different rel + s1_table_update_k1 # dito + s1_table_truncate + s1_table_insert_k1 # should be counted + s1_table_update_k1 # dito + s1_prepare_a + s1_ff # flush out non-transactional stats, might happen anyway + s2_commit_prepared_a + s2_ff + s1_table_stats + +# S1 prepares, S1 aborts prepared +permutation + s1_table_insert + s1_begin + s1_table_update_k1 # should be counted + s1_table_update_k1 # dito + s1_table_truncate + s1_table_insert_k1 # should *not* be counted, different rel + s1_table_update_k1 # dito + s1_prepare_a + s1_rollback_prepared_a + s1_ff + s1_table_stats + +# S1 prepares, S2 aborts prepared +permutation + s1_table_insert + s1_begin + s1_table_update_k1 # should be counted + s1_table_update_k1 # dito + s1_table_truncate + s1_table_insert_k1 # should *not* be counted, different rel + s1_table_update_k1 # dito + s1_prepare_a + s2_rollback_prepared_a + s1_ff s2_ff + s1_table_stats + + +### 2PC: rolled back drop maintains live / dead counters + +# S1 prepares, S1 aborts prepared +permutation + s1_table_insert + s1_table_update_k1 + s1_begin + # should all be counted + s1_table_delete_k1 + s1_table_insert_k1 + s1_table_update_k1 + s1_table_update_k1 + s1_table_drop + s1_prepare_a + s1_rollback_prepared_a + s1_ff + s1_table_stats + +# S1 prepares, S1 aborts prepared +permutation + s1_table_insert + s1_table_update_k1 + s1_begin + # should all be counted + s1_table_delete_k1 + s1_table_insert_k1 + s1_table_update_k1 + s1_table_update_k1 + s1_table_drop + s1_prepare_a + s2_rollback_prepared_a + s1_ff s2_ff + s1_table_stats + + +###################### +# SLRU stats tests +###################### + +# Verify SLRU stats generated in own transaction +permutation + s1_slru_save_stats + s1_listen + s1_begin + s1_big_notify + s1_ff + s1_slru_check_stats + s1_commit + s1_slru_check_stats + +# Verify SLRU stats generated in separate transaction +permutation + s1_slru_save_stats + s1_listen + s2_big_notify + s2_ff + s1_slru_check_stats + +# shouldn't see stats yet, not committed +permutation + s1_slru_save_stats + s1_listen + s2_begin + s2_big_notify + s2_ff + s1_slru_check_stats + s2_commit + + +### Check the different snapshot consistency models for fixed-amount statistics + +permutation + s1_fetch_consistency_none + s1_slru_save_stats s1_listen + s1_begin + s1_slru_check_stats + s2_big_notify + s2_ff + s1_slru_check_stats + s1_commit + s1_slru_check_stats +permutation + s1_fetch_consistency_cache + s1_slru_save_stats s1_listen + s1_begin + s1_slru_check_stats + s2_big_notify + s2_ff + s1_slru_check_stats + s1_commit + s1_slru_check_stats +permutation + s1_fetch_consistency_snapshot + s1_slru_save_stats s1_listen + s1_begin + s1_slru_check_stats + s2_big_notify + s2_ff + s1_slru_check_stats + s1_commit + s1_slru_check_stats + +# check that pg_stat_clear_snapshot(), well ... +permutation + s1_fetch_consistency_none + s1_slru_save_stats s1_listen + s1_begin + s1_slru_check_stats + s2_big_notify + s2_ff + s1_slru_check_stats + s1_clear_snapshot + s1_slru_check_stats + s1_commit +permutation + s1_fetch_consistency_cache + s1_slru_save_stats s1_listen + s1_begin + s1_slru_check_stats + s2_big_notify + s2_ff + s1_slru_check_stats + s1_clear_snapshot + s1_slru_check_stats + s1_commit +permutation + s1_fetch_consistency_snapshot + s1_slru_save_stats s1_listen + s1_begin + s1_slru_check_stats + s2_big_notify + s2_ff + s1_slru_check_stats + s1_clear_snapshot + s1_slru_check_stats + s1_commit + +# check that a variable-amount stats access caches fixed-amount stat too +permutation + s1_fetch_consistency_snapshot + s1_slru_save_stats s1_listen + s1_begin + s1_func_stats + s2_big_notify + s2_ff + s1_slru_check_stats + s1_commit + +# and the other way round +permutation + s1_fetch_consistency_snapshot + s1_slru_save_stats s1_listen + s1_begin + s2_big_notify + s2_ff + s1_slru_check_stats + s2_func_call + s2_ff + s1_func_stats + s1_clear_snapshot + s1_func_stats + s1_commit |