From 5264add7847871d61d36a5770dac2139d6a7bc80 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Thu, 7 Apr 2022 15:17:07 -0700 Subject: [PATCH] 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 Author: Andres Freund Discussion: https://postgr.es/m/20220303021600.hs34ghqcw6zcokdh@alap3.anarazel.de --- contrib/test_decoding/expected/stats.out | 117 +++++-- contrib/test_decoding/sql/stats.sql | 32 +- src/test/recovery/t/006_logical_decoding.pl | 63 ++++ src/test/regress/expected/stats.out | 164 ++++++++++ src/test/regress/parallel_schedule | 3 + src/test/regress/sql/stats.sql | 78 +++++ src/test/subscription/t/026_stats.pl | 318 +++++++++++++++----- 7 files changed, 664 insertions(+), 111 deletions(-) diff --git a/contrib/test_decoding/expected/stats.out b/contrib/test_decoding/expected/stats.out index d9aeaa4426..78d36429c8 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 6592c36a4d..630371f147 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'); 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 9713175930..6b233ff4c0 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -554,6 +554,170 @@ SELECT pg_stat_get_live_tuples(:drop_stats_test_subxact_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 stats, together with pg_stat_wal +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 AS SELECT 17; +DROP TABLE test_stats_temp; +-- Checkpoint twice: The checkpointer reports stats after reporting completion +-- of the checkpoint. But after a second checkpoint we'll see at least the +-- results of the first. +CHECKPOINT; +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 +----- +-- Test that reset_slru with a specified SLRU works. +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 +SELECT pg_stat_reset_slru('CommitTs'); + pg_stat_reset_slru +-------------------- + +(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 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) + +-- Test that reset_shared with archiver specified as the stats type works +SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset +SELECT pg_stat_reset_shared('archiver'); + pg_stat_reset_shared +---------------------- + +(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 +-- Test that reset_shared with bgwriter specified as the stats type works +SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset +SELECT pg_stat_reset_shared('bgwriter'); + pg_stat_reset_shared +---------------------- + +(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 +-- Test that reset_shared with wal specified as the stats type works +SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset +SELECT pg_stat_reset_shared('wal'); + pg_stat_reset_shared +---------------------- + +(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 doesn't reset anything +SELECT pg_stat_reset_shared(NULL); + pg_stat_reset_shared +---------------------- + +(1 row) + +SELECT stats_reset = :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver; + ?column? +---------- + t +(1 row) + +SELECT stats_reset = :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter; + ?column? +---------- + t +(1 row) + +SELECT stats_reset = :'wal_reset_ts'::timestamptz FROM pg_stat_wal; + ?column? +---------- + t +(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 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 stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database()); + ?column? +---------- + t +(1 row) + ---- -- pg_stat_get_snapshot_timestamp behavior ---- diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 1087b2c14f..103e11483d 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -123,6 +123,9 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion tr # ---------- # Another group of parallel tests +# +# The stats test resets stats, so nothing else needing stats access can be in +# this group. # ---------- test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression memoize stats diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 4d26671da7..096f00ce8b 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -286,6 +286,84 @@ SELECT pg_stat_get_live_tuples(:drop_stats_test_subxact_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 stats, together with pg_stat_wal +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 AS SELECT 17; +DROP TABLE test_stats_temp; + +-- Checkpoint twice: The checkpointer reports stats after reporting completion +-- of the checkpoint. But after a second checkpoint we'll see at least the +-- results of the first. +CHECKPOINT; +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 +----- + +-- Test that reset_slru with a specified SLRU works. +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 +SELECT pg_stat_reset_slru('CommitTs'); +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 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'; + +-- Test that reset_shared with archiver specified as the stats type works +SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset +SELECT pg_stat_reset_shared('archiver'); +SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver; +SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset + +-- Test that reset_shared with bgwriter specified as the stats type works +SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset +SELECT pg_stat_reset_shared('bgwriter'); +SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter; +SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset + +-- Test that reset_shared with wal specified as the stats type works +SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset +SELECT pg_stat_reset_shared('wal'); +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 doesn't reset anything +SELECT pg_stat_reset_shared(NULL); +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 stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset +SELECT pg_stat_reset(); +SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database()); + + ---- -- pg_stat_get_snapshot_timestamp behavior ---- diff --git a/src/test/subscription/t/026_stats.pl b/src/test/subscription/t/026_stats.pl index a42ea3170e..59a09ce6dc 100644 --- a/src/test/subscription/t/026_stats.pl +++ b/src/test/subscription/t/026_stats.pl @@ -18,83 +18,255 @@ my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber'); $node_subscriber->init(allows_streaming => 'logical'); $node_subscriber->start; -# Initial table setup on both publisher and subscriber. On subscriber we -# create the same tables but with primary keys. Also, insert some data that -# will conflict with the data replicated from publisher later. -$node_publisher->safe_psql( - 'postgres', - qq[ -BEGIN; -CREATE TABLE test_tab1 (a int); -INSERT INTO test_tab1 VALUES (1); -COMMIT; -]); -$node_subscriber->safe_psql( - 'postgres', - qq[ -BEGIN; -CREATE TABLE test_tab1 (a int primary key); -INSERT INTO test_tab1 VALUES (1); -COMMIT; -]); - -# Setup publication. -my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres'; -$node_publisher->safe_psql('postgres', - "CREATE PUBLICATION tap_pub FOR TABLE test_tab1;"); + +sub create_sub_pub_w_errors +{ + my ($node_publisher, $node_subscriber, $db, $table_name) = @_; + # Initial table setup on both publisher and subscriber. On subscriber we + # create the same tables but with primary keys. Also, insert some data that + # will conflict with the data replicated from publisher later. + $node_publisher->safe_psql( + $db, + qq[ + BEGIN; + CREATE TABLE $table_name(a int); + INSERT INTO $table_name VALUES (1); + COMMIT; + ]); + $node_subscriber->safe_psql( + $db, + qq[ + BEGIN; + CREATE TABLE $table_name(a int primary key); + INSERT INTO $table_name VALUES (1); + COMMIT; + ]); + + # Set up publication. + my $pub_name = $table_name . '_pub'; + my $publisher_connstr = $node_publisher->connstr . qq( dbname=$db); + + $node_publisher->safe_psql($db, + qq(CREATE PUBLICATION $pub_name FOR TABLE $table_name)); + + # Create subscription. The tablesync for table on subscription will enter into + # infinite error loop due to violating the unique constraint. + my $sub_name = $table_name . '_sub'; + $node_subscriber->safe_psql($db, + qq(CREATE SUBSCRIPTION $sub_name CONNECTION '$publisher_connstr' PUBLICATION $pub_name) + ); + + $node_publisher->wait_for_catchup($sub_name); + + # Wait for the tablesync error to be reported. + $node_subscriber->poll_query_until( + $db, + qq[ + SELECT sync_error_count > 0 + FROM pg_stat_subscription_stats + WHERE subname = '$sub_name' + ]) + or die + qq(Timed out while waiting for tablesync errors for subscription '$sub_name'); + + # Truncate test_tab1 so that tablesync worker can continue. + $node_subscriber->safe_psql($db, qq(TRUNCATE $table_name)); + + # Wait for initial tablesync to finish. + $node_subscriber->poll_query_until( + $db, + qq[ + SELECT count(1) = 1 FROM pg_subscription_rel + WHERE srrelid = '$table_name'::regclass AND srsubstate in ('r', 's') + ]) + or die + qq(Timed out while waiting for subscriber to synchronize data for table '$table_name'.); + + # Check test table on the subscriber has one row. + my $result = + $node_subscriber->safe_psql($db, qq(SELECT a FROM $table_name)); + is($result, qq(1), qq(Check that table '$table_name' now has 1 row.)); + + # Insert data to test table on the publisher, raising an error on the + # subscriber due to violation of the unique constraint on test table. + $node_publisher->safe_psql($db, qq(INSERT INTO $table_name VALUES (1))); + + # Wait for the apply error to be reported. + $node_subscriber->poll_query_until( + $db, + qq[ + SELECT apply_error_count > 0 + FROM pg_stat_subscription_stats + WHERE subname = '$sub_name' + ]) + or die + qq(Timed out while waiting for apply error for subscription '$sub_name'); + + # Truncate test table so that apply worker can continue. + $node_subscriber->safe_psql($db, qq(TRUNCATE $table_name)); + + return ($pub_name, $sub_name); +} + +my $db = 'postgres'; # There shouldn't be any subscription errors before starting logical replication. -my $result = $node_subscriber->safe_psql('postgres', - "SELECT count(1) FROM pg_stat_subscription_stats"); -is($result, qq(0), 'check no subscription error'); - -# Create subscription. The tablesync for test_tab1 on tap_sub will enter into -# infinite error loop due to violating the unique constraint. -$node_subscriber->safe_psql('postgres', - "CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr' PUBLICATION tap_pub;" +my $result = $node_subscriber->safe_psql($db, + qq(SELECT count(1) FROM pg_stat_subscription_stats)); +is($result, qq(0), + 'Check that there are no subscription errors before starting logical replication.' +); + +# Create the publication and subscription with sync and apply errors +my $table1_name = 'test_tab1'; +my ($pub1_name, $sub1_name) = + create_sub_pub_w_errors($node_publisher, $node_subscriber, $db, + $table1_name); + +# Apply and Sync errors are > 0 and reset timestamp is NULL +is( $node_subscriber->safe_psql( + $db, + qq(SELECT apply_error_count > 0, + sync_error_count > 0, + stats_reset IS NULL + FROM pg_stat_subscription_stats + WHERE subname = '$sub1_name') + ), + qq(t|t|t), + qq(Check that apply errors and sync errors are both > 0 and stats_reset is NULL for subscription '$sub1_name'.) +); + +# Reset a single subscription +$node_subscriber->safe_psql($db, + qq(SELECT pg_stat_reset_subscription_stats((SELECT subid FROM pg_stat_subscription_stats WHERE subname = '$sub1_name'))) +); + +# Apply and Sync errors are 0 and stats reset is not NULL +is( $node_subscriber->safe_psql( + $db, + qq(SELECT apply_error_count = 0, + sync_error_count = 0, + stats_reset IS NOT NULL + FROM pg_stat_subscription_stats + WHERE subname = '$sub1_name') + ), + qq(t|t|t), + qq(Confirm that apply errors and sync errors are both 0 and stats_reset is not NULL after reset for subscription '$sub1_name'.) +); + +# Get reset timestamp +my $reset_time1 = $node_subscriber->safe_psql($db, + qq(SELECT stats_reset FROM pg_stat_subscription_stats WHERE subname = '$sub1_name') ); -$node_publisher->wait_for_catchup('tap_sub'); - -# Wait for the tablesync error to be reported. -$node_subscriber->poll_query_until( - 'postgres', - qq[ -SELECT sync_error_count > 0 -FROM pg_stat_subscription_stats -WHERE subname = 'tap_sub' -]) or die "Timed out while waiting for tablesync error"; - -# Truncate test_tab1 so that tablesync worker can continue. -$node_subscriber->safe_psql('postgres', "TRUNCATE test_tab1;"); - -# Wait for initial tablesync for test_tab1 to finish. -$node_subscriber->poll_query_until( - 'postgres', - qq[ -SELECT count(1) = 1 FROM pg_subscription_rel -WHERE srrelid = 'test_tab1'::regclass AND srsubstate in ('r', 's') -]) or die "Timed out while waiting for subscriber to synchronize data"; - -# Check test_tab1 on the subscriber has one row. -$result = $node_subscriber->safe_psql('postgres', "SELECT a FROM test_tab1"); -is($result, qq(1), 'check the table has now row'); - -# Insert data to test_tab1 on the publisher, raising an error on the subscriber -# due to violation of the unique constraint on test_tab1. -$node_publisher->safe_psql('postgres', "INSERT INTO test_tab1 VALUES (1)"); - -# Wait for the apply error to be reported. -$node_subscriber->poll_query_until( - 'postgres', - qq[ -SELECT apply_error_count > 0 -FROM pg_stat_subscription_stats -WHERE subname = 'tap_sub' -]) or die "Timed out while waiting for apply error"; - -# Truncate test_tab1 so that apply worker can continue. -$node_subscriber->safe_psql('postgres', "TRUNCATE test_tab1;"); +# Reset single sub again +$node_subscriber->safe_psql( + $db, + qq(SELECT pg_stat_reset_subscription_stats((SELECT subid FROM + pg_stat_subscription_stats WHERE subname = '$sub1_name'))) +); + +# check reset timestamp is newer after reset +is( $node_subscriber->safe_psql( + $db, + qq(SELECT stats_reset > '$reset_time1'::timestamptz FROM + pg_stat_subscription_stats WHERE subname = '$sub1_name') + ), + qq(t), + qq(Check reset timestamp for '$sub1_name' is newer after second reset.)); + +# Make second subscription and publication +my $table2_name = 'test_tab2'; +my ($pub2_name, $sub2_name) = + create_sub_pub_w_errors($node_publisher, $node_subscriber, $db, + $table2_name); + +# Apply and Sync errors are > 0 and reset timestamp is NULL +is( $node_subscriber->safe_psql( + $db, + qq(SELECT apply_error_count > 0, + sync_error_count > 0, + stats_reset IS NULL + FROM pg_stat_subscription_stats + WHERE subname = '$sub2_name') + ), + qq(t|t|t), + qq(Confirm that apply errors and sync errors are both > 0 and stats_reset is NULL for sub '$sub2_name'.) +); + +# Reset all subscriptions +$node_subscriber->safe_psql($db, + qq(SELECT pg_stat_reset_subscription_stats(NULL))); + +# Apply and Sync errors are 0 and stats reset is not NULL +is( $node_subscriber->safe_psql( + $db, + qq(SELECT apply_error_count = 0, + sync_error_count = 0, + stats_reset IS NOT NULL + FROM pg_stat_subscription_stats + WHERE subname = '$sub1_name') + ), + qq(t|t|t), + qq(Confirm that apply errors and sync errors are both 0 and stats_reset is not NULL for sub '$sub1_name' after reset.) +); + +is( $node_subscriber->safe_psql( + $db, + qq(SELECT apply_error_count = 0, + sync_error_count = 0, + stats_reset IS NOT NULL + FROM pg_stat_subscription_stats + WHERE subname = '$sub2_name') + ), + qq(t|t|t), + qq(Confirm that apply errors and sync errors are both 0 and stats_reset is not NULL for sub '$sub2_name' after reset.) +); + +$reset_time1 = $node_subscriber->safe_psql($db, + qq(SELECT stats_reset FROM pg_stat_subscription_stats WHERE subname = '$sub1_name') +); +my $reset_time2 = $node_subscriber->safe_psql($db, + qq(SELECT stats_reset FROM pg_stat_subscription_stats WHERE subname = '$sub2_name') +); + +# Reset all subscriptions +$node_subscriber->safe_psql($db, + qq(SELECT pg_stat_reset_subscription_stats(NULL))); + +# check reset timestamp for sub1 is newer after reset +is( $node_subscriber->safe_psql( + $db, + qq(SELECT stats_reset > '$reset_time1'::timestamptz FROM + pg_stat_subscription_stats WHERE subname = '$sub1_name') + ), + qq(t), + qq(Confirm that reset timestamp for '$sub1_name' is newer after second reset.) +); + +# check reset timestamp for sub2 is newer after reset +is( $node_subscriber->safe_psql( + $db, + qq(SELECT stats_reset > '$reset_time2'::timestamptz FROM + pg_stat_subscription_stats WHERE subname = '$sub2_name') + ), + qq(t), + qq(Confirm that reset timestamp for '$sub2_name' is newer after second reset.) +); + +# Get subscription 1 oid +my $sub1_oid = $node_subscriber->safe_psql($db, + qq(SELECT oid FROM pg_subscription WHERE subname = '$sub1_name')); + +# Drop subscription 1 +$node_subscriber->safe_psql($db, qq(DROP SUBSCRIPTION $sub1_name)); + +# Subscription stats for sub1 should be gone +is( $node_subscriber->safe_psql( + $db, qq(SELECT pg_stat_have_stats('subscription', 0, $sub1_oid))), + qq(f), + qq(Subscription stats for subscription '$sub1_name' should be removed.)); + $node_subscriber->stop('fast'); $node_publisher->stop('fast'); -- 2.39.5