Horton

test_case

application/octet-stream

Download
Filename: test_case
Type: application/octet-stream
Part: 0
Message: Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions 
Test Case:
Publication Server:
create table t(a int PRIMARY KEY ,b text);
CREATE OR REPLACE FUNCTION large_val() RETURNS TEXT LANGUAGE SQL AS 'select array_agg(md5(g::text))::text from generate_series(1, 256) g';
create publication test_pub for table t with(PUBLISH='insert,delete,update,truncate');
alter table t replica identity FULL ;
insert into t values (generate_series(1,20),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*300;

Subscription server:
 create table t(a int,b text);
 create subscription test_sub CONNECTION 'host=localhost port=5432 dbname=postgres user=edb' PUBLICATION test_pub WITH ( slot_name = test_slot_sub1,streaming=on);

Test Cases:

Scenario 1:
begin;
savepoint a;
delete from t;
savepoint b;
insert into t values (generate_series(1,500),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*300;
savepoint c;
alter publication test_pub drop table t ;
 savepoint d;
 delete from t;
 savepoint e;
 insert into t values (generate_series(1,5000),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
 savepoint f;
 alter publication test_pub add table t;
 savepoint g;
rollback to savepoint b;
./pg_ctl -D pub -c -l pub_logs stop 
./pg_ctl -D pub -c -l pub_logs start

Scenario 2:
begin ;
 insert into t values (generate_series(1,5000),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
 savepoint a;
 savepoint b;

Restart the publication server, while the transaction is still in an uncommitted state.
./pg_ctl -D pub/ -c -l pub_logs stop -mi
./pg_ctl -D pub/ -c -l pub_logs start -mi

Scenario 3:
begin ;
savepoint a;
delete from t;
savepoint b;
insert into t values (generate_series(1,50000),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint c;
update t set b=large_val() where a%a=0;
savepoint d;
update t set b=large_val() where a%a=0;
savepoint e;
update t set b=large_val() where a%a=0;
savepoint f;
checkpoint ;
savepoint g;
rollback to savepoint g;
delete from t;
savepoint h;
checkpoint ;
savepoint i;
insert into t values (generate_series(1,50000),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint j;
savepoint k;
savepoint l;
alter publication test_pub drop table t ;
savepoint m;
rollback to savepoint g;
delete from t;
savepoint h;
checkpoint ;
savepoint i;
insert into t values (generate_series(1,50000),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint j;
savepoint k;
savepoint l;
alter publication test_pub drop table t ;
savepoint m;
delete from t;
savepoint n;
insert into t values (generate_series(1,5000),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint o;
alter publication test_pub add table t;
savepoint p;rollback to savepoint k;

Restart the publication server, while the transaction is still in an uncommitted state.
./pg_ctl -D pub/ -c -l pub_logs stop -mi
./pg_ctl -D pub/ -c -l pub_logs start -mi

Scenario 4:
begin ;
savepoint a;
delete from t;
savepoint b;
insert into t values (generate_series(1,5000),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;

Restart the publication server, while the insert  is still in progress.
./pg_ctl -D pub/ -c -l pub_logs stop -mi
./pg_ctl -D pub/ -c -l pub_logs start -mi

Scenario 5:
begin ;
savepoint a;
delete from t;
savepoint b;
insert into t values (generate_series(1,5000),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint c;
truncate t;
savepoint d;
insert into t values (generate_series(1,500),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint e;
delete from t;
savepoint f;
insert into t values (generate_series(1,100),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint g;
rollback to savepoint c;

Restart the publication server, while the transaction is still in an uncommitted state.
./pg_ctl -D pub/ -c -l pub_logs stop -mi
./pg_ctl -D pub/ -c -l pub_logs start -mi

Scenario 6:
begin ;
savepoint a;
delete from t;
savepoint b;
insert into t values (generate_series(1,5000),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint c;
alter table t drop column b;
savepoint d;
delete from t;
savepoint e;
insert into t values (generate_series(1,5000));
savepoint f;
rollback to savepoint e;
rollback to savepoint c;
Restart the publication server, while the transaction is still in an uncommitted state.
./pg_ctl -D pub/ -c -l pub_logs stop -mi
./pg_ctl -D pub/ -c -l pub_logs start -mi
Scenario 7:begin ;
savepoint a;
delete from t;
savepoint b;
insert into t values (generate_series(1,50000),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint c;
update t set b=large_val() where a%a=0;
savepoint d;
update t set b=large_val() where a%a=0;
savepoint e;
update t set b=large_val() where a%a=0;
savepoint f;
checkpoint;
savepoint g;
rollback to savepoint g;
delete from t;
savepoint h;
alter publication test_pub drop table t ;
savepoint m;
rollback to savepoint g;
delete from t;
savepoint h;
checkpoint ;
savepoint i;
insert into t values (generate_series(1,50000),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint j;
savepoint p;
rollback to savepoint h;
rollback to savepoint g;rollback to savepoint f;
rollback to savepoint e;
Restart the publication server, while the transaction is still in an uncommitted state.
./pg_ctl -D pub/ -c -l pub_logs stop -mi
./pg_ctl -D pub/ -c -l pub_logs start -mi
Scenario 8:begin ;
savepoint a;
delete from t;
savepoint b;
insert into t values (generate_series(1,500),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint c;
update t set b=large_val() where a%a=0;
savepoint d;
delete from t;
insert into t values (generate_series(1,500),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint e;
insert into t values (generate_series(1,500),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint f;
checkpoint;
savepoint g;
update t set b=large_val() where a%a=0;
savepoint h;
insert into t values (generate_series(1,50000),large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*30000;
savepoint i;
savepoint j;
rollback to savepoint h;
rollback to savepoint d;

Restart the publication server, while the transaction is still in an uncommitted state.
./pg_ctl -D pub/ -c -l pub_logs stop -mi
./pg_ctl -D pub/ -c -l pub_logs start -mi

Scenario 9:
Publication server data setup changes:
create table t(a int);
create publication test_pub for table t with(PUBLISH='insert,delete,truncate,update');

Subscription Server:
create table t(a int ,b text);
create subscription test_sub CONNECTION 'host=172.19.10.35 port=5432 dbname=postgres user=edb' PUBLICATION test_pub WITH ( slot_name = test_slot_sub1, streaming=on);

Publication server:
insert into t values (generate_series(1,90000));
begin ;
truncate t;
 savepoint a;
 insert into t values (generate_series(1,90000));
 savepoint b;
 xsadfg;
rollback to savepoint b;
 select count(*) from t;
 delete from t;
 rollback to savepoint b;
 alter table t add constraint t_pkey PRIMARY KEY(A);
 savepoint c;
 \d+ t
 select count(*) from t;
 delete from t;
savepoint d;
 insert into t values (generate_series(1,9)) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*2340;
 savepoint e;
 insert into t values (generate_series(1,9000)) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*2340;
rollback to savepoint e;
insert into t values (generate_series(1,9000)) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*99940;
savepoint f;
 rollback to savepoint b;
Restart the publication server, while the transaction is still in an uncommitted state.
./pg_ctl -D pub/ -c -l pub_logs stop -mi
./pg_ctl -D pub/ -c -l pub_logs start -mi

Scenario 10: 
truncate t;
begin ;
 insert into t values (generate_series(1,9), large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*20;
 savepoint a;
 insert into t values (generate_series(1,90000), large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*3789;
 rollback to savepoint a;
 insert into t values (generate_series(1,90000), large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*37999;
 rollback to savepoint a;
 insert into t values (generate_series(1,90000), large_val()) ON CONFLICT (a) DO UPDATE SET a=EXCLUDED.a*3799900;
Restart the publication server, while the transaction is still in an uncommitted state.
./pg_ctl -D pub/ -c -l pub_logs stop -mi
./pg_ctl -D pub/ -c -l pub_logs start -mi

All the above test cases are also repeated with normal shutdown mode and aborting the subscription server instead of publication server.