Skip to content

Commit c1ab7cd

Browse files
kelvichkelvich
committed
add test for concurrent global and local transactions
1 parent 5f4887e commit c1ab7cd

File tree

5 files changed

+120
-22
lines changed

5 files changed

+120
-22
lines changed

‎contrib/postgres_fdw/t/001_bank_check.pl

Lines changed: 94 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33

44
use PostgresNode;
55
use TestLib;
6-
use Test::More tests => 1;
6+
use Test::More tests => 2;
77

88
my $master = get_new_node("master");
99
$master->init;
@@ -18,75 +18,154 @@
1818
$shard1->init;
1919
$shard1->append_conf('postgresql.conf', qq(
2020
max_prepared_transactions = 30
21-
log_checkpoints = true
22-
# shared_preload_libraries = 'pg_tsdtm'
2321
));
2422
$shard1->start;
2523

2624
my $shard2 = get_new_node("shard2");
2725
$shard2->init;
2826
$shard2->append_conf('postgresql.conf', qq(
2927
max_prepared_transactions = 30
30-
log_checkpoints = true
31-
# shared_preload_libraries = 'pg_tsdtm'
3228
));
3329
$shard2->start;
3430

31+
###############################################################################
32+
# Prepare nodes
3533
###############################################################################
3634

3735
$master->psql('postgres', "CREATE EXTENSION postgres_fdw");
3836
$master->psql('postgres', "CREATE TABLE accounts(id integer primary key, amount integer)");
37+
$master->psql('postgres', "CREATE TABLE global_transactions(tx_time timestamp)");
3938

4039
foreach my $node ($shard1, $shard2)
4140
{
4241
my $port = $node->port;
4342
my $host = $node->host;
4443

45-
# $node->psql('postgres', "CREATE EXTENSION pg_tsdtm");
4644
$node->psql('postgres', "CREATE TABLE accounts(id integer primary key, amount integer)");
4745

4846
$master->psql('postgres', "CREATE SERVER shard_$port FOREIGN DATA WRAPPER postgres_fdw options(dbname 'postgres', host '$host', port '$port')");
4947
$master->psql('postgres', "CREATE FOREIGN TABLE accounts_fdw_$port() inherits (accounts) server shard_$port options(table_name 'accounts')");
5048
$master->psql('postgres', "CREATE USER MAPPING for stas SERVER shard_$port options (user 'stas')");
51-
52-
# diag("done $host $port");
5349
}
5450

5551
$shard1->psql('postgres', "insert into accounts select 2*id-1, 0 from generate_series(1, 10010) as id;");
52+
$shard1->psql('postgres', "CREATE TABLE local_transactions(tx_time timestamp)");
53+
5654
$shard2->psql('postgres', "insert into accounts select 2*id, 0 from generate_series(1, 10010) as id;");
55+
$shard2->psql('postgres', "CREATE TABLE local_transactions(tx_time timestamp)");
56+
57+
$master->pgbench(-n, -c => 20, -t => 30, -f => "$TestLib::log_path/../../t/bank.sql", 'postgres' );
5758

5859
# diag( $master->connstr() );
5960
# sleep(3600);
6061

62+
###############################################################################
63+
# Helpers
64+
###############################################################################
65+
66+
sub count_and_delete_rows
67+
{
68+
my ($node, $table) = @_;
69+
my ($rc, $count, $err);
70+
71+
($rc, $count, $err) = $node->psql('postgres',"select count(*) from $table",
72+
on_error_die => 1);
73+
74+
die "count_rows: $err" if ($err ne '');
75+
76+
$node->psql('postgres',"delete from $table", on_error_die => 1);
77+
78+
diag($node->name, ": completed $count transactions");
79+
80+
return $count;
81+
}
82+
83+
###############################################################################
84+
# Concurrent global transactions
6185
###############################################################################
6286

6387
my ($err, $rc);
88+
my $started;
6489
my $seconds = 30;
90+
my $selects;
6591
my $total = '0';
6692
my $oldtotal = '0';
67-
my $isolation_error = 0;
93+
my $isolation_errors = 0;
6894

6995

70-
$master->pgbench(-n, -c => 20, -t => 30, -f => "$TestLib::log_path/../../t/bank.pgb", 'postgres' );
96+
my $pgb_handle;
7197

72-
my $pgb_handle = $master->pgbench_async(-n, -c => 5, -T => $seconds, -f => "$TestLib::log_path/../../t/bank.pgb", 'postgres' );
98+
$pgb_handle = $master->pgbench_async(-n, -c => 5, -T => $seconds, -f => "$TestLib::log_path/../../t/bank.sql", 'postgres' );
7399

