pgstat: add/extend tests for resetting various kinds of stats.

- subscriber stats reset path was untested
- slot stat sreset path for all slots was untested
- pg_stat_database.sessions etc was untested
- pg_stat_reset_shared() was untested, for any kind of shared stats
- pg_stat_reset() was untested

Author: Melanie Plageman <melanieplageman@gmail.com>
Author: Andres Freund <andres@anarazel.de>
Discussion: https://postgr.es/m/20220303021600.hs34ghqcw6zcokdh@alap3.anarazel.de

2 files changed, 111 insertions, 38 deletions

diff --git a/contrib/test_decoding/expected/stats.out b/contrib/test_decoding/expected/stats.out
index d9aeaa44264..78d36429c8a 100644
--- a/contrib/test_decoding/expected/stats.out
+++ b/contrib/test_decoding/expected/stats.out
@@ -1,6 +1,9 @@
 -- predictability
 SET synchronous_commit = on;
-SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot_stats', 'test_decoding');
+SELECT 'init' FROM
+    pg_create_logical_replication_slot('regression_slot_stats1', 'test_decoding') s1,
+    pg_create_logical_replication_slot('regression_slot_stats2', 'test_decoding') s2,
+    pg_create_logical_replication_slot('regression_slot_stats3', 'test_decoding') s3;
  ?column? 
 ----------
  init
@@ -10,7 +13,19 @@ CREATE TABLE stats_test(data text);
 -- non-spilled xact
 SET logical_decoding_work_mem to '64MB';
 INSERT INTO stats_test values(1);
-SELECT count(*) FROM pg_logical_slot_get_changes('regression_slot_stats', NULL, NULL, 'skip-empty-xacts', '1');
+SELECT count(*) FROM pg_logical_slot_get_changes('regression_slot_stats1', NULL, NULL, 'skip-empty-xacts', '1');
+ count 
+-------
+     3
+(1 row)
+
+SELECT count(*) FROM pg_logical_slot_get_changes('regression_slot_stats2', NULL, NULL, 'skip-empty-xacts', '1');
+ count 
+-------
+     3
+(1 row)
+
+SELECT count(*) FROM pg_logical_slot_get_changes('regression_slot_stats3', NULL, NULL, 'skip-empty-xacts', '1');
  count 
 -------
      3
@@ -22,31 +37,65 @@ SELECT pg_stat_force_next_flush();
  
 (1 row)
 
-SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots;
-       slot_name       | spill_txns | spill_count | total_txns | total_bytes 
------------------------+------------+-------------+------------+-------------
- regression_slot_stats | t          | t           | t          | t
-(1 row)
+SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
+       slot_name        | spill_txns | spill_count | total_txns | total_bytes 
+------------------------+------------+-------------+------------+-------------
+ regression_slot_stats1 | t          | t           | t          | t
+ regression_slot_stats2 | t          | t           | t          | t
+ regression_slot_stats3 | t          | t           | t          | t
+(3 rows)
 
 RESET logical_decoding_work_mem;
--- reset the slot stats
-SELECT pg_stat_reset_replication_slot('regression_slot_stats');
+-- reset stats for one slot, others should be unaffected
+SELECT pg_stat_reset_replication_slot('regression_slot_stats1');
  pg_stat_reset_replication_slot 
 --------------------------------
  
 (1 row)
 
