On Thu, Mar 17, 2022 at 3:36 AM Andres Freund <and...@anarazel.de> wrote:
> I've attached a substantially improved version of the shared memory stats
> patch.
...
> - lot of the pg_stat_ views like bgwriter, pg_stat_database have zero
> coverage today
Attached are some tests including tests that reset of stats works for
all views having a reset timestamp as well as a basic test for at least
one column in all of the following stats views:
pg_stat_archiver, pg_stat_bgwriter, pg_stat_wal, pg_stat_slru,
pg_stat_replication_slots, pg_stat_database
It might be nice to have a test for one of the columns fetched from the
PgStatBgwriter data structure since those and the Checkpointer stats are
stored separately despite being displayed in the same view currently.
but, alas...
- Melanie
From 37e5ba3b7743309b00c81dbfe65cfd481d4859a6 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Tue, 22 Mar 2022 16:53:32 -0400
Subject: [PATCH] test
---
src/test/isolation/expected/stats.out | 35 ++++
src/test/isolation/specs/stats.spec | 31 +++
src/test/recovery/t/006_logical_decoding.pl | 63 ++++++
src/test/regress/expected/stats.out | 208 ++++++++++++++++++++
src/test/regress/sql/stats.sql | 107 ++++++++++
5 files changed, 444 insertions(+)
diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out
index ca553be3bf..7a156e1da2 100644
--- a/src/test/isolation/expected/stats.out
+++ b/src/test/isolation/expected/stats.out
@@ -1895,3 +1895,38 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| 5|t |t
(1 row)
+
+starting permutation: s1_slru_save_stats s1_listen s1_big_notify s1_ff s1_slru_check_stats
+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 big_notify;
+step s1_big_notify: SELECT pg_notify('big_notify',
+ repeat('0', current_setting('block_size')::int / 2)) FROM generate_series(1, 2);
+
+pg_notify
+---------
+
+
+(2 rows)
+
+s1: NOTIFY "big_notify" with payload "0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" from s1
+step s1_ff: SELECT pg_stat_force_next_flush();
+pg_stat_force_next_flush
+------------------------
+
+(1 row)
+
+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';
+
+?column?
+--------
+t
+(1 row)
+
diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec
index 752a71c478..4970b5f9d4 100644
--- a/src/test/isolation/specs/stats.spec
+++ b/src/test/isolation/specs/stats.spec
@@ -11,11 +11,14 @@ setup
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);
}
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();
@@ -58,8 +61,25 @@ step "s1_func_stats2" {
FROM test_stat_oid AS tso
WHERE tso.name = 'test_stat_func2'
}
+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 big_notify; }
+step "s1_big_notify" { SELECT pg_notify('big_notify',
+ repeat('0', current_setting('block_size')::int / 2)) FROM generate_series(1, 2);
+ }
+
+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';
+ }
#step "s1_func_stats_debug" {SELECT * FROM pg_stat_user_functions;}
+
session "s2"
setup { SET stats_fetch_consistency = 'none'; }
step "s2_track_funcs_all" { SET track_functions = 'all'; }
@@ -285,3 +305,14 @@ permutation
"s1_func_stats"
"s2_rollback_prepared_a"
"s1_func_stats"
+
+######################
+# SLRU stats tests
+######################
+
+permutation
+ "s1_slru_save_stats"
+ "s1_listen"
+ "s1_big_notify"
+ "s1_ff"
+ "s1_slru_check_stats"
diff --git a/src/test/recovery/t/006_logical_decoding.pl b/src/test/recovery/t/006_logical_decoding.pl
index 9cec2792fc..3ccced2ea2 100644
--- a/src/test/recovery/t/006_logical_decoding.pl
+++ b/src/test/recovery/t/006_logical_decoding.pl
@@ -200,6 +200,69 @@ chomp($logical_restart_lsn_post);
ok(($logical_restart_lsn_pre cmp $logical_restart_lsn_post) == 0,
"logical slot advance persists across restarts");
+my $stats_test_slot1 = 'test_slot';
+my $stats_test_slot2 = 'logical_slot';
+
+# Test that reset works for pg_stat_replication_slots
+
+# Stats exist for stats test slot 1
+is($node_primary->safe_psql(
+ 'postgres',
+ qq(SELECT total_bytes > 0, stats_reset IS NULL FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot1')
+), qq(t|t), qq(Total bytes is > 0 and stats_reset is NULL for slot '$stats_test_slot1'.));
+
+# Do reset of stats for stats test slot 1
+$node_primary->safe_psql(
+ 'postgres',
+ qq(SELECT pg_stat_reset_replication_slot('$stats_test_slot1'))
+);
+
+# Get reset value after reset
+my $reset1 = $node_primary->safe_psql(
+ 'postgres',
+ qq(SELECT stats_reset FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot1')
+);
+
+# Do reset again
+$node_primary->safe_psql(
+ 'postgres',
+ qq(SELECT pg_stat_reset_replication_slot('$stats_test_slot1'))
+);
+
+is($node_primary->safe_psql(
+ 'postgres',
+ qq(SELECT stats_reset > '$reset1'::timestamptz, total_bytes = 0 FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot1')
+), qq(t|t), qq(Check that reset timestamp is later after the second reset of stats for slot '$stats_test_slot1' and confirm total_bytes was set to 0.));
+
+# Check that test slot 2 has NULL in reset timestamp
+is($node_primary->safe_psql(
+ 'postgres',
+ qq(SELECT stats_reset IS NULL FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot2')
+), qq(t), qq(Stats_reset is NULL for slot '$stats_test_slot2' before reset.));
+
+# Get reset value again for test slot 1
+$reset1 = $node_primary->safe_psql(
+ 'postgres',
+ qq(SELECT stats_reset FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot1')
+);
+
+# Reset stats for all replication slots
+$node_primary->safe_psql(
+ 'postgres',
+ qq(SELECT pg_stat_reset_replication_slot(NULL))
+);
+
+# Check that test slot 2 reset timestamp is no longer NULL after reset
+is($node_primary->safe_psql(
+ 'postgres',
+ qq(SELECT stats_reset IS NOT NULL FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot2')
+), qq(t), qq(Stats_reset is not NULL for slot '$stats_test_slot2' after reset all.));
+
+is($node_primary->safe_psql(
+ 'postgres',
+ qq(SELECT stats_reset > '$reset1'::timestamptz FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot1')
+), qq(t), qq(Check that reset timestamp is later after resetting stats for slot '$stats_test_slot1' again.));
+
# done with the node
$node_primary->stop;
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 741c2d3e4f..da81038f8c 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -515,4 +515,212 @@ SELECT pg_stat_get_function_calls(:stats_test_func2_oid);
DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
DROP TABLE prevstats;
+-----
+-- Test that various stats views are being properly populated
+-----
+-- Test that sessions is incremented when a new session is started in pg_stat_database
+SELECT sessions AS db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+\c
+SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database());
+ ?column?
+----------
+ t
+(1 row)
+
+-- Test pg_stat_bgwriter checkpointer-related stat
+SELECT checkpoints_req AS rqst_ckpts_before FROM pg_stat_bgwriter \gset
+-- Test pg_stat_wal
+SELECT wal_bytes AS wal_bytes_before FROM pg_stat_wal \gset
+CREATE TABLE test_stats_temp(a int);
+INSERT INTO test_stats_temp SELECT 1 FROM generate_series(1,1000)i;
+CHECKPOINT;
+SELECT checkpoints_req > :rqst_ckpts_before FROM pg_stat_bgwriter;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT wal_bytes > :wal_bytes_before FROM pg_stat_wal;
+ ?column?
+----------
+ t
+(1 row)
+
+-----
+-- Test that resetting stats works for reset timestamp
+-----
+SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'CommitTs' \gset
+SELECT stats_reset AS slru_notify_reset_ts FROM pg_stat_slru WHERE name = 'Notify' \gset
+-- Test that reset_slru with a specified SLRU works.
+SELECT pg_stat_reset_slru('CommitTs');
+ pg_stat_reset_slru
+--------------------
+
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'CommitTs';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'CommitTs' \gset
+-- Test that multiple SLRUs are reset when no specific SLRU provided to reset function
+SELECT pg_stat_reset_slru(NULL);
+ pg_stat_reset_slru
+--------------------
+
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'CommitTs';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'Notify';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
+-- Test that reset_shared with archiver specified as the stats type works
+SELECT pg_stat_reset_shared('archiver');
+ pg_stat_reset_shared
+----------------------
+
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
+SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
+-- Test that reset_shared with bgwriter specified as the stats type works
+SELECT pg_stat_reset_shared('bgwriter');
+ pg_stat_reset_shared
+----------------------
+
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
+SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
+-- Test that reset_shared with wal specified as the stats type works
+SELECT pg_stat_reset_shared('wal');
+ pg_stat_reset_shared
+----------------------
+
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
+-- Test that reset_shared with no specified stats type resets all of them
+SELECT pg_stat_reset_shared(NULL);
+ pg_stat_reset_shared
+----------------------
+
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
+ ?column?
+----------
+ f
+(1 row)
+
+-- Test that reset works for pg_stat_database
+-- Since pg_stat_database stats_reset starts out as NULL, reset it once first so we have something to compare it to
+SELECT pg_stat_reset();
+ pg_stat_reset
+---------------
+
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+SELECT pg_stat_reset();
+ pg_stat_reset
+---------------
+
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
+ ?column?
+----------
+ t
+(1 row)
+
-- End of Stats Test
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 1e156ca5e4..cd3dc70edb 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -281,4 +281,111 @@ SELECT pg_stat_get_function_calls(:stats_test_func2_oid);
DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
DROP TABLE prevstats;
+-----
+-- Test that various stats views are being properly populated
+-----
+
+-- Test that sessions is incremented when a new session is started in pg_stat_database
+SELECT sessions AS db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+\c
+SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database());
+
+-- Test pg_stat_bgwriter checkpointer-related stat
+
+SELECT checkpoints_req AS rqst_ckpts_before FROM pg_stat_bgwriter \gset
+
+-- Test pg_stat_wal
+
+SELECT wal_bytes AS wal_bytes_before FROM pg_stat_wal \gset
+
+CREATE TABLE test_stats_temp(a int);
+INSERT INTO test_stats_temp SELECT 1 FROM generate_series(1,1000)i;
+
+CHECKPOINT;
+
+SELECT checkpoints_req > :rqst_ckpts_before FROM pg_stat_bgwriter;
+SELECT wal_bytes > :wal_bytes_before FROM pg_stat_wal;
+
+-----
+-- Test that resetting stats works for reset timestamp
+-----
+
+SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'CommitTs' \gset
+
+SELECT stats_reset AS slru_notify_reset_ts FROM pg_stat_slru WHERE name = 'Notify' \gset
+
+-- Test that reset_slru with a specified SLRU works.
+SELECT pg_stat_reset_slru('CommitTs');
+
+SELECT pg_stat_force_next_flush();
+
+SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'CommitTs';
+
+SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'CommitTs' \gset
+
+-- Test that multiple SLRUs are reset when no specific SLRU provided to reset function
+SELECT pg_stat_reset_slru(NULL);
+
+SELECT pg_stat_force_next_flush();
+
+SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'CommitTs';
+SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'Notify';
+
+SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
+
+-- Test that reset_shared with archiver specified as the stats type works
+SELECT pg_stat_reset_shared('archiver');
+
+SELECT pg_stat_force_next_flush();
+
+SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
+
+SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
+
+SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
+
+-- Test that reset_shared with bgwriter specified as the stats type works
+SELECT pg_stat_reset_shared('bgwriter');
+
+SELECT pg_stat_force_next_flush();
+
+SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
+
+SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
+
+SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
+
+-- Test that reset_shared with wal specified as the stats type works
+SELECT pg_stat_reset_shared('wal');
+
+SELECT pg_stat_force_next_flush();
+
+SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
+
+SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
+
+-- Test that reset_shared with no specified stats type resets all of them
+SELECT pg_stat_reset_shared(NULL);
+
+SELECT pg_stat_force_next_flush();
+
+SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
+SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
+SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
+
+-- Test that reset works for pg_stat_database
+
+-- Since pg_stat_database stats_reset starts out as NULL, reset it once first so we have something to compare it to
+SELECT pg_stat_reset();
+
+SELECT pg_stat_force_next_flush();
+
+SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+
+SELECT pg_stat_reset();
+
+SELECT pg_stat_force_next_flush();
+
+SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
+
-- End of Stats Test
--
2.30.2
