openGauss-server
/
hw_partition_interval_exchange.out

--
-- 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;