PostgreSQL检查分区表下个月分区是否存在

脚本功能

马上2024年了，跨年前必不可少的工作就是要检查一下明年的分区是不是存在。

或者现在就想检查一下下个月的分区是不是存在，也是没问题的。

检查分区表下个月分区是否存在

1）首先要确定分区范围

pg中没有单独的视图记录分区范围可以用如下方法查看分区定义，再截取分区起始和结束值

test_db=# select  relname,pg_get_expr(c.relpartbound,c.oid) from pg_class c where relname='test_check_part_202310';
        relname         |                            pg_get_expr
------------------------+--------------------------------------------------------------------
 test_check_part_202310 | FOR VALUES FROM ('2023-10-01 00:00:00') TO ('2023-11-01 00:00:00')

mogdb/opengauss中可以直接在pg_partition表中查到分区的上边界

MogDB=#select relname,boundaries from pg_partition where relname='p12';
 relname |       boundaries        
---------+-------------------------
 p12     | {"2024-01-01 00:00:00"}

2）取出范围边界的时间中的最大值判断端时间是不是大于下个月1号或者明年1号

脚本使用示例

pg查询示例

test_check_part表最大分区时间是”2023-12-01 00:00:00“，没有明年的分区

test_db=# select nspname,relname,max(part_range) max_part_range,case when max(part_range)>date_trunc('year',now()+interval'1y') then 't' else 'f' end  has_next_year_part
from (select nsp.nspname,c.relname,(regexp_matches(pg_get_expr(pc.relpartbound,pc.oid),'\((''[0-9]{4}-[0-9]{2}-[0-9]{2}.*?'')\)','g'))[1]::timestamp part_range
  from pg_class c, pg_partitioned_table p , pg_partition_tree(c.oid) t ,pg_class pc,pg_namespace nsp
  where c.relkind = 'p' and c.relispartition = 'f' and c.oid=p.partrelid and p.partstrat='r' and t.level=1 and t.relid=pc.oid and c.relnamespace=nsp.oid) r
group by nspname,relname  ;
 nspname |      relname      |   max_part_range    | has_next_year_part
---------+-------------------+---------------------+--------------------
 public  | test_check_part_v | 2024-03-01 00:00:00 | t
 public  | test_check_part   | 2023-12-01 00:00:00 | f
(2 rows)

test_db=# \d+ test_check_part
                                  Partitioned table "public.test_check_part"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 c_date | timestamp without time zone |           |          |         | plain    |              |
 name   | character varying           |           |          |         | extended |              |
 id     | integer                     |           |          |         | plain    |              |
Partition key: RANGE (c_date)
Partitions: test_check_part_202310 FOR VALUES FROM ('2023-10-01 00:00:00') TO ('2023-11-01 00:00:00'),
            test_check_part_202311 FOR VALUES FROM ('2023-11-01 00:00:00') TO ('2023-12-01 00:00:00')

test_db=# \d+ test_check_part_v
                            Partitioned table "public.test_check_part_v"
 Column |       Type        | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
 c_date | character varying |           |          |         | extended |              |
 name   | character varying |           |          |         | extended |              |
 id     | integer           |           |          |         | plain    |              |
Partition key: RANGE (c_date)
Partitions: test_check_part_v_202401 FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'),
            test_check_part_v_202402 FOR VALUES FROM ('2024-02-01') TO ('2024-03-01')

test_db=#

mogdb查询示例

test_part2表删除三个月分区后，查询结果变成了f，没有下个月的分区

MogDB=#select t.nspname,t.relname,t.max_part,t.max_part_time,case when t.max_part_time>date_trunc('month',now()+interval'1month') then 't' else 'f' end  has_next_month_part 
#from 
#(select nsp.nspname,c.relname,max(boundaries[1]) max_part,max(case when boundaries[1] ~ '([0-9]{4}-[0-9]{2}-[0-9]{2}.*?)' then boundaries[1]::timestamp 
#when boundaries[1] ~ '(^[0-9]+$)' and boundaries[1]>946684800::bigint and boundaries[1]<4102444800::bigint then to_timestamp(boundaries[1]::bigint) end)  max_part_time
#from pg_partition p , pg_class c ,pg_namespace nsp where p.parentid=c.oid and c.relnamespace=nsp.oid group by nsp.nspname,c.relname) t 
#where t.max_part_time is not null;
 nspname |  relname   |      max_part       |     max_part_time      | has_next_month_part 
