Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc1ab7cd

Browse files
committed
add test for concurrent global and local transactions
1 parent5f4887e commitc1ab7cd

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::Moretests=>1;
6+
use Test::Moretests=>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
foreachmy$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+
subcount_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 ($errne'');
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 ( ($totalne$oldtotal)and ($totalne'') )
79106
{
80-
$isolation_error = 1;
107+
$isolation_errors++;
81108
$oldtotal =$total;
82109
diag("Isolation error. Total =$total");
83110
}
84-
# diag("Total =$total");
111+
if (($erreq'')and ($totalne'') ) {$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 ( ($totalne$oldtotal)and ($totalne'') )
143+
{
144+
$isolation_errors++;
145+
$oldtotal =$total;
146+
diag("Isolation error. Total =$total");
147+
}
148+
if (($erreq'')and ($totalne'') ) {$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 updAS (UPDATE accountsSET amount= amount-1WHERE id= :id RETURNING*)
5+
INSERT into global_transactionsSELECT now()FROM upd;
6+
-- separate this test with big amount of connections
7+
--select pg_sleep(0.5*random());
8+
UPDATE accountsSET amount= amount+1WHERE 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 updAS (UPDATE accountsSET amount= amount-1WHERE id= (2*:id+1) RETURNING*)
5+
INSERT into local_transactionsSELECT now()FROM upd;
6+
UPDATE accountsSET amount= amount+1WHERE 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 updAS (UPDATE accountsSET amount= amount-1WHERE id=2*:id RETURNING*)
5+
INSERT into local_transactionsSELECT now()FROM upd;
6+
UPDATE accountsSET amount= amount+1WHERE id= (2*:id+2);
7+
-- INSERT into local_transactions values(now());
8+
COMMIT;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp