1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
|
-- test huge transactions
-- Set 'max_locks_per_transaction' to 10000 to run test
SELECT * FROM pglogical_regress_variables()
\gset
\c :provider_dsn
-- medium number of rows in many different tables (100k): replication with DDL outside transaction
create or replace function create_many_tables(int, int) returns void language plpgsql as $$
DECLARE
i int;
cr_command varchar;
BEGIN
FOR i IN $1 .. $2 LOOP
cr_command := 'SELECT pglogical.replicate_ddl_command(''
CREATE TABLE public.HUGE' || i || ' (
id integer primary key,
id1 integer,
data text default ''''data'''',
data1 text default ''''data1''''
);
'')';
EXECUTE cr_command;
END LOOP;
END;
$$;
-- write multile version of this statement
create or replace function add_many_tables_to_replication_set(int, int) returns void language plpgsql as $$
DECLARE
i int;
cr_command varchar;
BEGIN
FOR i IN $1 .. $2 LOOP
cr_command := 'SELECT * FROM pglogical.replication_set_add_table(
''default'', ''HUGE' || i || ''' );';
EXECUTE cr_command;
END LOOP;
END;
$$;
create or replace function insert_into_many_tables(int, int) returns void language plpgsql as $$
DECLARE
i int;
cr_command varchar;
BEGIN
FOR i IN $1 .. $2 LOOP
cr_command := 'INSERT INTO public.HUGE' || i || ' VALUES (generate_series(1, 200), generate_series(1, 200))';
EXECUTE cr_command;
END LOOP;
END;
$$;
create or replace function drop_many_tables(int, int) returns void language plpgsql as $$
DECLARE
i int;
cr_command varchar;
BEGIN
FOR i IN $1 .. $2 LOOP
cr_command := 'SELECT pglogical.replicate_ddl_command(''
DROP TABLE public.HUGE' || i ||' CASCADE;
'')';
EXECUTE cr_command;
END LOOP;
END;
$$;
SELECT * FROM create_many_tables(1,100000);
SELECT * FROM add_many_tables_to_replication_set(1,100000);
SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);
BEGIN;
SELECT * FROM insert_into_many_tables(1,100000);
COMMIT;
SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);
\c :subscriber_dsn
SELECT count(*) FROM public.HUGE2;
\dtS+ public.HUGE2;
\c :provider_dsn
\set VERBOSITY terse
SELECT * FROM drop_many_tables(1,100000);
SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);
-- medium number of rows in many different tables: replication with DDL inside transaction
BEGIN;
SELECT * FROM create_many_tables(1,100000);
SELECT * FROM add_many_tables_to_replication_set(1,100000);
SELECT * FROM insert_into_many_tables(1,100000);
COMMIT;
SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);
\c :subscriber_dsn
SELECT count(*) FROM public.HUGE2;
\dtS+ public.HUGE2;
\c :provider_dsn
\set VERBOSITY terse
SELECT * FROM drop_many_tables(1,100000);
DROP function create_many_tables(int, int);
DROP function add_many_tables_to_replication_set(int,int);
DROP function insert_into_many_tables(int, int);
DROP function drop_many_tables(int, int);
SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);
|
