Skip to content

Commit 7b28913

Browse files
anarazelanarazel
committed
Fix and test snapshot behavior on standby.
I (Andres) broke this in 623a9CA79bx, because I didn't think about the way snapshots are built on standbys sufficiently. Unfortunately our existing tests did not catch this, as they are all just querying with psql (therefore ending up with fresh snapshots). The fix is trivial, we just need to increment the transaction completion counter in ExpireTreeKnownAssignedTransactionIds(), which is the equivalent of ProcArrayEndTransaction() during recovery. This commit also adds a new test doing some basic testing of the correctness of snapshots built on standbys. To avoid the aforementioned issue of one-shot psql's not exercising the snapshot caching, the test uses a long lived psqls, similar to 013_crash_restart.pl. It'd be good to extend the test further. Reported-By: Ian Barwick <ian.barwick@2ndquadrant.com> Author: Andres Freund <andres@anarazel.de> Author: Ian Barwick <ian.barwick@2ndquadrant.com> Discussion: https://postgr.es/m/61291ffe-d611-f889-68b5-c298da9fb18f@2ndquadrant.com
1 parent 9fc2122 commit 7b28913

File tree

2 files changed

+195
-0
lines changed

2 files changed

+195
-0
lines changed

‎src/backend/storage/ipc/procarray.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4280,6 +4280,9 @@ ExpireTreeKnownAssignedTransactionIds(TransactionId xid, int nsubxids,
42804280
/* As in ProcArrayEndTransaction, advance latestCompletedXid */
42814281
MaintainLatestCompletedXidRecovery(max_xid);
42824282

4283+
/* ... and xactCompletionCount */
4284+
ShmemVariableCache->xactCompletionCount++;
4285+
42834286
LWLockRelease(ProcArrayLock);
42844287
}
42854288

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