-SELECT slot_name, spill_txns, spill_count, total_txns, total_bytes FROM pg_stat_replication_slots;
-       slot_name       | spill_txns | spill_count | total_txns | total_bytes 
------------------------+------------+-------------+------------+-------------
- regression_slot_stats |          0 |           0 |          0 |           0
+SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
+       slot_name        | spill_txns | spill_count | total_txns | total_bytes 
+------------------------+------------+-------------+------------+-------------
+ regression_slot_stats1 | t          | t           | f          | f
+ regression_slot_stats2 | t          | t           | t          | t
+ regression_slot_stats3 | t          | t           | t          | t
+(3 rows)
+
+-- reset stats for all slots
+SELECT pg_stat_reset_replication_slot(NULL);
+ pg_stat_reset_replication_slot 
+--------------------------------
+ 
+(1 row)
+
+SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
+       slot_name        | spill_txns | spill_count | total_txns | total_bytes 
+------------------------+------------+-------------+------------+-------------
+ regression_slot_stats1 | t          | t           | f          | f
+ regression_slot_stats2 | t          | t           | f          | f
+ regression_slot_stats3 | t          | t           | f          | f
+(3 rows)
+
+-- verify accessing/resetting stats for non-existent slot does something reasonable
+SELECT * FROM pg_stat_get_replication_slot('do-not-exist');
+  slot_name   | spill_txns | spill_count | spill_bytes | stream_txns | stream_count | stream_bytes | total_txns | total_bytes | stats_reset 
+--------------+------------+-------------+-------------+-------------+--------------+--------------+------------+-------------+-------------
+ do-not-exist |          0 |           0 |           0 |           0 |            0 |            0 |          0 |           0 | 
+(1 row)
+
+SELECT pg_stat_reset_replication_slot('do-not-exist');
+ERROR:  replication slot "do-not-exist" does not exist
+SELECT * FROM pg_stat_get_replication_slot('do-not-exist');
+  slot_name   | spill_txns | spill_count | spill_bytes | stream_txns | stream_count | stream_bytes | total_txns | total_bytes | stats_reset 
+--------------+------------+-------------+-------------+-------------+--------------+--------------+------------+-------------+-------------
+ do-not-exist |          0 |           0 |           0 |           0 |            0 |            0 |          0 |           0 | 
 (1 row)
 
 -- spilling the xact
 BEGIN;
 INSERT INTO stats_test SELECT 'serialize-topbig--1:'||g.i FROM generate_series(1, 5000) g(i);
 COMMIT;
-SELECT count(*) FROM pg_logical_slot_peek_changes('regression_slot_stats', NULL, NULL, 'skip-empty-xacts', '1');
+SELECT count(*) FROM pg_logical_slot_peek_changes('regression_slot_stats1', NULL, NULL, 'skip-empty-xacts', '1');
  count 
 -------
   5002
@@ -62,31 +111,39 @@ SELECT pg_stat_force_next_flush();
 (1 row)
 
 SELECT slot_name, spill_txns > 0 AS spill_txns, spill_count > 0 AS spill_count FROM pg_stat_replication_slots;
-       slot_name       | spill_txns | spill_count 
------------------------+------------+-------------
- regression_slot_stats | t          | t
-(1 row)
+       slot_name        | spill_txns | spill_count 
+------------------------+------------+-------------
+ regression_slot_stats1 | t          | t
+ regression_slot_stats2 | f          | f
+ regression_slot_stats3 | f          | f
+(3 rows)
 
 -- Ensure stats can be repeatedly accessed using the same stats snapshot. See
 -- https://postgr.es/m/20210317230447.c7uc4g3vbs4wi32i%40alap3.anarazel.de
 BEGIN;
 SELECT slot_name FROM pg_stat_replication_slots;
-       slot_name       
------------------------
- regression_slot_stats
-(1 row)
+       slot_name        
+------------------------
+ regression_slot_stats1
+ regression_slot_stats2
+ regression_slot_stats3
+(3 rows)
 
 SELECT slot_name FROM pg_stat_replication_slots;
-       slot_name       
------------------------
- regression_slot_stats
-(1 row)
+       slot_name        
+------------------------
+ regression_slot_stats1
+ regression_slot_stats2
+ regression_slot_stats3
+(3 rows)
 
 COMMIT;
 DROP TABLE stats_test;
