forked from openGauss-Ecosystem/openGauss-server
322 lines
13 KiB
Plaintext
322 lines
13 KiB
Plaintext
--
|
|
-- 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;
|