ANBOB™

opengauss系数据库insert失败因缺少匹配分区表，提示inserted partition key does not map to any table partition ，在oracle中一样存在该问题，建议月末或年末提前查询下个月的分区是否存在，匹配oracle的dba_tab_partitions,在postgresq 11后查询pg_partitioned_table,而在openGauss中查询pg_partition.

create table test_part(id int,orderno number, sal number, sdate date not null)
partition by range(sdate)
(
  partition p09 values less than(to_date('2023-10-1','yyyy-mm-dd')),
  partition p10 values less than(to_date('2023-11-1','yyyy-mm-dd')),
  partition p11 values less than(to_date('2023-12-1','yyyy-mm-dd')),
  partition p12 values less than(to_date('2024-01-1','yyyy-mm-dd'))
);

create table test_part2(id int,orderno number, sal number, sdate date not null)
partition by range(sdate)
(
  partition p09 values less than(to_date('2023-10-1','yyyy-mm-dd')),
  partition p10 values less than(to_date('2023-11-1','yyyy-mm-dd')),
  partition p11 values less than(to_date('2023-12-1','yyyy-mm-dd')),
  partition p12 values less than(to_date('2024-01-1','yyyy-mm-dd')),
  partition pmax values less than (maxvalue)
);

create table test_part3(id int,orderno number, sal number, sdate date not null)
partition by hash(id)
partitions 3
;


create table test_part4(id int,orderno number, sal number, sdate date not null,sn number not null)
partition by range(sn)
(
  partition p09 values less than( date_part('epoch', DATE_TRUNC('month', now()) + '0 month')),
  partition p10 values less than( date_part('epoch', DATE_TRUNC('month', now()) + '1 month')),
  partition p11 values less than( date_part('epoch', DATE_TRUNC('month', now()) + '2 month')),
  partition p12 values less than( date_part('epoch', DATE_TRUNC('month', now()) + '3 month'))
);

SELECT parentid::regclass,relname,partstrategy, boundaries,array_to_string (boundaries,',') FROM pg_partition p where parttype='p'

# range partition
SELECT parentid::regclass,max(boundaries) tt FROM pg_partition p where   parttype='p' and partstrategy='r'   group by parentid

date to epoch

select date_part('epoch', now())::numeric c1,date_part('epoch', now()) c2,to_char(date_part('epoch', now())) c3,round(date_part('epoch', now()),0) c4,extract(epoch from now()) c5;

epoch to date

select to_char(date_part('epoch', timestamp '2023-11-01'::timestamp without time zone)) tt;
tt
----------
1698796800

SELECT TIMESTAMP 'epoch' + 1698768000 * INTERVAL '1 second' tt;
tt
--------------------
2023-10-31 16:00:00.0

select to_timestamp(1698768000) c1,to_timestamp(1698768000)::timestamp without time zone c2;
c1                     c2
--------------------   --------------------
2023-11-01 00:00:00.0  2023-11-01 00:00:00.0

Tote:
当前是+8时区

select  DATE_TRUNC('month', now()), 
DATE_TRUNC('month', now()::timestamp with time zone),
DATE_TRUNC('month', now()) + '3 month', 
DATE_TRUNC('month', now()::timestamp with time zone) + '3 month', 
DATE_TRUNC('month', now()::timestamp without time zone) + '3 month', 
to_char(date_part('epoch', DATE_TRUNC('month', now()) + '3 month')),
extract(epoch from now()) ,
TIMESTAMP 'epoch' + 1704038400 * INTERVAL '1 second';

add partition

alter table test_part add partition p13 values less than (to_date('2024-02-1','yyyy-mm-dd'))

Note：
在opengauss中对于声明式分区，range partition增加分区语法和oracle一样.

同事写了一份查询预分区是否存在的方法, 因为opengauss目前没有postgresql中的pg_partitioned_table(pg v11+)，这里pg与og分别 ：

# postgresql

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   --  next year
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

# opengauss/MogDB

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  ---next month
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

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

打赏

微信扫一扫，打赏作者吧～