1、 测试环境

select pw_version();
                                 pw_version                                  
-----------------------------------------------------------------------------
 (PanWeiDB_V2.0-S3.1.0_B01) compiled at 2024-12-23 00:47:15 commit 5d08dc9  +
  product name:PanWeiDB                                                     +
  version:V2.0-S3.1.0_B01                                                   +
  commit:5d08dc9                                                            +
  openGauss version:5.0.0                                                   +
  host:x86_64-pc-linux-gnu
(1 row)

2、 测试用表

/* 删除同名表 */
drop table if exists t_test_part_range cascade;
/* 创建测试分区表 */
create table if not exists t_test_part_range 
(
id int not null,
code text not null,
create_date date not null
)
partition by range(create_date) (
partition p_20200412 values less than(to_date('2021-01-01','yyyy-mm-dd')),
partition p_20210401 values less than(to_date('2021-02-01','yyyy-mm-dd')),
partition p_20210402 values less than(to_date('2021-03-01','yyyy-mm-dd')),
partition p_20210403 values less than(to_date('2021-04-01','yyyy-mm-dd')),
partition p_20210404 values less than(to_date('2021-05-01','yyyy-mm-dd')),
partition p_20210405 values less than(to_date('2021-06-01','yyyy-mm-dd')),
partition p_20210406 values less than(to_date('2021-07-01','yyyy-mm-dd')),
partition p_20210407 values less than(to_date('2021-08-01','yyyy-mm-dd')),
partition p_20210408 values less than(to_date('2021-09-01','yyyy-mm-dd')),
partition p_20210409 values less than(to_date('2021-10-01','yyyy-mm-dd')),
partition p_20210410 values less than(to_date('2021-11-01','yyyy-mm-dd')),
partition p_20210411 values less than(to_date('2021-12-01','yyyy-mm-dd')),
partition p_20210412 values less than(to_date('2022-01-01','yyyy-mm-dd')),
partition p_max values less than(maxvalue)
);
/* 查看分区及边界 */
select relname, parttype, parentid, boundaries 
from pg_partition
where parentid in (select oid from pg_class where relname = 't_test_part_range');
/* 创建序列 */
create sequence seq_t_test_part_range;
/* 插入测试数据 */
insert into t_test_part_range 
SELECT seq_t_test_part_range.nextval as id,
       uuid() AS code,
       rn / 24 / 60 + to_date('2021-01-01','yyyy-mm-dd')  AS create_date
  from generate_series(1,365*24*60) AS rn;

/* 创建全局索引 */
create index t_test_part_range_n1 on t_test_part_range(code);

3、 测试truncate分区

按下面顺序在不同会话中执行

3.1 truncate前在事物中更改数据

begin;
select * from t_test_part_range partition(p_20210408) limit 5;
delete from t_test_part_range where code = '62F4CE69886FAC6FABDC961F9E5E5943';
update t_test_part_range set code = uuid() where code = '10325D2632AAB009BF7DF4246431A938';

3.2 truncate分区

在事务中清除分区

begin;
alter table t_test_part_range truncate partition for (to_date('2021-02-02','yyyy-mm-dd')) update global index;

3.3 truncate后在事物中更改数据

begin;
select * from t_test_part_range partition(p_20210409) limit 5;
delete from t_test_part_range where code = '1231CF6DABCEE11909521E5F05468F45';
update t_test_part_range set code = uuid() where code = '69608963E3D06049AE9E272F5B683732';

3.4 truncate测试完验正全局索引

select owner,index_name,table_owner,status from all_indexes where index_name = upper('t_test_part_range_n1');
explain analyze select * from t_test_part_range where code = '1B4DD167936B884273B39A213A23C54A';

验正通过

orcl=> select owner,index_name,table_owner,status from all_indexes where index_name = upper('t_test_part_range_n1');
 owner |      index_name      | table_owner | status 
-------+----------------------+-------------+--------
 SCOTT | T_TEST_PART_RANGE_N1 | SCOTT       | VALID
(1 row)

orcl=> explain analyze select * from t_test_part_range where code = '1B4DD167936B884273B39A213A23C54A';
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using t_test_part_range_n1 on t_test_part_range  (cost=0.00..8.27 rows=1 width=45) (actual time=0.221..0.222 rows=1 loops=1)
   Index Cond: (code = '1B4DD167936B884273B39A213A23C54A'::text)
 Total runtime: 0.264 ms
(3 rows)

4、 测试drop分区

按下面顺序在不同会话中执行

4.1 drop前在事物中更改数据

begin;
select * from t_test_part_range partition(p_20210408) limit 5;
delete from t_test_part_range where code = 'D1BA2936E0529E4D360F50446740E079';
update t_test_part_range set code = uuid() where code = '1B4DD167936B884273B39A213A23C54A';

4.2 drop分区

在事务中清除分区

begin;
alter table t_test_part_range drop partition for (to_date('2021-03-01','yyyy-mm-dd')) update global index;

4.3 drop后在事物中更改数据

begin;
select * from t_test_part_range partition(p_20210409) limit 5;
delete from t_test_part_range where code = '944AAA3FB048300BD84F056CBDB7BB40';
update t_test_part_range set code = uuid() where code = '3BAFA25BDCBCEF0ECFA23D562F87C419';

4.4 drop测试完验正全局索引

分别提交前面三个会话的事务后测试

select owner,index_name,table_owner,status from all_indexes where index_name = upper('t_test_part_range_n1');
explain analyze select * from t_test_part_range where code = '1B4DD167936B884273B39A213A23C54A';

验正通过

orcl=> select owner,index_name,table_owner,status from all_indexes where index_name = upper('t_test_part_range_n1');
 owner |      index_name      | table_owner | status 
-------+----------------------+-------------+--------
 SCOTT | T_TEST_PART_RANGE_N1 | SCOTT       | VALID
(1 row)

Time: 13.431 ms
orcl=> explain analyze select * from t_test_part_range where code = '37A13B263BE32A29B5FA374014083E5A';
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using t_test_part_range_n1 on t_test_part_range  (cost=0.00..8.27 rows=1 width=45) (actual time=0.093..0.094 rows=1 loops=1)
   Index Cond: (code = '37A13B263BE32A29B5FA374014083E5A'::text)
 Total runtime: 0.177 ms
(3 rows)

Time: 1.663 ms

5、结论

在本版本(V2.0-S3.1.0_B01)测试中
清除与删除分区与其它分区中的修改操作互不影响
使用参数update global index可以同步维护全局索引