add_more_basic_stats_tests.patch

From 37e5ba3b7743309b00c81dbfe65cfd481d4859a6 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplageman@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