-SELECT pg_drop_replication_slot('regression_slot_stats');
- pg_drop_replication_slot 
---------------------------
- 
+SELECT pg_drop_replication_slot('regression_slot_stats1'),
+    pg_drop_replication_slot('regression_slot_stats2'),
+    pg_drop_replication_slot('regression_slot_stats3');
+ pg_drop_replication_slot | pg_drop_replication_slot | pg_drop_replication_slot 
+--------------------------+--------------------------+--------------------------
+                          |                          | 
 (1 row)
 
diff --git a/contrib/test_decoding/sql/stats.sql b/contrib/test_decoding/sql/stats.sql
index 6592c36a4d9..630371f147a 100644
--- a/contrib/test_decoding/sql/stats.sql
+++ b/contrib/test_decoding/sql/stats.sql
@@ -1,27 +1,41 @@
 -- predictability
 SET synchronous_commit = on;
 
-SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot_stats', 'test_decoding');
+SELECT 'init' FROM
+    pg_create_logical_replication_slot('regression_slot_stats1', 'test_decoding') s1,
+    pg_create_logical_replication_slot('regression_slot_stats2', 'test_decoding') s2,
+    pg_create_logical_replication_slot('regression_slot_stats3', 'test_decoding') s3;
 
 CREATE TABLE stats_test(data text);
 
 -- non-spilled xact
 SET logical_decoding_work_mem to '64MB';
 INSERT INTO stats_test values(1);
-SELECT count(*) FROM pg_logical_slot_get_changes('regression_slot_stats', NULL, NULL, 'skip-empty-xacts', '1');
+SELECT count(*) FROM pg_logical_slot_get_changes('regression_slot_stats1', NULL, NULL, 'skip-empty-xacts', '1');
+SELECT count(*) FROM pg_logical_slot_get_changes('regression_slot_stats2', NULL, NULL, 'skip-empty-xacts', '1');
+SELECT count(*) FROM pg_logical_slot_get_changes('regression_slot_stats3', NULL, NULL, 'skip-empty-xacts', '1');
 SELECT pg_stat_force_next_flush();
-SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots;
+SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
 RESET logical_decoding_work_mem;
 
--- reset the slot stats
-SELECT pg_stat_reset_replication_slot('regression_slot_stats');
-SELECT slot_name, spill_txns, spill_count, total_txns, total_bytes FROM pg_stat_replication_slots;
+-- reset stats for one slot, others should be unaffected
+SELECT pg_stat_reset_replication_slot('regression_slot_stats1');
+SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
+
+-- reset stats for all slots
+SELECT pg_stat_reset_replication_slot(NULL);
+SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
+
+-- verify accessing/resetting stats for non-existent slot does something reasonable
+SELECT * FROM pg_stat_get_replication_slot('do-not-exist');
+SELECT pg_stat_reset_replication_slot('do-not-exist');
+SELECT * FROM pg_stat_get_replication_slot('do-not-exist');
 
 -- spilling the xact
 BEGIN;
 INSERT INTO stats_test SELECT 'serialize-topbig--1:'||g.i FROM generate_series(1, 5000) g(i);
 COMMIT;
-SELECT count(*) FROM pg_logical_slot_peek_changes('regression_slot_stats', NULL, NULL, 'skip-empty-xacts', '1');
+SELECT count(*) FROM pg_logical_slot_peek_changes('regression_slot_stats1', NULL, NULL, 'skip-empty-xacts', '1');
 
 -- Check stats. We can't test the exact stats count as that can vary if any
 -- background transaction (say by autovacuum) happens in parallel to the main
@@ -37,4 +51,6 @@ SELECT slot_name FROM pg_stat_replication_slots;
 COMMIT;
 
 DROP TABLE stats_test;
-SELECT pg_drop_replication_slot('regression_slot_stats');
+SELECT pg_drop_replication_slot('regression_slot_stats1'),
+    pg_drop_replication_slot('regression_slot_stats2'),
+    pg_drop_replication_slot('regression_slot_stats3');