path: root/src/test/isolation/specs/stats.spec

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