---------+------------+---------------------+------------------------+---------------------
 public  | test_part  | 2024-01-01 00:00:00 | 2024-01-01 00:00:00+08 | t
 public  | test_part2 | 2024-01-01 00:00:00 | 2024-01-01 00:00:00+08 | t
 public  | test_part4 | 1704038400          | 2024-01-01 00:00:00+08 | t
(3 rows)

MogDB=#alter table test_part2 drop partition p12;
ALTER TABLE                                   
MogDB=#alter table test_part2 drop partition p11;
ALTER TABLE
MogDB=#alter table test_part2 drop partition p10;
ALTER TABLE
MogDB=#select t.nspname,t.relname,t.max_part,t.max_part_time,case when t.max_part_time>date_trunc('month',now()+interval'1month') then 't' else 'f' end  has_next_month_part 
from 
(select nsp.nspname,c.relname,max(boundaries[1]) max_part,max(case when boundaries[1] ~ '([0-9]{4}-[0-9]{2}-[0-9]{2}.*?)' then boundaries[1]::timestamp 
when boundaries[1] ~ '(^[0-9]+$)' and boundaries[1]>946684800::bigint and boundaries[1]<4102444800::bigint then to_timestamp(boundaries[1]::bigint) end)  max_part_time
from pg_partition p , pg_class c ,pg_namespace nsp where p.parentid=c.oid and c.relnamespace=nsp.oid group by nsp.nspname,c.relname) t 
where t.max_part_time is not null;
 nspname |  relname   |      max_part       |     max_part_time      | has_next_month_part 
---------+------------+---------------------+------------------------+---------------------
 public  | test_part  | 2024-01-01 00:00:00 | 2024-01-01 00:00:00+08 | t
 public  | test_part2 | 2023-10-01 00:00:00 | 2023-10-01 00:00:00+08 | f
 public  | test_part4 | 1704038400          | 2024-01-01 00:00:00+08 | t
(3 rows)

脚本如下

pg中查询SQL

注：本例中使用正则表达式获取分区边界，只判断了范围分区且按时间分区的分区表，如要检查下个月分区只需要把date_trunc(‘year’,now()+interval’1y’)改成date_trunc(‘month’,now()+interval’1month’)即可

select relname,max(part_range) max_part_range,case when max(part_range)>date_trunc('year',now()+interval'1y') then 't' else 'f' end  has_next_year_part
from (select c.relname,(regexp_matches(pg_get_expr(pc.relpartbound,pc.oid),'\((''[0-9]{4}-[0-9]{2}-[0-9]{2}.*?'')\)','g'))[1]::timestamp part_range
  from pg_class c, pg_partitioned_table p , pg_partition_tree(c.oid) t ,pg_class pc
  where c.relkind = 'p' and c.relispartition = 'f' and c.oid=p.partrelid and p.partstrat='r' and t.level=1 and t.relid=pc.oid ) r 
group by relname  ;

mogdb中查询SQL

注：本例中分两种情况判断分区是否存在，一个是分区范围用时间格式表示的，另一个是分区范围用epoch（自1970年1月1日00:00:00 UTC以来的秒数）格式表示的，且把epoch限制在946684800~4102444800之间（2000年-2100年）

select t.nspname,t.relname,t.max_part,t.max_part_time,case when t.max_part_time>date_trunc('month',now()+interval'1month') then 't' else 'f' end  has_next_month_part 
from 
(select nsp.nspname,c.relname,max(boundaries[1]) max_part,max(case when boundaries[1] ~ '([0-9]{4}-[0-9]{2}-[0-9]{2}.*?)' then boundaries[1]::timestamp 
when boundaries[1] ~ '(^[0-9]+$)' and boundaries[1]>946684800::bigint and boundaries[1]<4102444800::bigint then to_timestamp(boundaries[1]::bigint) end)  max_part_time
from pg_partition p , pg_class c ,pg_namespace nsp where p.parentid=c.oid and c.relnamespace=nsp.oid group by nsp.nspname,c.relname) t 
where t.max_part_time is not null