-
Notifications
You must be signed in to change notification settings - Fork 235
/
hw_partition_interval_exchange.out
321 lines (297 loc) · 12.9 KB
/
hw_partition_interval_exchange.out
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
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
--
-- Test exchange operator for interval partitioned table
--
--
---- create interval partitioned table
--
CREATE TABLE interval_normal_exchange (logdate date not null)
PARTITION BY RANGE (logdate)
INTERVAL ('1 month')
(
PARTITION interval_normal_exchange_p1 VALUES LESS THAN ('2020-03-01'),
PARTITION interval_normal_exchange_p2 VALUES LESS THAN ('2020-04-01'),
PARTITION interval_normal_exchange_p3 VALUES LESS THAN ('2020-05-01')
);
-- see about the info of the partitioned table in pg_partition
select relname, parttype, partstrategy, boundaries from pg_partition
where parentid = (select oid from pg_class where relname = 'interval_normal_exchange')
order by relname;
relname | parttype | partstrategy | boundaries
-----------------------------+----------+--------------+--------------
interval_normal_exchange | r | i |
interval_normal_exchange_p1 | p | r | {2020-03-01}
interval_normal_exchange_p2 | p | r | {2020-04-01}
interval_normal_exchange_p3 | p | r | {2020-05-01}
(4 rows)
-- insert the record that is smaller than the lower boundary
insert into interval_normal_exchange values ('2020-02-21');
insert into interval_normal_exchange values ('2020-02-22');
insert into interval_normal_exchange values ('2020-02-23');
insert into interval_normal_exchange values ('2020-5-01');
insert into interval_normal_exchange values ('2020-5-02');
insert into interval_normal_exchange values ('2020-5-03');
-- see about the info of the partitioned table in pg_partition
select relname, parttype, partstrategy, boundaries from pg_partition
where parentid = (select oid from pg_class where relname = 'interval_normal_exchange')
order by relname;
relname | parttype | partstrategy | boundaries
-----------------------------+----------+--------------+------------------------------
interval_normal_exchange | r | i |
interval_normal_exchange_p1 | p | r | {2020-03-01}
interval_normal_exchange_p2 | p | r | {2020-04-01}
interval_normal_exchange_p3 | p | r | {2020-05-01}
sys_p1 | p | i | {"Mon Jun 01 00:00:00 2020"}
(5 rows)
--
---- create to be exchanged table and test range partition exchange
--
CREATE TABLE interval_exchange_test (logdate date not null);
insert into interval_exchange_test values ('2020-02-24');
insert into interval_exchange_test values ('2020-02-25');
insert into interval_exchange_test values ('2020-02-26');
-- do exchange partition interval_normal_exchange_p1 and interval_exchange_test
-- The data they have belongs to the same range.
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (interval_normal_exchange_p1)
WITH TABLE interval_exchange_test;
select * from interval_normal_exchange partition (interval_normal_exchange_p1)order by logdate;
logdate
--------------------------
Mon Feb 24 00:00:00 2020
Tue Feb 25 00:00:00 2020
Wed Feb 26 00:00:00 2020
(3 rows)
select * from interval_exchange_test order by logdate;
logdate
--------------------------
Fri Feb 21 00:00:00 2020
Sat Feb 22 00:00:00 2020
Sun Feb 23 00:00:00 2020
(3 rows)
-- exchange back
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (interval_normal_exchange_p1)
WITH TABLE interval_exchange_test;
select * from interval_normal_exchange partition (interval_normal_exchange_p1)order by logdate;
logdate
--------------------------
Fri Feb 21 00:00:00 2020
Sat Feb 22 00:00:00 2020
Sun Feb 23 00:00:00 2020
(3 rows)
select * from interval_exchange_test order by logdate;
logdate
--------------------------
Mon Feb 24 00:00:00 2020
Tue Feb 25 00:00:00 2020
Wed Feb 26 00:00:00 2020
(3 rows)
-- Insert a new record not belongs to interval_normal_exchange_p1
insert into interval_exchange_test values ('2020-3-05');
-- defaut is WITH VALIDATION, and the exchange will be failed
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (interval_normal_exchange_p1)
WITH TABLE interval_exchange_test;
ERROR: some rows in table do not qualify for specified partition
-- WITHOUT VALIDATION and the exchange will be success, but some date will in the wrong range;
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (interval_normal_exchange_p1)
WITH TABLE interval_exchange_test WITHOUT VALIDATION;
select * from interval_normal_exchange partition (interval_normal_exchange_p1)order by logdate;
logdate
--------------------------
Mon Feb 24 00:00:00 2020
Tue Feb 25 00:00:00 2020
Wed Feb 26 00:00:00 2020
Thu Mar 05 00:00:00 2020
(4 rows)
select * from interval_exchange_test order by logdate;
logdate
--------------------------
Fri Feb 21 00:00:00 2020
Sat Feb 22 00:00:00 2020
Sun Feb 23 00:00:00 2020
(3 rows)
-- not include '2020-3-05'
select * from interval_normal_exchange where logdate > '2020-03-01' order by logdate;
logdate
--------------------------
Fri May 01 00:00:00 2020
Sat May 02 00:00:00 2020
Sun May 03 00:00:00 2020
(3 rows)
--
---- clean the data and test interval partition exchange
--
truncate table interval_exchange_test;
insert into interval_exchange_test values ('2020-5-04');
insert into interval_exchange_test values ('2020-5-05');
insert into interval_exchange_test values ('2020-5-06');
-- exchange table
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (sys_p1)
WITH TABLE interval_exchange_test;
select * from interval_normal_exchange partition (sys_p1)order by logdate;
logdate
--------------------------
Mon May 04 00:00:00 2020
Tue May 05 00:00:00 2020
Wed May 06 00:00:00 2020
(3 rows)
select * from interval_exchange_test order by logdate;
logdate
--------------------------
Fri May 01 00:00:00 2020
Sat May 02 00:00:00 2020
Sun May 03 00:00:00 2020
(3 rows)
-- exchange back
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (sys_p1)
WITH TABLE interval_exchange_test;
select * from interval_normal_exchange partition (sys_p1)order by logdate;
logdate
--------------------------
Fri May 01 00:00:00 2020
Sat May 02 00:00:00 2020
Sun May 03 00:00:00 2020
(3 rows)
select * from interval_exchange_test order by logdate;
logdate
--------------------------
Mon May 04 00:00:00 2020
Tue May 05 00:00:00 2020
Wed May 06 00:00:00 2020
(3 rows)
insert into interval_exchange_test values ('2020-6-05');
-- defaut is WITH VALIDATION, and the exchange will be failed
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (interval_normal_exchange_p1)
WITH TABLE interval_exchange_test;
ERROR: some rows in table do not qualify for specified partition
-- WITHOUT VALIDATION and the exchange will be success, but some date will in the wrong range;
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (interval_normal_exchange_p1)
WITH TABLE interval_exchange_test WITHOUT VALIDATION;
select * from interval_normal_exchange partition (interval_normal_exchange_p1)order by logdate;
logdate
--------------------------
Mon May 04 00:00:00 2020
Tue May 05 00:00:00 2020
Wed May 06 00:00:00 2020
Fri Jun 05 00:00:00 2020
(4 rows)
select * from interval_exchange_test order by logdate;
logdate
--------------------------
Mon Feb 24 00:00:00 2020
Tue Feb 25 00:00:00 2020
Wed Feb 26 00:00:00 2020
Thu Mar 05 00:00:00 2020
(4 rows)
-- not include '2020-6-05'
select * from interval_normal_exchange order by logdate;
logdate
--------------------------
Fri May 01 00:00:00 2020
Sat May 02 00:00:00 2020
Sun May 03 00:00:00 2020
Mon May 04 00:00:00 2020
Tue May 05 00:00:00 2020
Wed May 06 00:00:00 2020
Fri Jun 05 00:00:00 2020
(7 rows)
select * from interval_normal_exchange where logdate > '2020-06-01' order by logdate;
logdate
---------
(0 rows)
drop table interval_normal_exchange;
drop table table_001;
ERROR: table "table_001" does not exist
create table table_001(
COL_1 smallint,
COL_2 char(5),
COL_3 int,
COL_4 date,
COL_5 boolean,
COL_6 nchar(5),
COL_7 float
);
drop table partition_table_001;
ERROR: table "partition_table_001" does not exist
create table partition_table_001(
COL_1 smallint,
COL_2 char(5),
COL_3 int,
COL_4 date,
COL_5 boolean,
COL_6 nchar(5),
COL_7 float
)
PARTITION BY RANGE (COL_4)
INTERVAL ('1 month')
(
PARTITION partition_table_001_p1 VALUES LESS THAN ('2020-03-01'),
PARTITION partition_table_001_p2 VALUES LESS THAN ('2020-04-01'),
PARTITION partition_table_001_p3 VALUES LESS THAN ('2020-05-01')
);
insert into partition_table_001 values (1,'aaa',1,'2020-02-23',true,'aaa',1.1);
insert into partition_table_001 values (2,'bbb',2,'2020-03-23',false,'bbb',2.2);
insert into partition_table_001 values (3,'ccc',3,'2020-04-23',true,'ccc',3.3);
insert into partition_table_001 values (4,'ddd',4,'2020-05-23',false,'ddd',4.4);
insert into partition_table_001 values (5,'eee',5,'2020-06-23',true,'eee',5.5);
insert into partition_table_001 values (6,'fff',6,'2020-07-23',false,'fff',6.6);
ALTER TABLE partition_table_001 EXCHANGE PARTITION (sys_p1) WITH TABLE table_001;
select * from table_001 order by 1;
col_1 | col_2 | col_3 | col_4 | col_5 | col_6 | col_7
-------+-------+-------+--------------------------+-------+-------+-------
4 | ddd | 4 | Sat May 23 00:00:00 2020 | f | ddd | 4.4
(1 row)
select * from partition_table_001 order by 1;
col_1 | col_2 | col_3 | col_4 | col_5 | col_6 | col_7
-------+-------+-------+--------------------------+-------+-------+-------
1 | aaa | 1 | Sun Feb 23 00:00:00 2020 | t | aaa | 1.1
2 | bbb | 2 | Mon Mar 23 00:00:00 2020 | f | bbb | 2.2
3 | ccc | 3 | Thu Apr 23 00:00:00 2020 | t | ccc | 3.3
5 | eee | 5 | Tue Jun 23 00:00:00 2020 | t | eee | 5.5
6 | fff | 6 | Thu Jul 23 00:00:00 2020 | f | fff | 6.6
(5 rows)
select relname, parttype, partstrategy, boundaries from pg_partition
where parentid = (select oid from pg_class where relname = 'partition_table_001')
order by relname;
relname | parttype | partstrategy | boundaries
------------------------+----------+--------------+------------------------------
partition_table_001 | r | i |
partition_table_001_p1 | p | r | {2020-03-01}
partition_table_001_p2 | p | r | {2020-04-01}
partition_table_001_p3 | p | r | {2020-05-01}
sys_p1 | p | i | {"Mon Jun 01 00:00:00 2020"}
sys_p2 | p | i | {"Wed Jul 01 00:00:00 2020"}
sys_p3 | p | i | {"Sat Aug 01 00:00:00 2020"}
(7 rows)
ALTER TABLE partition_table_001 EXCHANGE PARTITION (sys_p1) WITH TABLE table_001;
select * from table_001 order by 1;
col_1 | col_2 | col_3 | col_4 | col_5 | col_6 | col_7
-------+-------+-------+-------+-------+-------+-------
(0 rows)
select * from partition_table_001 order by 1;
col_1 | col_2 | col_3 | col_4 | col_5 | col_6 | col_7
-------+-------+-------+--------------------------+-------+-------+-------
1 | aaa | 1 | Sun Feb 23 00:00:00 2020 | t | aaa | 1.1
2 | bbb | 2 | Mon Mar 23 00:00:00 2020 | f | bbb | 2.2
3 | ccc | 3 | Thu Apr 23 00:00:00 2020 | t | ccc | 3.3
4 | ddd | 4 | Sat May 23 00:00:00 2020 | f | ddd | 4.4
5 | eee | 5 | Tue Jun 23 00:00:00 2020 | t | eee | 5.5
6 | fff | 6 | Thu Jul 23 00:00:00 2020 | f | fff | 6.6
(6 rows)
insert into table_001 values (7,'eee',7,'2020-08-23',true,'eee',7.7);
ALTER TABLE partition_table_001 EXCHANGE PARTITION (sys_p1) WITH TABLE table_001 with validation verbose;
select * from table_001 order by 1;
col_1 | col_2 | col_3 | col_4 | col_5 | col_6 | col_7
-------+-------+-------+--------------------------+-------+-------+-------
4 | ddd | 4 | Sat May 23 00:00:00 2020 | f | ddd | 4.4
(1 row)
select * from partition_table_001 order by 1;
col_1 | col_2 | col_3 | col_4 | col_5 | col_6 | col_7
-------+-------+-------+--------------------------+-------+-------+-------
1 | aaa | 1 | Sun Feb 23 00:00:00 2020 | t | aaa | 1.1
2 | bbb | 2 | Mon Mar 23 00:00:00 2020 | f | bbb | 2.2
3 | ccc | 3 | Thu Apr 23 00:00:00 2020 | t | ccc | 3.3
5 | eee | 5 | Tue Jun 23 00:00:00 2020 | t | eee | 5.5
6 | fff | 6 | Thu Jul 23 00:00:00 2020 | f | fff | 6.6
7 | eee | 7 | Sun Aug 23 00:00:00 2020 | t | eee | 7.7
(6 rows)
drop table table_001;
drop table partition_table_001;