Debian Codegraph @

src/test/recovery/t/021_row_visibility.pl

raw
1
2 # Copyright (c) 2021-2025, PostgreSQL Global Development Group
3
4 # Checks that snapshots on standbys behave in a minimally reasonable
5 # way.
6 use strict;
7 use warnings FATAL => 'all';
8
9 use PostgreSQL::Test::Cluster;
10 use PostgreSQL::Test::Utils;
11 use Test::More;
12
13 # Initialize primary node
14 my $node_primary = PostgreSQL::Test::Cluster->new('primary');
15 $node_primary->init(allows_streaming => 1);
16 $node_primary->append_conf('postgresql.conf', 'max_prepared_transactions=10');
17 $node_primary->start;
18
19 # Initialize with empty test table
20 $node_primary->safe_psql('postgres',
21 'CREATE TABLE public.test_visibility (data text not null)');
22
23 # Take backup
24 my $backup_name = 'my_backup';
25 $node_primary->backup($backup_name);
26
27 # Create streaming standby from backup
28 my $node_standby = PostgreSQL::Test::Cluster->new('standby');
29 $node_standby->init_from_backup($node_primary, $backup_name,
30 has_streaming => 1);
31 $node_standby->append_conf('postgresql.conf', 'max_prepared_transactions=10');
32 $node_standby->start;
33
34 my $psql_timeout =
35 IPC::Run::timer(2 * $PostgreSQL::Test::Utils::timeout_default);
36
37 # One psql to primary and standby each, for all queries. That allows
38 # to check uncommitted changes being replicated and such.
39 my %psql_primary = (stdin => '', stdout => '', stderr => '');
40 $psql_primary{run} = IPC::Run::start(
41 [
42 'psql', '--no-psqlrc', '--no-align',
43 '--file' => '-',
44 '--dbname' => $node_primary->connstr('postgres'),
45 ],
46 '<' => \$psql_primary{stdin},
47 '>' => \$psql_primary{stdout},
48 '2>' => \$psql_primary{stderr},
49 $psql_timeout);
50
51 my %psql_standby = ('stdin' => '', 'stdout' => '', 'stderr' => '');
52 $psql_standby{run} = IPC::Run::start(
53 [
54 'psql', '--no-psqlrc', '--no-align',
55 '--file' => '-',
56 '--dbname' => $node_standby->connstr('postgres'),
57 ],
58 '<' => \$psql_standby{stdin},
59 '>' => \$psql_standby{stdout},
60 '2>' => \$psql_standby{stderr},
61 $psql_timeout);
62
63 #
64 # 1. Check initial data is the same
65 #
66 ok( send_query_and_wait(
67 \%psql_standby,
68 q/SELECT * FROM test_visibility ORDER BY data;/,
69 qr/^\(0 rows\)$/m),
70 'data not visible');
71
72 #
73 # 2. Check if an INSERT is replayed and visible
74 #
75 $node_primary->psql('postgres',
76 "INSERT INTO test_visibility VALUES ('first insert')");
77 $node_primary->wait_for_catchup($node_standby);
78
79 ok( send_query_and_wait(
80 \%psql_standby,
81 q[SELECT * FROM test_visibility ORDER BY data;],
82 qr/first insert.*\n\(1 row\)/m),
83 'insert visible');
84
85 #
86 # 3. Verify that uncommitted changes aren't visible.
87 #
88 ok( send_query_and_wait(
89 \%psql_primary,
90 q[
91 BEGIN;
92 UPDATE test_visibility SET data = 'first update' RETURNING data;
93 ],
94 qr/^UPDATE 1$/m),
95 'UPDATE');
96
97 $node_primary->psql('postgres', "SELECT txid_current();"); # ensure WAL flush
98 $node_primary->wait_for_catchup($node_standby);
99
100 ok( send_query_and_wait(
101 \%psql_standby,
102 q[SELECT * FROM test_visibility ORDER BY data;],
103 qr/first insert.*\n\(1 row\)/m),
104 'uncommitted update invisible');
105
106 #
107 # 4. That a commit turns 3. visible
108 #
109 ok(send_query_and_wait(\%psql_primary, q[COMMIT;], qr/^COMMIT$/m), 'COMMIT');
110
111 $node_primary->wait_for_catchup($node_standby);
112
113 ok( send_query_and_wait(
114 \%psql_standby,
115 q[SELECT * FROM test_visibility ORDER BY data;],
116 qr/first update\n\(1 row\)$/m),
117 'committed update visible');
118
119 #
120 # 5. Check that changes in prepared xacts is invisible
121 #
122 ok( send_query_and_wait(
123 \%psql_primary, q[
124 DELETE from test_visibility; -- delete old data, so we start with clean slate
125 BEGIN;
126 INSERT INTO test_visibility VALUES('inserted in prepared will_commit');
127 PREPARE TRANSACTION 'will_commit';],
128 qr/^PREPARE TRANSACTION$/m),
129 'prepared will_commit');
130
131 ok( send_query_and_wait(
132 \%psql_primary, q[
133 BEGIN;
134 INSERT INTO test_visibility VALUES('inserted in prepared will_abort');
135 PREPARE TRANSACTION 'will_abort';
136 ],
137 qr/^PREPARE TRANSACTION$/m),
138 'prepared will_abort');
139
140 $node_primary->wait_for_catchup($node_standby);
141
142 ok( send_query_and_wait(
143 \%psql_standby,
144 q[SELECT * FROM test_visibility ORDER BY data;],
145 qr/^\(0 rows\)$/m),
146 'uncommitted prepared invisible');
147
148 # For some variation, finish prepared xacts via separate connections
149 $node_primary->safe_psql('postgres', "COMMIT PREPARED 'will_commit';");
150 $node_primary->safe_psql('postgres', "ROLLBACK PREPARED 'will_abort';");
151 $node_primary->wait_for_catchup($node_standby);
152
153 ok( send_query_and_wait(
154 \%psql_standby,
155 q[SELECT * FROM test_visibility ORDER BY data;],
156 qr/will_commit.*\n\(1 row\)$/m),
157 'finished prepared visible');
158
159 # explicitly shut down psql instances gracefully - to avoid hangs
160 # or worse on windows
161 $psql_primary{stdin} .= "\\q\n";
162 $psql_primary{run}->finish;
163 $psql_standby{stdin} .= "\\q\n";
164 $psql_standby{run}->finish;
165
166 $node_primary->stop;
167 $node_standby->stop;
168
169 # Send query, wait until string matches
170 sub send_query_and_wait
171 {
172 my ($psql, $query, $untl) = @_;
173
174 # send query
175 $$psql{stdin} .= $query;
176 $$psql{stdin} .= "\n";
177
178 # wait for query results
179 $$psql{run}->pump_nb();
180 while (1)
181 {
182 last if $$psql{stdout} =~ /$untl/;
183
184 if ($psql_timeout->is_expired)
185 {
186 BAIL_OUT("aborting wait: program timed out\n"
187 . "stream contents: >>$$psql{stdout}<<\n"
188 . "pattern searched for: $untl\n");
189 return 0;
190 }
191 if (not $$psql{run}->pumpable())
192 {
193 BAIL_OUT("aborting wait: program died\n"
194 . "stream contents: >>$$psql{stdout}<<\n"
195 . "pattern searched for: $untl\n");
196 return 0;
197 }
198 $$psql{run}->pump();
199 }
200
201 $$psql{stdout} = '';
202
203 return 1;
204 }
205
206 done_testing();