forked from pgpartman/pg_partman
-
Notifications
You must be signed in to change notification settings - Fork 0
/
test-time-epoch-time-time-subpart.sql
226 lines (200 loc) · 29.4 KB
/
test-time-epoch-time-time-subpart.sql
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
-- ########## TIME EPOCH PARENT / TIME EPOCH SUBPARENT / TIME EPOCH SUB-SUB-PARENT DYNAMIC ##########
-- Currently tests 23, 39, 47 & 67 may fail around new years boundaries
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true
BEGIN;
SELECT set_config('search_path','partman, public',false);
SELECT plan(72);
CREATE SCHEMA partman_test;
CREATE TABLE partman_test.time_taptest_table (col1 int primary key, col2 text, col3 bigint NOT NULL DEFAULT extract('epoch' from now()));
INSERT INTO partman_test.time_taptest_table (col1, col3) VALUES (generate_series(1,10), extract('epoch' from CURRENT_TIMESTAMP));
-- yearly
SELECT create_parent('partman_test.time_taptest_table', 'col3', 'time', 'yearly', p_premake := 2, p_epoch := true);
-- Make sure optimize values can be different
UPDATE part_config SET optimize_trigger = 5, optimize_constraint = 10 WHERE parent_table = 'partman_test.time_taptest_table';
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY'), 'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||' exists');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 year'::interval, 'YYYY'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 year'::interval, 'YYYY')||' exists');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||' exists');
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||' does not exist');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||' exists');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||' exists');
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'3 years'::interval, 'YYYY'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'3 years'::interval, 'YYYY')||' does not exist');
-- Move data from parent
SELECT results_eq('SELECT partition_data_time(''partman_test.time_taptest_table'')::int', ARRAY[10], 'Check that partitioning function returns correct count of rows moved');
SELECT is_empty('SELECT * FROM ONLY partman_test.time_taptest_table', 'Check that yearly parent table has had data moved to partition');
SELECT results_eq('SELECT count(*)::int FROM partman_test.time_taptest_table', ARRAY[10], 'Check count from parent table');
SELECT results_eq('SELECT count(*)::int FROM partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY'),
ARRAY[10], 'Check count from time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY'));
-- monthly
SELECT create_sub_parent('partman_test.time_taptest_table', 'col3', 'time', 'monthly', p_premake := 2, p_epoch := true);
-- Make sure optimize values can be different
UPDATE part_config_sub SET sub_optimize_trigger = 5, sub_optimize_constraint = 10, sub_retention_keep_table = false WHERE sub_parent = 'partman_test.time_taptest_table';
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM')||' exists');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 month'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 month'::interval, 'YYYY_MM'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 month'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 month'::interval, 'YYYY_MM')||' exists');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 months'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'2 months'::interval, 'YYYY_MM'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 months'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'2 months'::interval, 'YYYY_MM')||' exists');
-- Near end of year (Oct) following may fail since next hear's minimum month table will be created
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 months'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 months'::interval, 'YYYY_MM'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 months'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 months'::interval, 'YYYY_MM')||' does not exist (this test may fail around year boundary. See comment in test code)');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 month'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'1 month'::interval, 'YYYY_MM'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 month'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'1 month'::interval, 'YYYY_MM')||' exists');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 months'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'2 months'::interval, 'YYYY_MM'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 months'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'2 months'::interval, 'YYYY_MM')||' exists');
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'3 months'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'3 months'::interval, 'YYYY_MM'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'3 months'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'3 months'::interval, 'YYYY_MM')||' does not exist');
-- Check that previous and future years had the minimal partition made
-- year +/- 1 tests may fail around year boundary. Tables may or may not exist depending on premake. That's fine. Should be ok for further in the future.
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 year'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 year'::interval, 'YYYY')||'_01',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 year'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 year'::interval, 'YYYY')||'_01 exists (this test may fail around year boundary. See comment in test code)');
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 year'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 year'::interval, 'YYYY')||'_02',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 year'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 year'::interval, 'YYYY')||'_02 does not exists (this test may fail around year boundary. See comment in test code)');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_01',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_01 exists');
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_02',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_02 does not exists');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_01',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_01 exists (this test may fail around year boundary. See comment in test code)');
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_02',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_02 exists (this test may fail around year boundary. See comment in test code)');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_01',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'2 year'::interval, 'YYYY')||'_01 exists');
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_02',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'2 year'::interval, 'YYYY')||'_02 exists');
SELECT results_eq('SELECT partition_data_time(''partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||''')::int', ARRAY[10], 'Check that partitioning function returns correct count of rows moved (yearly subparent)');
SELECT is_empty('SELECT * FROM ONLY partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY'), 'Check data got moved out of time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY'));
SELECT results_eq('SELECT count(*)::int FROM partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM'),
ARRAY[10], 'Check count from time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM'));
-- Check subpart config
SELECT results_eq('SELECT sub_parent FROM part_config_sub ORDER BY sub_parent',
ARRAY['partman_test.time_taptest_table'],
'Check that part_config_sub has all tables configured as needed');
-- daily
SELECT results_eq('SELECT create_sub_parent(''partman_test.time_taptest_table_p''||to_char(CURRENT_TIMESTAMP, ''YYYY''), ''col3'', ''time'', ''daily'', p_premake := 2, p_epoch := true)',
ARRAY[true], 'Subpartitioning partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||' should return true');
SELECT results_eq('SELECT create_sub_parent(''partman_test.time_taptest_table_p''||to_char(CURRENT_TIMESTAMP+''1 year''::interval, ''YYYY''), ''col3'', ''time'', ''daily'', p_premake := 2, p_epoch := true)',
ARRAY[true], 'Subpartitioning partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 year'::interval, 'YYYY')||' should return true');
SELECT results_eq('SELECT create_sub_parent(''partman_test.time_taptest_table_p''||to_char(CURRENT_TIMESTAMP+''2 years''::interval, ''YYYY''), ''col3'', ''time'', ''daily'', p_premake := 2, p_epoch := true)',
ARRAY[true], 'Subpartitioning partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||' should return true');
SELECT results_eq('SELECT create_sub_parent(''partman_test.time_taptest_table_p''||to_char(CURRENT_TIMESTAMP-''1 year''::interval, ''YYYY''), ''col3'', ''time'', ''daily'', p_premake := 2, p_epoch := true)',
ARRAY[true], 'Subpartitioning partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||' should return true');
SELECT results_eq('SELECT create_sub_parent(''partman_test.time_taptest_table_p''||to_char(CURRENT_TIMESTAMP-''2 years''::interval, ''YYYY''), ''col3'', ''time'', ''daily'', p_premake := 2, p_epoch := true)',
ARRAY[true], 'Subpartitioning partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||' should return true');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM_DD'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM')||' exists')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM_DD');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 day'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 day'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP+'1 day'::interval, 'YYYY_MM_DD'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 day'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 day'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP+'1 day'::interval, 'YYYY_MM_DD')||' exists');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 days'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'2 days'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP+'2 days'::interval, 'YYYY_MM_DD'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 days'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'2 days'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP+'2 days'::interval, 'YYYY_MM_DD')||' does not exists');
-- This test may fail around the end of the year or the end of some months since the minimal partition for the next year or next month was created. That's fine
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY_MM_DD'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY_MM_DD')||' does not exist (this test may fail around year or month boundaries. See comment in test code)');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 day'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'1 day'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP-'1 day'::interval, 'YYYY_MM_DD'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 day'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'1 day'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP-'1 day'::interval, 'YYYY_MM_DD')||' exists');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 days'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'2 days'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP-'2 days'::interval, 'YYYY_MM_DD'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 days'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'2 days'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP-'2 days'::interval, 'YYYY_MM_DD')||' exists');
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'3 days'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'3 days'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP-'3 days'::interval, 'YYYY_MM_DD'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'3 days'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'3 days'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP-'3 days'::interval, 'YYYY_MM_DD')||' does not exist');
-- Check that previous and future years had the minimal partition made
-- year +/- 1 tests may fail around year boundary. Tables may or may not exist depending on premake. That's fine. Should be ok for further in the future.
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 years'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP+'1 years'::interval, 'YYYY')||'_01_01',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 years'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP+'1 years'::interval, 'YYYY')||'_01_01 exists (this test may fail around year boundary. See comment in test code)');
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 years'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP+'1 years'::interval, 'YYYY')||'_01_02',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 years'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP+'1 years'::interval, 'YYYY')||'_01_02 does not exists (this test may fail around year boundary. See comment in test code)');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_01_01',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_01_01 exists');
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_01_02',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')||'_01_02 does not exists');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_01_01',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_01_01 exists (this test may fail around year boundary. See comment in test code)');
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_01_02',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY')||'_01_02 exists (this test may fail around year boundary. See comment in test code)');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_01_01',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'2 year'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_01_01 exists');
SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_01_02',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP-'2 year'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY')||'_01_02 does not exists');
SELECT results_eq('SELECT partition_data_time(''partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM')||''')::int',
ARRAY[10], 'Check that partitioning function returns correct count of rows moved (monthly subparent)');
SELECT is_empty('SELECT * FROM ONLY partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM'),
'Check data got moved out of time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM'));
SELECT results_eq('SELECT count(*)::int FROM partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM_DD'),
ARRAY[10], 'Check count from time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM_DD'));
-- Check subpart config
SELECT results_eq('SELECT sub_parent FROM part_config_sub ORDER BY sub_parent',
ARRAY['partman_test.time_taptest_table',
'partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY'),
'partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY'),
'partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY'),
'partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 year'::interval, 'YYYY'),
'partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY')],
'Check that part_config_sub has all tables configured as needed');
INSERT INTO partman_test.time_taptest_table (col1, col2, col3) VALUES (generate_series(11,20), 'stuff', extract('epoch' from CURRENT_TIMESTAMP+'1 day'::interval));
SELECT is_empty('SELECT * FROM ONLY partman_test.time_taptest_table', 'Check new data did not go into parent time_taptest_table');
SELECT is_empty('SELECT * FROM ONLY partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 day', 'YYYY'),
'Check new data did not go into subparent time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 day', 'YYYY'));
SELECT is_empty('SELECT * FROM ONLY partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 day', 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 day', 'YYYY_MM'),
'Check new data did not go into subparent time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 day', 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 day', 'YYYY_MM'));
SELECT results_eq('SELECT count(*)::int FROM partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 day', 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 day', 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP+'1 day', 'YYYY_MM_DD'),
ARRAY[10], 'Check count from time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 day', 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'1 day', 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP+'1 day', 'YYYY_MM_DD'));
UPDATE part_config SET premake = 3, optimize_trigger = 3 WHERE parent_table LIKE 'partman_test.time_taptest_table%' AND partition_type = 'time';
SELECT run_maintenance();
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||' exists');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 months'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 months'::interval, 'YYYY_MM'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 months'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 months'::interval, 'YYYY_MM')||' exists');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY_MM_DD'),
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY_MM_DD')||' exists');
-- Check that future year had the minimal partition made
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_01',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_01 exists');
SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_01_01',
'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_01_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_01_01 exists');
-- Check subpart config
SELECT results_eq('SELECT sub_parent FROM part_config_sub ORDER BY sub_parent',
ARRAY['partman_test.time_taptest_table',
'partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 years'::interval, 'YYYY'),
'partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 year'::interval, 'YYYY'),
'partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY'),
'partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 year'::interval, 'YYYY'),
'partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 years'::interval, 'YYYY'),
'partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')],
'Check that part_config_sub has all tables configured as needed');
INSERT INTO partman_test.time_taptest_table (col1, col2, col3) VALUES (generate_series(21,30), 'stuff', extract('epoch' from CURRENT_TIMESTAMP+'3 years'::interval));
SELECT is_empty('SELECT * FROM ONLY partman_test.time_taptest_table', 'Check new data did not go into parent time_taptest_table');
SELECT results_eq('SELECT count(*)::int FROM partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY'),
ARRAY[10], 'Check count from time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY')||'. Data should have gone here since monthly subpartition for it does not exist. This test may fail in January since that monthly partition should exist.');
-- Move data from yearly parent table and create appropriate monthly child for it
SELECT results_eq('SELECT partition_data_time(''partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||''')::int',
ARRAY[10], 'Check that partitioning function returns correct count of rows moved from time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'. This test may fail in January since that monthly partition should exist.');
SELECT is_empty('SELECT * FROM ONLY partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY'),
'Check new data did not go into subparent time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY'));
SELECT results_eq('SELECT count(*)::int FROM partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY_MM'),
ARRAY[10], 'Check count from time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY_MM')||'. Data should have gone here since daily subpartition for it does not exist.');
-- Move data from monthly parent table and create appropriate daily child for it
SELECT results_eq('SELECT partition_data_time(''partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY_MM')||''')::int',
ARRAY[10], 'Check that partitioning function returns correct count of rows moved from time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY_MM'));
SELECT is_empty('SELECT * FROM ONLY partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY_MM'),
'Check new data did not go into subparent time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY_MM'));
SELECT results_eq('SELECT count(*)::int FROM partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY_MM_DD'),
ARRAY[10], 'Check count from time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY_MM')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years', 'YYYY_MM_DD'));
/*
-- Disabled test for now. New years makes testing undo functions hard. Would be calling undo on year+1_01 twice and second one would fail.
SELECT results_eq('SELECT count(*)::int FROM partman_test.time_taptest_table', ARRAY[30], 'Check count from top parent');
SELECT throws_ok('SELECT undo_partition_time(''partman_test.time_taptest_table_p''||to_char(CURRENT_TIMESTAMP+''3 years''::interval, ''YYYY''), 20, p_keep_table := false)',
'P0001',
'Child table for this parent has child table(s) itself (partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_p'||to_char(CURRENT_TIMESTAMP+'3 years'::interval, 'YYYY')||'_01). Run undo partitioning on this table or remove inheritance first to ensure all data is properly moved to parent
CONTEXT: SQL statement "SELECT undo_partition_time(''partman_test.time_taptest_table_p''||to_char(CURRENT_TIMESTAMP+''3 years''::interval, ''YYYY''), 20, p_keep_table := false)"
PL/pgSQL function throws_ok(text,character,text,text) line 16 at EXECUTE statement
DETAIL:
HINT: ',
'Check that undoing partitions is prevented if subpartitions still exist');
*/
SELECT * FROM finish();
ROLLBACK;