Lines changed: 192 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,192 @@
1+
# Checks that snapshots on standbys behave in a minimally reasonable
2+
# way.
3+
use strict;
4+
use warnings;
5+
6+
use PostgresNode;
7+
use TestLib;
8+
use Test::More tests => 10;
9+
10+
# Initialize primary node
11+
my $node_primary = get_new_node('primary');
12+
$node_primary->init(allows_streaming => 1);
13+
$node_primary->append_conf('postgresql.conf', 'max_prepared_transactions=10');
14+
$node_primary->start;
15+
16+
# Initialize with empty test table
17+
$node_primary->safe_psql('postgres',
18+
'CREATE TABLE public.test_visibility (data text not null)');
19+
20+
# Take backup
21+
my $backup_name = 'my_backup';
22+
$node_primary->backup($backup_name);
23+
24+
# Create streaming standby from backup
25+
my $node_standby = get_new_node('standby');
26+
$node_standby->init_from_backup($node_primary, $backup_name,
27+
has_streaming => 1);
28+
$node_standby->append_conf('postgresql.conf', 'max_prepared_transactions=10');
29+
$node_standby->start;
30+
31+
# To avoid hanging while expecting some specific input from a psql
32+
# instance being driven by us, add a timeout high enough that it
33+
# should never trigger even on very slow machines, unless something
34+
# is really wrong.
35+
my $psql_timeout = IPC::Run::timer(30);
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} =
41+
IPC::Run::start(
42+
['psql', '-XA', '-f', '-', '-d', $node_primary->connstr('postgres')],
43+
'<', \$psql_primary{stdin},
44+
'>', \$psql_primary{stdout},
45+
'2>', \$psql_primary{stderr},
46+
$psql_timeout);
47+
48+
my %psql_standby = ('stdin' => '', 'stdout' => '', 'stderr' => '');
49+
$psql_standby{run} =
50+
IPC::Run::start(
51+
['psql', '-XA', '-f', '-', '-d', $node_standby->connstr('postgres')],
52+
'<', \$psql_standby{stdin},
53+
'>', \$psql_standby{stdout},
54+
'2>', \$psql_standby{stderr},
55+
$psql_timeout);
56+
57+
#
58+
# 1. Check initial data is the same
59+
#
60+
ok(send_query_and_wait(\%psql_standby,
61+
q/SELECT * FROM test_visibility ORDER BY data;/,
62+
qr/^\(0 rows\)$/m),
63+
'data not visible');
64+
65+
#
66+
# 2. Check if an INSERT is replayed and visible
67+
#
68+
$node_primary->psql('postgres', "INSERT INTO test_visibility VALUES ('first insert')");
69+
$node_primary->wait_for_catchup($node_standby, 'replay',
70+
$node_primary->lsn('insert'));
71+
72+
ok(send_query_and_wait(\%psql_standby,
73+
q[SELECT * FROM test_visibility ORDER BY data;],
74+
qr/first insert.*\n\(1 row\)/m),
75+
'insert visible');
76+
77+
#
78+
# 3. Verify that uncommitted changes aren't visible.
79+
#
80+
ok(send_query_and_wait(\%psql_primary,
81+
q[
82+
BEGIN;
83+
UPDATE test_visibility SET data = 'first update' RETURNING data;
84+
],
85+
qr/^UPDATE 1$/m),
86+
'UPDATE');
87+
88+
$node_primary->psql('postgres', "SELECT txid_current();"); # ensure WAL flush
89+
$node_primary->wait_for_catchup($node_standby, 'replay',
90+
$node_primary->lsn('insert'));
91+
92+
ok(send_query_and_wait(\%psql_standby,
93+
q[SELECT * FROM test_visibility ORDER BY data;],
94+
qr/first insert.*\n\(1 row\)/m),
95+
'uncommitted update invisible');
96+
97+
#
98+
# 4. That a commit turns 3. visible
99+
#
100+
ok(send_query_and_wait(\%psql_primary,
101+
q[COMMIT;],
102+
qr/^COMMIT$/m),
103+
'COMMIT');
104+
105+
$node_primary->wait_for_catchup($node_standby, 'replay',
106+
$node_primary->lsn('insert'));
107+
108+
ok(send_query_and_wait(\%psql_standby,
109+
q[SELECT * FROM test_visibility ORDER BY data;],
110+
qr/first update\n\(1 row\)$/m),
111+
'committed update visible');
112+
113+
#
114+
# 5. Check that changes in prepared xacts is invisible
115+
#
116+
ok(send_query_and_wait(\%psql_primary, q[
117+
DELETE from test_visibility; -- delete old data, so we start with clean slate
118+
BEGIN;
119+
INSERT INTO test_visibility VALUES('inserted in prepared will_commit');
120+
PREPARE TRANSACTION 'will_commit';],
121+
qr/^PREPARE TRANSACTION$/m),
122+
'prepared will_commit');
123+
124+
ok(send_query_and_wait(\%psql_primary, q[
125+
BEGIN;
126+
INSERT INTO test_visibility VALUES('inserted in prepared will_abort');
127+
PREPARE TRANSACTION 'will_abort';
128+
],
129+
qr/^PREPARE TRANSACTION$/m),
130+
'prepared will_abort');
131+
132+
$node_primary->wait_for_catchup($node_standby, 'replay',
133+
$node_primary->lsn('insert'));
134+
135+
ok(send_query_and_wait(\%psql_standby,
136+
q[SELECT * FROM test_visibility ORDER BY data;],
137+
qr/^\(0 rows\)$/m),
138+
'uncommitted prepared invisible');
139+
140+
# For some variation, finish prepared xacts via separate connections
141+
$node_primary->safe_psql('postgres',
142+
"COMMIT PREPARED 'will_commit';");
143+
$node_primary->safe_psql('postgres',
144+
"ROLLBACK PREPARED 'will_abort';");
145+
$node_primary->wait_for_catchup($node_standby, 'replay',
146+
$node_primary->lsn('insert'));
147+
148+
ok(send_query_and_wait(\%psql_standby,
149+
q[SELECT * FROM test_visibility ORDER BY data;],
150+
qr/will_commit.*\n\(1 row\)$/m),
151+
'finished prepared visible');
152+
153+
$node_primary->stop;
154+
$node_standby->stop;
155+
156+
# Send query, wait until string matches
157+
sub send_query_and_wait
158+
{
159+
my ($psql, $query, $untl) = @_;
160+
my $ret;
161+
162+
# send query
163+
$$psql{stdin} .= $query;
164+
$$psql{stdin} .= "\n";
165+
166+
# wait for query results
167+
$$psql{run}->pump_nb();
168+
while (1)
169+
{
170+
last if $$psql{stdout} =~ /$untl/;
171+
172+
if ($psql_timeout->is_expired)
173+
{
174+
BAIL_OUT("aborting wait: program timed out\n".
175+
"stream contents: >>$$psql{stdout}<<\n".
176+
"pattern searched for: $untl\n");
177+
return 0;
178+
}
179+
if (not $$psql{run}->pumpable())
180+
{
181+
BAIL_OUT("aborting wait: program died\n".
182+
"stream contents: >>$$psql{stdout}<<\n".
183+
"pattern searched for: $untl\n");
184+
return 0;
185+
}
186+
$$psql{run}->pump();
187+
}
188+
189+
$$psql{stdout} = '';
190+
191+
return 1;
192+
}

0 commit comments

Comments
 (0)