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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
|
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true
-- This should be the last batch of tests since I don't feel like resetting the batch limits for any to come after them
SELECT set_config('search_path','mimeo, dblink, public',false);
SELECT plan(37);
SELECT dblink_connect('mimeo_test', 'host=localhost port=5432 dbname=mimeo_source user=mimeo_owner password=mimeo_owner');
SELECT is(dblink_get_connections() @> '{mimeo_test}', 't', 'Remote database connection established');
UPDATE refresh_config_inserter SET batch_limit = 500 WHERE dest_table = 'mimeo_source.inserter_test_source';
UPDATE refresh_config_inserter SET batch_limit = 500 WHERE dest_table = 'mimeo_source.Inserter-Test-Source';
UPDATE refresh_config_inserter SET batch_limit = 500 WHERE dest_table = 'mimeo_dest.inserter_test_dest_serial';
UPDATE refresh_config_inserter SET batch_limit = 500 WHERE dest_table = 'mimeo_dest.Inserter-Test-Source_Serial';
UPDATE refresh_config_updater SET batch_limit = 500 WHERE dest_table = 'mimeo_source.updater_test_source';
UPDATE refresh_config_updater SET batch_limit = 500 WHERE dest_table = 'mimeo_source.Updater-Test-Source';
UPDATE refresh_config_updater SET batch_limit = 500 WHERE dest_table = 'mimeo_dest.updater_test_dest_serial';
UPDATE refresh_config_updater SET batch_limit = 500 WHERE dest_table = 'mimeo_dest.Updater-Test-Source_Serial';
UPDATE refresh_config_dml SET batch_limit = 500 WHERE dest_table = 'mimeo_source.dml_test_source';
UPDATE refresh_config_dml SET batch_limit = 500 WHERE dest_table = 'mimeo_source.Dml-Test-Source';
UPDATE refresh_config_logdel SET batch_limit = 500 WHERE dest_table = 'mimeo_source.logdel_test_source';
UPDATE refresh_config_logdel SET batch_limit = 500 WHERE dest_table = 'mimeo_source.LogDel-Test-Source';
SELECT results_eq ('SELECT batch_limit FROM refresh_config_inserter WHERE dest_table = ''mimeo_source.inserter_test_source''', ARRAY[500],
'Check that batch_limit got set for time based inserter');
SELECT results_eq ('SELECT batch_limit FROM refresh_config_inserter WHERE dest_table = ''mimeo_source.Inserter-Test-Source''', ARRAY[500],
'Check that batch_limit got set for time based inserter');
SELECT results_eq ('SELECT batch_limit FROM refresh_config_inserter WHERE dest_table = ''mimeo_dest.inserter_test_dest_serial''', ARRAY[500],
'Check that batch_limit got set for serial based inserter');
SELECT results_eq ('SELECT batch_limit FROM refresh_config_inserter WHERE dest_table = ''mimeo_dest.Inserter-Test-Source_Serial''', ARRAY[500],
'Check that batch_limit got set for serial based inserter');
SELECT results_eq ('SELECT batch_limit FROM refresh_config_updater WHERE dest_table = ''mimeo_source.updater_test_source''', ARRAY[500],
'Check that batch_limit got set for updater');
SELECT results_eq ('SELECT batch_limit FROM refresh_config_updater WHERE dest_table = ''mimeo_source.Updater-Test-Source''', ARRAY[500],
'Check that batch_limit got set for updater');
SELECT results_eq ('SELECT batch_limit FROM refresh_config_updater WHERE dest_table = ''mimeo_dest.updater_test_dest_serial''', ARRAY[500],
'Check that batch_limit got set for serial based updater');
SELECT results_eq ('SELECT batch_limit FROM refresh_config_updater WHERE dest_table = ''mimeo_dest.Updater-Test-Source_Serial''', ARRAY[500],
'Check that batch_limit got set for serial based updater');
SELECT results_eq ('SELECT batch_limit FROM refresh_config_dml WHERE dest_table = ''mimeo_source.dml_test_source''', ARRAY[500],
'Check that batch_limit got set for dml');
SELECT results_eq ('SELECT batch_limit FROM refresh_config_dml WHERE dest_table = ''mimeo_source.Dml-Test-Source''', ARRAY[500],
'Check that batch_limit got set for dml');
SELECT results_eq ('SELECT batch_limit FROM refresh_config_logdel WHERE dest_table = ''mimeo_source.logdel_test_source''', ARRAY[500],
'Check that batch_limit got set for logdel');
SELECT results_eq ('SELECT batch_limit FROM refresh_config_logdel WHERE dest_table = ''mimeo_source.LogDel-Test-Source''', ARRAY[500],
'Check that batch_limit got set for logdel');
SELECT diag('Inserting more data for: mimeo_source.inserter_test_source');
SELECT dblink_exec('mimeo_test', 'INSERT INTO mimeo_source.inserter_test_source (col1, col2, col3) VALUES (generate_series(100031,110000), ''test''||generate_series(100031,110000)::text, now())');
SELECT diag('Inserting more data for: mimeo_source.Inserter-Test-Source');
SELECT dblink_exec('mimeo_test', 'INSERT INTO mimeo_source."Inserter-Test-Source" (col1, "group", "Col-3") VALUES (generate_series(100031,110000), ''test''||generate_series(100031,110000)::text, now())');
SELECT diag('Inserting more data for: mimeo_source.updater_test_source');
SELECT dblink_exec('mimeo_test', 'INSERT INTO mimeo_source.updater_test_source (col1, col2, col3) VALUES (generate_series(100031,110000), ''test''||generate_series(100031,110000)::text, now())');
SELECT diag('Inserting more data for: mimeo_source.Updater-Test-Source');
SELECT dblink_exec('mimeo_test', 'INSERT INTO mimeo_source."Updater-Test-Source" ("COL-1", "group", "Col3") VALUES (generate_series(100031,110000), ''test''||generate_series(100031,110000)::text, now())');
SELECT diag('Inserting more data for: mimeo_source.dml_test_source');
SELECT dblink_exec('mimeo_test', 'INSERT INTO mimeo_source.dml_test_source (col1, col2, col3) VALUES (generate_series(100001,110000), ''test''||generate_series(100001,110000)::text, now())');
SELECT diag('Inserting more data for: mimeo_source.Dml-Test-Source');
SELECT dblink_exec('mimeo_test', 'INSERT INTO mimeo_source."Dml-Test-Source" ("COL1", "group", "Col-3") VALUES (generate_series(100001,110000), ''test''||generate_series(100001,110000)::text, now())');
SELECT diag('Inserting more data for: mimeo_source.logdel_test_source');
SELECT dblink_exec('mimeo_test', 'INSERT INTO mimeo_source.logdel_test_source (col1, col2, col3) VALUES (generate_series(100001,110000), ''test''||generate_series(100001,110000)::text, now())');
SELECT diag('Inserting more data for: mimeo_source."LogDel-Test-Source"');
SELECT dblink_exec('mimeo_test', 'INSERT INTO mimeo_source."LogDel-Test-Source" ("COL1", "group", "Col-3") VALUES (generate_series(100001,110000), ''test''||generate_series(100001,110000)::text, now())');
SELECT diag('Sleeping for 10 seconds to ensure gap for incremental tests...');
SELECT pg_sleep(10);
SELECT diag('Running batch limit refresh for: mimeo_source.inserter_test_source');
SELECT refresh_inserter('mimeo_source.inserter_test_source');
SELECT diag('Running batch limit refresh for: mimeo_source.Inserter-Test-Source');
SELECT refresh_inserter('mimeo_source.Inserter-Test-Source');
SELECT diag('Running batch limit refresh for: mimeo_dest.inserter_test_dest_serial');
SELECT refresh_inserter('mimeo_dest.inserter_test_dest_serial');
SELECT diag('Running batch limit refresh for: mimeo_dest.Inserter-Test-Source_Serial');
SELECT refresh_inserter('mimeo_dest.Inserter-Test-Source_Serial');
SELECT diag('Running batch limit refresh for: mimeo_source.updater_test_source');
SELECT refresh_updater('mimeo_source.updater_test_source');
SELECT diag('Running batch limit refresh for: mimeo_source.Updater-Test-Source');
SELECT refresh_updater('mimeo_source.Updater-Test-Source');
SELECT diag('Running batch limit refresh for: mimeo_dest.updater_test_dest_serial');
SELECT refresh_updater('mimeo_dest.updater_test_dest_serial');
SELECT diag('Running batch limit refresh for: mimeo_dest.Updater-Test-Source_Serial');
SELECT refresh_updater('mimeo_dest.Updater-Test-Source_Serial');
SELECT diag('Running batch limit refresh for: mimeo_source.dml_test_source');
SELECT refresh_dml('mimeo_source.dml_test_source');
SELECT diag('Running batch limit refresh for: mimeo_source.Dml-Test-Source');
SELECT refresh_dml('mimeo_source.Dml-Test-Source');
SELECT diag('Running batch limit refresh for: mimeo_source.logdel_test_source');
SELECT refresh_logdel('mimeo_source.logdel_test_source');
SELECT diag('Running batch limit refresh for: mimeo_source.LogDel-Test-Source');
SELECT refresh_logdel('mimeo_source.LogDel-Test-Source');
-- #### Time based INSERTER & UPDATER should have gotten no rows due to all new column values having the same timestamp
-- Make sure to exclude the repull test rows from the destination query since they have an odd timestamp value
SELECT results_eq('SELECT col1, col2, col3 FROM mimeo_source.inserter_test_source WHERE col1 <= 100000 ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, col2, col3 FROM mimeo_source.inserter_test_source WHERE col1 <= 100000 ORDER BY col1 ASC'') t (col1 int, col2 text, col3 timestamptz)',
'Check data for: mimeo_source.inserter_test_source');
SELECT results_eq('SELECT col1, "group", "Col-3" FROM mimeo_source."Inserter-Test-Source" WHERE col1 <= 100000 ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, "group", "Col-3" FROM mimeo_source."Inserter-Test-Source" WHERE col1 <= 100000 ORDER BY col1 ASC'') t (col1 int, "group" text, "Col-3" timestamptz)',
'Check data for: mimeo_source."Inserter-Test-Source"');
SELECT results_eq('SELECT col1, col2, col3 FROM mimeo_source.updater_test_source WHERE col1 <= 100000 ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, col2, col3 FROM mimeo_source.updater_test_source WHERE col1 <= 100000 ORDER BY col1 ASC'') t (col1 int, col2 text, col3 timestamptz)',
'Check data for: mimeo_source.updater_test_source');
SELECT results_eq('SELECT "COL-1", "group", "Col3" FROM mimeo_source."Updater-Test-Source" WHERE "COL-1" <= 100000 ORDER BY "COL-1" ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT "COL-1", "group", "Col3" FROM mimeo_source."Updater-Test-Source" WHERE "COL-1" <= 100000 ORDER BY "COL-1" ASC'') t ("COL-1" int, "group" text, "Col3" timestamptz)',
'Check data for: mimeo_source."Updater-Test-Source"');
-- #### Serial based INSERTER & UPDATE and DML & LOGDEL should have worked fine and only gotten 500 rows. Should be warning in jobmon log, but don't need to test for that here
-- Batch is from 99999 to 100499 and one value is removed from high boundary
SELECT results_eq('SELECT col1, col2, col3 FROM mimeo_dest.inserter_test_dest_serial ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, col2, col3 FROM mimeo_source.inserter_test_source WHERE col1 < 100499 ORDER BY col1 ASC'') t (col1 int, col2 text, col3 timestamptz)',
'Check data for: mimeo_dest.inserter_test_dest_serial');
-- Batch is from 99999 to 100499 and one value is removed from high boundary
SELECT results_eq('SELECT col1, "group", "Col-3" FROM mimeo_dest."Inserter-Test-Source_Serial" ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, "group", "Col-3" FROM mimeo_source."Inserter-Test-Source" WHERE col1 < 100499 ORDER BY col1 ASC'') t (col1 int, "group" text, "Col-3" timestamptz)',
'Check data for: mimeo_dest."Inserter-Test-Source_Serial"');
-- Batch is from 99999 to 100499 and one value is removed from high boundary
SELECT results_eq('SELECT col1, col2, col3, col4 FROM mimeo_dest.updater_test_dest_serial ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, col2, col3, col4 FROM mimeo_source.updater_test_source WHERE col1 < 100499 ORDER BY col1 ASC'') t (col1 int, col2 text, col3 timestamptz, col4 int)',
'Check data for: mimeo_dest.updater_test_dest_serial');
-- Batch is from 99999 to 100499 and one value is removed from high boundary
SELECT results_eq('SELECT "COL-1", "group", "Col3" FROM mimeo_dest."Updater-Test-Source_Serial" ORDER BY "COL-1" ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT "COL-1", "group", "Col3" FROM mimeo_source."Updater-Test-Source" WHERE "COL-1" < 100499 ORDER BY "COL-1" ASC'') t ("COL-1" int, "group" text, "Col3" timestamptz)',
'Check data for: mimeo_source."Updater-Test-Source_Serial"');
SELECT results_eq('SELECT col1, col2, col3 FROM mimeo_source.dml_test_source ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, col2, col3 FROM mimeo_source.dml_test_source WHERE col1 <= 100500 ORDER BY col1 ASC'') t (col1 int, col2 text, col3 timestamptz)',
'Check data for: mimeo_source.dml_test_source');
SELECT results_eq('SELECT "COL1", "group", "Col-3" FROM mimeo_source."Dml-Test-Source" ORDER BY "COL1" ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT "COL1", "group", "Col-3" FROM mimeo_source."Dml-Test-Source" WHERE "COL1" <= 100500 ORDER BY "COL1" ASC'') t ("COL1" int, "group" text, "Col-3" timestamptz)',
'Check data for: mimeo_source.Dml-Test-Source');
SELECT results_eq('SELECT col1, col2, col3 FROM mimeo_source.logdel_test_source ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, col2, col3 FROM mimeo_source.logdel_test_source WHERE col1 <= 100500 ORDER BY col1 ASC'') t (col1 int, col2 text, col3 timestamptz)',
'Check data for: mimeo_source.logdel_test_source');
SELECT results_eq('SELECT "COL1", "group", "Col-3" FROM mimeo_source."LogDel-Test-Source" ORDER BY "COL1" ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT "COL1", "group", "Col-3" FROM mimeo_source."LogDel-Test-Source" WHERE "COL1" <= 100500 ORDER BY "COL1" ASC'') t ("COL1" int, "group" text, "Col-3" timestamptz)',
'Check data for: mimeo_source.LogDel-Test-Source');
SELECT diag('Running full data refresh for: mimeo_source.inserter_test_source');
SELECT refresh_inserter('mimeo_source.inserter_test_source', p_limit := 20000);
SELECT diag('Running full data refresh for: mimeo_source.Inserter-Test-Source');
SELECT refresh_inserter('mimeo_source.Inserter-Test-Source', p_limit := 20000);
SELECT diag('Running full data refresh for: mimeo_dest.inserter_test_dest_serial');
SELECT refresh_inserter('mimeo_dest.inserter_test_dest_serial', p_limit := 20000);
SELECT diag('Running full data refresh for: mimeo_dest.Inserter-Test-Source_Serial');
SELECT refresh_inserter('mimeo_dest.Inserter-Test-Source_Serial', p_limit := 20000);
SELECT diag('Running full data refresh for: mimeo_source.updater_test_source');
SELECT refresh_updater('mimeo_source.updater_test_source', p_limit := 20000);
SELECT diag('Running full data refresh for: mimeo_source.Updater-Test-Source');
SELECT refresh_updater('mimeo_source.Updater-Test-Source', p_limit := 20000);
SELECT diag('Running full data refresh for: mimeo_dest.updater_test_dest_serial');
SELECT refresh_updater('mimeo_dest.updater_test_dest_serial', p_limit := 20000);
SELECT diag('Running full data refresh for: mimeo_dest.Updater-Test-Source_Serial');
SELECT refresh_updater('mimeo_dest.Updater-Test-Source_Serial', p_limit := 20000);
SELECT diag('Running full data refresh for: mimeo_source.dml_test_source');
SELECT refresh_dml('mimeo_source.dml_test_source', p_limit := 20000);
SELECT diag('Running full data refresh for: mimeo_source.Dml-Test-Source');
SELECT refresh_dml('mimeo_source.Dml-Test-Source', p_limit := 20000);
SELECT diag('Running full data refresh for: mimeo_source.logdel_test_source');
SELECT refresh_logdel('mimeo_source.logdel_test_source', p_limit := 20000);
SELECT diag('Running full data refresh for: mimeo_source.LogDel-Test-Source');
SELECT refresh_logdel('mimeo_source.LogDel-Test-Source', p_limit := 20000);
-- #### Should now have all rows
SELECT results_eq('SELECT col1, col2, col3 FROM mimeo_source.inserter_test_source ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, col2, col3 FROM mimeo_source.inserter_test_source ORDER BY col1 ASC'') t (col1 int, col2 text, col3 timestamptz)',
'Check data for: mimeo_source.inserter_test_source');
SELECT results_eq('SELECT col1, "group", "Col-3" FROM mimeo_source."Inserter-Test-Source" ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, "group", "Col-3" FROM mimeo_source."Inserter-Test-Source" ORDER BY col1 ASC'') t (col1 int, "group" text, "Col-3" timestamptz)',
'Check data for: mimeo_source."Inserter-Test-Source"');
SELECT results_eq('SELECT col1, col2, col3 FROM mimeo_dest.inserter_test_dest_serial ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, col2, col3 FROM mimeo_source.inserter_test_source WHERE col1 < (SELECT max(col1) FROM mimeo_source.inserter_test_source) ORDER BY col1 ASC'') t (col1 int, col2 text, col3 timestamptz)',
'Check data for: mimeo_dest.inserter_test_dest_serial');
SELECT results_eq('SELECT col1, "group", "Col-3" FROM mimeo_dest."Inserter-Test-Source_Serial" ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, "group", "Col-3" FROM mimeo_source."Inserter-Test-Source" WHERE col1 < (SELECT max(col1) FROM mimeo_source."Inserter-Test-Source") ORDER BY col1 ASC'') t (col1 int, "group" text, "Col-3" timestamptz)',
'Check data for: mimeo_dest."Inserter-Test-Source_Serial"');
SELECT results_eq('SELECT col1, col2, col3 FROM mimeo_source.updater_test_source ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, col2, col3 FROM mimeo_source.updater_test_source ORDER BY col1 ASC'') t (col1 int, col2 text, col3 timestamptz)',
'Check data for: mimeo_source.updater_test_source');
SELECT results_eq('SELECT "COL-1", "group", "Col3" FROM mimeo_source."Updater-Test-Source" ORDER BY "COL-1" ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT "COL-1", "group", "Col3" FROM mimeo_source."Updater-Test-Source" ORDER BY "COL-1" ASC'') t ("COL-1" int, "group" text, "Col3" timestamptz)',
'Check data for: mimeo_source."Updater-Test-Source"');
SELECT results_eq('SELECT col1, col2, col3, col4 FROM mimeo_dest.updater_test_dest_serial ORDER BY col4 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, col2, col3, col4 FROM mimeo_source.updater_test_source WHERE col4 < (SELECT max(col4) FROM mimeo_source.updater_test_source) ORDER BY col4 ASC'') t (col1 int, col2 text, col3 timestamptz, col4 int)',
'Check data for: mimeo_dest.updater_test_dest_serial');
SELECT results_eq('SELECT "COL-1", "group", "Col3" FROM mimeo_dest."Updater-Test-Source_Serial" ORDER BY "COL-1" ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT "COL-1", "group", "Col3" FROM mimeo_source."Updater-Test-Source" WHERE "COL-1" < (SELECT max("COL-1") FROM mimeo_source."Updater-Test-Source") ORDER BY "COL-1" ASC'') t ("COL-1" int, "group" text, "Col3" timestamptz)',
'Check data for: mimeo_source."Updater-Test-Source_Serial"');
SELECT results_eq('SELECT col1, col2, col3 FROM mimeo_source.dml_test_source ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, col2, col3 FROM mimeo_source.dml_test_source ORDER BY col1 ASC'') t (col1 int, col2 text, col3 timestamptz)',
'Check data for: mimeo_source.dml_test_source');
SELECT results_eq('SELECT "COL1", "group", "Col-3" FROM mimeo_source."Dml-Test-Source" ORDER BY "COL1" ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT "COL1", "group", "Col-3" FROM mimeo_source."Dml-Test-Source" ORDER BY "COL1" ASC'') t ("COL1" int, "group" text, "Col-3" timestamptz)',
'Check data for: mimeo_source.Dml-Test-Source');
SELECT results_eq('SELECT col1, col2, col3 FROM mimeo_source.logdel_test_source ORDER BY col1 ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT col1, col2, col3 FROM mimeo_source.logdel_test_source ORDER BY col1 ASC'') t (col1 int, col2 text, col3 timestamptz)',
'Check data for: mimeo_source.logdel_test_source');
SELECT results_eq('SELECT "COL1", "group", "Col-3" FROM mimeo_source."LogDel-Test-Source" ORDER BY "COL1" ASC',
'SELECT * FROM dblink(''mimeo_test'', ''SELECT "COL1", "group", "Col-3" FROM mimeo_source."LogDel-Test-Source" ORDER BY "COL1" ASC'') t ("COL1" int, "group" text, "Col-3" timestamptz)',
'Check data for: mimeo_source.LogDel-Test-Source');
SELECT dblink_disconnect('mimeo_test');
--SELECT is_empty('SELECT dblink_get_connections() @> ''{mimeo_test}''', 'Close remote database connection');
SELECT * FROM finish();
|
