On Tue, Mar 30, 2021 at 11:00 AM Andres Freund <and...@anarazel.de> wrote:
>
> Hi,
>
> On 2021-03-30 10:13:29 +0530, vignesh C wrote:
> > On Tue, Mar 30, 2021 at 6:28 AM Andres Freund <and...@anarazel.de> wrote:
> > > Any chance you could write a tap test exercising a few of these cases?
> >
> > I can try to write a patch for this if nobody objects.
>
> Cool!
>
Attached a patch which has the test for the first scenario.
> > > E.g. things like:
> > >
> > > - create a few slots, drop one of them, shut down, start up, verify
> > > stats are still sane
> > > - create a few slots, shut down, manually remove a slot, lower
> > > max_replication_slots, start up
> >
> > Here by "manually remove a slot", do you mean to remove the slot
> > manually from the pg_replslot folder?
>
> Yep - thereby allowing max_replication_slots after the shutdown/start to
> be lower than the number of slots-stats objects.
I have not included the 2nd test in the patch as the test fails with
following warnings and also displays the statistics of the removed
slot:
WARNING: problem in alloc set Statistics snapshot: detected write
past chunk end in block 0x55d038b8e410, chunk 0x55d038b8e438
WARNING: problem in alloc set Statistics snapshot: detected write
past chunk end in block 0x55d038b8e410, chunk 0x55d038b8e438
This happens because the statistics file has an additional slot
present even though the replication slot was removed. I felt this
issue should be fixed. I will try to fix this issue and send the
second test along with the fix.
Regards,
Vignesh
From c3aded40e0516dbd42d52c72c5b95ec45f991923 Mon Sep 17 00:00:00 2001
From: vignesh <vignes...@gmail.com>
Date: Wed, 31 Mar 2021 11:02:40 +0530
Subject: [PATCH v1] Added tests for verification of logical replication
statistics.
Added tests for verification of logical replication statistics after
restart of server.
---
src/test/subscription/t/022_repl_stats.pl | 161 ++++++++++++++++++++++
1 file changed, 161 insertions(+)
create mode 100644 src/test/subscription/t/022_repl_stats.pl
diff --git a/src/test/subscription/t/022_repl_stats.pl b/src/test/subscription/t/022_repl_stats.pl
new file mode 100644
index 0000000000..7755a447c0
--- /dev/null
+++ b/src/test/subscription/t/022_repl_stats.pl
@@ -0,0 +1,161 @@
+# Test replication statistics data gets updated in pg_stat_replication_slots
+# view for logical replication.
+use strict;
+use warnings;
+use PostgresNode;
+use TestLib;
+use Test::More tests => 5;
+
+# Create publisher node.
+my $node_publisher = get_new_node('publisher');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->append_conf('postgresql.conf', 'logical_decoding_work_mem = 64kB');
+$node_publisher->start;
+
+# Create subscriber nodes.
+my $node_subscriber1 = get_new_node('subscriber1');
+$node_subscriber1->init(allows_streaming => 'logical');
+$node_subscriber1->start;
+
+my $node_subscriber2 = get_new_node('subscriber2');
+$node_subscriber2->init(allows_streaming => 'logical');
+$node_subscriber2->start;
+
+my $node_subscriber3 = get_new_node('subscriber3');
+$node_subscriber3->init(allows_streaming => 'logical');
+$node_subscriber3->start;
+
+# Create table on publisher.
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE test_tab (a int primary key, b varchar)");
+
+# Create table on subscribers.
+$node_subscriber1->safe_psql('postgres', "CREATE TABLE test_tab (a int primary key, b text, c timestamptz DEFAULT now(), d bigint DEFAULT 999)");
+$node_subscriber2->safe_psql('postgres', "CREATE TABLE test_tab (a int primary key, b text, c timestamptz DEFAULT now(), d bigint DEFAULT 999)");
+$node_subscriber3->safe_psql('postgres', "CREATE TABLE test_tab (a int primary key, b text, c timestamptz DEFAULT now(), d bigint DEFAULT 999)");
+
+# Setup logical replication.
+my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
+$node_publisher->safe_psql('postgres', "CREATE PUBLICATION tap_pub FOR TABLE test_tab");
+
+my $appname1 = 'tap_sub1';
+$node_subscriber1->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub1
+ CONNECTION '$publisher_connstr
+ application_name=$appname1'
+ PUBLICATION tap_pub WITH (streaming = on)"
+);
+
+my $appname2 = 'tap_sub2';
+$node_subscriber2->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub2
+ CONNECTION '$publisher_connstr
+ application_name=$appname2'
+ PUBLICATION tap_pub WITH (streaming = off)"
+);
+
+my $appname3 = 'tap_sub3';
+$node_subscriber3->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub3
+ CONNECTION '$publisher_connstr
+ application_name=$appname3'
+ PUBLICATION tap_pub WITH (streaming = off)"
+);
+
+$node_publisher->wait_for_catchup($appname1);
+$node_publisher->wait_for_catchup($appname2);
+$node_publisher->wait_for_catchup($appname3);
+
+# Interleave a pair of transactions, each exceeding the 64kB limit.
+my $in = '';
+my $out = '';
+
+my $timer = IPC::Run::timeout(180);
+
+my $h = $node_publisher->background_psql('postgres', \$in, \$out, $timer,
+ on_error_stop => 0);
+
+$in .= q{
+BEGIN;
+INSERT INTO test_tab SELECT i, md5(i::text) FROM generate_series(3, 5000) s(i);
+UPDATE test_tab SET b = md5(b) WHERE mod(a,2) = 0;
+DELETE FROM test_tab WHERE mod(a,3) = 0;
+};
+$h->pump_nb;
+
+$node_publisher->safe_psql(
+ 'postgres', q{
+BEGIN;
+INSERT INTO test_tab SELECT i, md5(i::text) FROM generate_series(5001, 9999) s(i);
+DELETE FROM test_tab WHERE a > 5000;
+COMMIT;
+});
+
+$in .= q{
+COMMIT;
+\q
+};
+$h->finish; # errors make the next test fail, so ignore them here
+
+$node_publisher->wait_for_catchup($appname1);
+$node_publisher->wait_for_catchup($appname2);
+$node_publisher->wait_for_catchup($appname3);
+
+# Verify data is replicated to the subscribers.
+my $result =
+ $node_subscriber1->safe_psql('postgres', "SELECT count(*), count(c), count(d = 999) FROM test_tab");
+is($result, qq(3332|3332|3332), 'check publisher data is replicated to the subscriber');
+
+$result =
+ $node_subscriber2->safe_psql('postgres', "SELECT count(*), count(c), count(d = 999) FROM test_tab");
+is($result, qq(3332|3332|3332), 'check publisher data is replicated to the subscriber');
+
+$result =
+ $node_subscriber3->safe_psql('postgres', "SELECT count(*), count(c), count(d = 999) FROM test_tab");
+is($result, qq(3332|3332|3332), 'check publisher data is replicated to the subscriber');
+
+# Test to verify replication statistics data is updated in
+# pg_stat_replication_slots statistics view.
+$result = $node_publisher->safe_psql('postgres',
+ "SELECT slot_name,
+ spill_txns > 0 AS spill_txns,
+ spill_count > 0 AS spill_count,
+ spill_bytes > 0 AS spill_bytes,
+ stream_txns > 0 AS stream_txns,
+ stream_count > 0 AS stream_count,
+ stream_bytes > 0 AS stream_bytes,
+ stats_reset
+ FROM pg_stat_replication_slots ORDER BY slot_name"
+);
+is($result, qq(tap_sub1|f|f|f|t|t|t|
+tap_sub2|t|t|t|f|f|f|
+tap_sub3|t|t|t|f|f|f|), 'check replication statistics are updated');
+
+# Test to drop one of the subscribers and verify replication statistics data is
+# fine after publisher is restarted.
+$node_subscriber3->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub3;");
+
+$node_publisher->stop;
+$node_publisher->start;
+
+# Verify statistics data present in pg_stat_replication_slots are sane after
+# publisher is restarted
+$result = $node_publisher->safe_psql('postgres',
+ "SELECT slot_name,
+ spill_txns > 0 AS spill_txns,
+ spill_count > 0 AS spill_count,
+ spill_bytes > 0 AS spill_bytes,
+ stream_txns > 0 AS stream_txns,
+ stream_count > 0 AS stream_count,
+ stream_bytes > 0 AS stream_bytes,
+ stats_reset
+ FROM pg_stat_replication_slots ORDER BY slot_name"
+);
+is($result, qq(tap_sub1|f|f|f|t|t|t|
+tap_sub2|t|t|t|f|f|f|), 'check replication statistics are updated');
+
+# shutdown
+$node_subscriber1->stop;
+$node_subscriber2->stop;
+$node_subscriber3->stop;
+$node_publisher->stop;
--
2.25.1
