Debian Codegraph @

src/test/recovery/t/030_stats_cleanup_replica.pl

raw
1 # Copyright (c) 2021-2025, PostgreSQL Global Development Group
2
3 # Tests that standbys:
4 # - drop stats for objects when the those records are replayed
5 # - persist stats across graceful restarts
6 # - discard stats after immediate / crash restarts
7
8 use strict;
9 use warnings FATAL => 'all';
10 use PostgreSQL::Test::Cluster;
11 use PostgreSQL::Test::Utils;
12 use Test::More;
13
14 my $node_primary = PostgreSQL::Test::Cluster->new('primary');
15 $node_primary->init(allows_streaming => 1);
16 $node_primary->append_conf('postgresql.conf', "track_functions = 'all'");
17 $node_primary->start;
18
19 my $backup_name = 'my_backup';
20 $node_primary->backup($backup_name);
21
22 my $node_standby = PostgreSQL::Test::Cluster->new('standby');
23 $node_standby->init_from_backup($node_primary, $backup_name,
24 has_streaming => 1);
25 $node_standby->start;
26
27
28 ## Test that stats are cleaned up on standby after dropping table or function
29
30 my $sect = 'initial';
31
32 my ($dboid, $tableoid, $funcoid) =
33 populate_standby_stats('postgres', 'public');
34 test_standby_func_tab_stats_status('postgres',
35 $dboid, $tableoid, $funcoid, 't');
36
37 drop_table_by_oid('postgres', $tableoid);
38 drop_function_by_oid('postgres', $funcoid);
39
40 $sect = 'post drop';
41 $node_primary->wait_for_replay_catchup($node_standby);
42 test_standby_func_tab_stats_status('postgres',
43 $dboid, $tableoid, $funcoid, 'f');
44
45
46 ## Test that stats are cleaned up on standby after dropping indirectly
47
48 $sect = "schema creation";
49
50 $node_primary->safe_psql('postgres', "CREATE SCHEMA drop_schema_test1");
51 $node_primary->wait_for_replay_catchup($node_standby);
52
53 ($dboid, $tableoid, $funcoid) =
54 populate_standby_stats('postgres', 'drop_schema_test1');
55
56 test_standby_func_tab_stats_status('postgres',
57 $dboid, $tableoid, $funcoid, 't');
58 $node_primary->safe_psql('postgres', "DROP SCHEMA drop_schema_test1 CASCADE");
59
60 $sect = "post schema drop";
61
62 $node_primary->wait_for_replay_catchup($node_standby);
63
64 # verify table and function stats removed from standby
65 test_standby_func_tab_stats_status('postgres',
66 $dboid, $tableoid, $funcoid, 'f');
67
68
69 ## Test that stats are cleaned up on standby after dropping database
70
71 $sect = "createdb";
72
73 $node_primary->safe_psql('postgres', "CREATE DATABASE test");
74 $node_primary->wait_for_replay_catchup($node_standby);
75
76 ($dboid, $tableoid, $funcoid) = populate_standby_stats('test', 'public');
77
78 # verify stats are present
79 test_standby_func_tab_stats_status('test', $dboid, $tableoid, $funcoid, 't');
80 test_standby_db_stats_status('test', $dboid, 't');
81
82 $node_primary->safe_psql('postgres', "DROP DATABASE test");
83 $sect = "post dropdb";
84 $node_primary->wait_for_replay_catchup($node_standby);
85
86 # Test that the stats were cleaned up on standby
87 # Note that this connects to 'postgres' but provides the dboid of dropped db
88 # 'test' which we acquired previously
89 test_standby_func_tab_stats_status('postgres',
90 $dboid, $tableoid, $funcoid, 'f');
91
92 test_standby_db_stats_status('postgres', $dboid, 'f');
93
94
95 ## verify that stats persist across graceful restarts on a replica
96
97 # NB: Can't test database stats, they're immediately repopulated when
98 # reconnecting...
99 $sect = "pre restart";
100 ($dboid, $tableoid, $funcoid) = populate_standby_stats('postgres', 'public');
101 test_standby_func_tab_stats_status('postgres',
102 $dboid, $tableoid, $funcoid, 't');
103
104 $node_standby->restart();
105
106 $sect = "post non-immediate";
107
108 test_standby_func_tab_stats_status('postgres',
109 $dboid, $tableoid, $funcoid, 't');
110
111 # but gone after an immediate restart
112 $node_standby->stop('immediate');
113 $node_standby->start();
114
115 $sect = "post immediate restart";
116
117 test_standby_func_tab_stats_status('postgres',
118 $dboid, $tableoid, $funcoid, 'f');
119
120
121 done_testing();
122
123
124 sub populate_standby_stats
125 {
126 my ($connect_db, $schema) = @_;
127
128 # create objects on primary
129 $node_primary->safe_psql($connect_db,
130 "CREATE TABLE $schema.drop_tab_test1 AS SELECT generate_series(1,100) AS a"
131 );
132 $node_primary->safe_psql($connect_db,
133 "CREATE FUNCTION $schema.drop_func_test1() RETURNS VOID AS 'select 2;' LANGUAGE SQL IMMUTABLE"
134 );
135 $node_primary->wait_for_replay_catchup($node_standby);
136
137 # collect object oids
138 my $dboid = $node_standby->safe_psql($connect_db,
139 "SELECT oid FROM pg_database WHERE datname = '$connect_db'");
140 my $tableoid = $node_standby->safe_psql($connect_db,
141 "SELECT '$schema.drop_tab_test1'::regclass::oid");
142 my $funcoid = $node_standby->safe_psql($connect_db,
143 "SELECT '$schema.drop_func_test1()'::regprocedure::oid");
144
145 # generate stats on standby
146 $node_standby->safe_psql($connect_db,
147 "SELECT * FROM $schema.drop_tab_test1");
148 $node_standby->safe_psql($connect_db, "SELECT $schema.drop_func_test1()");
149
150 return ($dboid, $tableoid, $funcoid);
151 }
152
153 sub drop_function_by_oid
154 {
155 my ($connect_db, $funcoid) = @_;
156
157 # Get function name from returned oid
158 my $func_name = $node_primary->safe_psql($connect_db,
159 "SELECT '$funcoid'::regprocedure");
160 $node_primary->safe_psql($connect_db, "DROP FUNCTION $func_name");
161 }
162
163 sub drop_table_by_oid
164 {
165 my ($connect_db, $tableoid) = @_;
166
167 # Get table name from returned oid
168 my $table_name =
169 $node_primary->safe_psql($connect_db, "SELECT '$tableoid'::regclass");
170 $node_primary->safe_psql($connect_db, "DROP TABLE $table_name");
171 }
172
173 sub test_standby_func_tab_stats_status
174 {
175 local $Test::Builder::Level = $Test::Builder::Level + 1;
176 my ($connect_db, $dboid, $tableoid, $funcoid, $present) = @_;
177
178 my %expected = (rel => $present, func => $present);
179 my %stats;
180
181 $stats{rel} = $node_standby->safe_psql($connect_db,
182 "SELECT pg_stat_have_stats('relation', $dboid, $tableoid)");
183 $stats{func} = $node_standby->safe_psql($connect_db,
184 "SELECT pg_stat_have_stats('function', $dboid, $funcoid)");
185
186 is_deeply(\%stats, \%expected, "$sect: standby stats as expected");
187
188 return;
189 }
190
191 sub test_standby_db_stats_status
192 {
193 local $Test::Builder::Level = $Test::Builder::Level + 1;
194 my ($connect_db, $dboid, $present) = @_;
195
196 is( $node_standby->safe_psql(
197 $connect_db, "SELECT pg_stat_have_stats('database', $dboid, 0)"),
198 $present,
199 "$sect: standby db stats as expected");
200 }