74-
my $started = time();
100+
$started = time();
101+
$selects = 0;
75102
while (time() - $started < $seconds)
76103
{
77104
($rc, $total, $err) = $master->psql('postgres', "select sum(amount) from accounts");
78105
if ( ($total ne $oldtotal) and ($total ne '') )
79106
{
80-
$isolation_error = 1;
107+
$isolation_errors++;
81108
$oldtotal = $total;
82109
diag("Isolation error. Total = $total");
83110
}
84-
# diag("Total = $total");
111+
if (($err eq '') and ($total ne '') ) { $selects++; }
85112
}
86113

87114
$master->pgbench_await($pgb_handle);
88115

89-
is($isolation_error, 0, 'check proper isolation');
116+
# sanity check
117+
diag("completed $selects selects");
118+
die "no actual transactions happend" unless ( $selects > 0 &&
119+
count_and_delete_rows($master, 'global_transactions') > 0);
120+
121+
is($isolation_errors, 0, 'isolation between concurrent global transaction');
122+
123+
###############################################################################
124+
# Concurrent global and local transactions
125+
###############################################################################
126+
127+
my ($pgb_handle1, $pgb_handle2, $pgb_handle3);
128+
129+
# global txses
130+
$pgb_handle1 = $master->pgbench_async(-n, -c => 5, -T => $seconds, -f => "$TestLib::log_path/../../t/bank.sql", 'postgres' );
131+
132+
# concurrent local
133+
$pgb_handle2 = $shard1->pgbench_async(-n, -c => 5, -T => $seconds, -f => "$TestLib::log_path/../../t/bank1.sql", 'postgres' );
134+
$pgb_handle3 = $shard2->pgbench_async(-n, -c => 5, -T => $seconds, -f => "$TestLib::log_path/../../t/bank2.sql", 'postgres' );
135+
136+
$started = time();
137+
$selects = 0;
138+
$oldtotal = 0;
139+
while (time() - $started < $seconds)
140+
{
141+
($rc, $total, $err) = $master->psql('postgres', "select sum(amount) from accounts");
142+
if ( ($total ne $oldtotal) and ($total ne '') )
143+
{
144+
$isolation_errors++;
145+
$oldtotal = $total;
146+
diag("Isolation error. Total = $total");
147+
}
148+
if (($err eq '') and ($total ne '') ) { $selects++; }
149+
}
150+
151+
diag("selects = $selects");
152+
$master->pgbench_await($pgb_handle1);
153+
$shard1->pgbench_await($pgb_handle2);
154+
$shard2->pgbench_await($pgb_handle3);
155+
156+
diag("completed $selects selects");
157+
die "" unless ( $selects > 0 &&
158+
count_and_delete_rows($master, 'global_transactions') > 0 &&
159+
count_and_delete_rows($shard1, 'local_transactions') > 0 &&
160+
count_and_delete_rows($shard2, 'local_transactions') > 0);
161+
162+
is($isolation_errors, 0, 'isolation between concurrent global and local transactions');
163+
164+
165+
# diag( $master->connstr('postgres'), "\n" );
166+
# diag( $shard1->connstr('postgres'), "\n" );
167+
# diag( $shard2->connstr('postgres'), "\n" );
168+
# sleep(3600);
90169

91170
$master->stop;
92171
$shard1->stop;

‎contrib/postgres_fdw/t/bank.pgb

Lines changed: 0 additions & 7 deletions
This file was deleted.

‎contrib/postgres_fdw/t/bank.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
\set id random(1, 20000)
2+
3+
BEGIN;
4+
WITH upd AS (UPDATE accounts SET amount = amount - 1 WHERE id = :id RETURNING *)
5+
INSERT into global_transactions SELECT now() FROM upd;
6+
-- separate this test with big amount of connections
7+
--select pg_sleep(0.5*random());
8+
UPDATE accounts SET amount = amount + 1 WHERE id = (:id + 1);
9+
-- INSERT into global_transactions values(now());
10+
COMMIT;

‎contrib/postgres_fdw/t/bank1.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
\set id random(1, 10000)
2+
3+
BEGIN;
4+
WITH upd AS (UPDATE accounts SET amount = amount - 1 WHERE id = (2*:id + 1) RETURNING *)
5+
INSERT into local_transactions SELECT now() FROM upd;
6+
UPDATE accounts SET amount = amount + 1 WHERE id = (2*:id + 3);
7+
-- INSERT into local_transactions values(now());
8+
COMMIT;

‎contrib/postgres_fdw/t/bank2.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
\set id random(1, 10000)
2+
3+
BEGIN;
4+
WITH upd AS (UPDATE accounts SET amount = amount - 1 WHERE id = 2*:id RETURNING *)
5+
INSERT into local_transactions SELECT now() FROM upd;
6+
UPDATE accounts SET amount = amount + 1 WHERE id = (2*:id + 2);
7+
-- INSERT into local_transactions values(now());
8+
COMMIT;

0 commit comments

Comments
 (0)