ANBOB™

范围分区是大型关系数据库中常用的Table类型， 如按时间维度划分方便后期的周期清理，在Oracle中range分区只需指定分区的上限(less than)，而在PostgreSQL中的分区比较另类，需要指定分区的上限和下限区间（>= and <），而这种设计就有可能存在一个问题，相邻的两个分区之间有可能无映射分区，数据进入table时范围除了table级最小和最大时间，在PostgreSQL中还有可能因为条件不连续存在分区之间的GAP，如以天为分区，  分区界限是2023/03/01, 2023/03/02, 2023/03/04, 缺失 2023/03/03分区，在Oracle中数据会自动进入下一分区，而在PostgreSQL中则会报错未找到匹配分区（如果没有default分区）。

演示：

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

CREATE TABLE measurement_y2006m05 PARTITION OF measurement
    FOR VALUES FROM ('2006-05-01') TO ('2006-06-01');

anbob=# \d measurement
           分区表 "public.measurement"
   栏位    |  类型   | 校对规则 |  可空的  | 预设
-----------+---------+----------+----------+------
 city_id   | integer |          | not null |
 logdate   | date    |          | not null |
 peaktemp  | integer |          |          |
 unitsales | integer |          |          |
分区键值: RANGE (logdate)
分区的数量：3(可以使用 \d+ 来列出它们)


anbob=# \d+ measurement
                        分区表 "public.measurement"
   栏位    |  类型   | 校对规则 |  可空的  | 预设 | 存储  | 统计目标 | 描述
-----------+---------+----------+----------+------+-------+----------+------
 city_id   | integer |          | not null |      | plain |          |
 logdate   | date    |          | not null |      | plain |          |
 peaktemp  | integer |          |          |      | plain |          |
 unitsales | integer |          |          |      | plain |          |
分区键值: RANGE (logdate)
分区: measurement_y2006m02 FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'),
      measurement_y2006m03 FOR VALUES FROM ('2006-03-01') TO ('2006-04-01'),
      measurement_y2006m05 FOR VALUES FROM ('2006-05-01') TO ('2006-06-01')

Note:
注意上面未创建4月分区。

anbob=# insert into measurement values(1,to_date('2006-03-01','yyyy-mm-dd'),1,1);
INSERT 0 1

anbob=# select * from measurement;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-01 |        1 |         1
(1 行记录)

anbob=# select tableoid, tableoid::regclass, t.* from measurement t;
 tableoid |       tableoid       | city_id |  logdate   | peaktemp | unitsales
----------+----------------------+---------+------------+----------+-----------
    65764 | measurement_y2006m03 |       1 | 2006-03-01 |        1 |         1
(1 行记录)

anbob=# select * from measurement_y2006m03;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2006-03-01 |        1 |         1
(1 行记录)

anbob=# insert into measurement values(1,to_date('2006-04-01','yyyy-mm-dd'),1,1);
错误:  没有为行找到关系"measurement"的分区
描述:  失败行的分区键包含(logdate) = (2006-04-01).

Note:
注意4月分的数据insert失败，因为分区未找到匹配。

anbob=# select * from pg_inherits;
 inhrelid | inhparent | inhseqno
----------+-----------+----------
    65761 |     65758 |        1
    65764 |     65758 |        1
    65767 |     65758 |        1
(3 行记录)

范围分区基于内部继承：单个分区链接到分区表，并在后台继承;
继承树存储在pg_inherits：
每个直接子表一个条目，分区边界以内部格式 ()存储在 pg_class.relpartbound 中pg_node_tree ；
系统目录信息函数 pg_get_expr（pg_node_tree、relation_oid） 可以：反编译表达式的内部形式

列出表分区的上限和下限

anbob=# SELECT  i.inhparent::regclass,
anbob-#       i.inhrelid::regclass
anbob-#      , partition_bound
anbob-#      , split_part(partition_bound, '''', 2) AS lower_bound
anbob-#      , split_part(partition_bound, '''', 4) AS upper_bound
anbob-# FROM   pg_inherits i
anbob-# JOIN   pg_class    c ON c.oid = i.inhrelid
anbob-#      , pg_get_expr(c.relpartbound, i.inhrelid) AS partition_bound
anbob-# WHERE  inhparent = 'measurement'::regclass;
  inhparent  |       inhrelid       |                 partition_bound                  | lower_bound | upper_bound
-------------+----------------------+--------------------------------------------------+-------------+-------------
 measurement | measurement_y2006m02 | FOR VALUES FROM ('2006-02-01') TO ('2006-03-01') | 2006-02-01  | 2006-03-01
 measurement | measurement_y2006m03 | FOR VALUES FROM ('2006-03-01') TO ('2006-04-01') | 2006-03-01  | 2006-04-01
 measurement | measurement_y2006m05 | FOR VALUES FROM ('2006-05-01') TO ('2006-06-01') | 2006-05-01  | 2006-06-01 ---gap

查找范围分区中间遗漏的分区GAP

anbob=# select v2.inhparent,
anbob-#        prev_upper_bound  gap_lower_bound,
anbob-#    lower_bound gap_upper_bound
anbob-# from (
anbob(#   select v1.*,
anbob(#   lag(upper_bound) OVER (PARTITION BY inhparent  ORDER BY lower_bound) as prev_upper_bound
anbob(#   from (
anbob(#       SELECT i.inhparent::regclass,
anbob(#            i.inhrelid::regclass
anbob(#            , partition_bound
anbob(#            , split_part(partition_bound, '''', 2) AS lower_bound
anbob(#            , split_part(partition_bound, '''', 4) AS upper_bound
anbob(#       FROM   pg_inherits i
anbob(#       JOIN   pg_class    c ON c.oid = i.inhrelid
anbob(#            , pg_get_expr(c.relpartbound, i.inhrelid) AS partition_bound
anbob(#       WHERE  inhparent = 'measurement'::regclass
anbob(#   ) v1
anbob(#   ORDER BY inhparent,lower_bound
anbob(# ) v2
anbob-# where   lower_bound > prev_upper_bound
anbob-# order by 1,2;
  inhparent  | gap_lower_bound | gap_upper_bound
-------------+-----------------+-----------------
 measurement | 2006-04-01      | 2006-05-01
(1 行记录)

Note:
这里仅包括第一级继承。如果想列出所有分区，您必须递归地浏览图形以覆盖subpartition 使用pg_inherits如

select pt.relname as partition_name,
       pg_get_expr(pt.relpartbound, pt.oid, true) as partition_expression
from pg_class base_tb 
  join pg_inherits i on i.inhparent = base_tb.oid 
  join pg_class pt on pt.oid = i.inhrelid

这基于声明式分区的几个实现细节，这是 Postgres 10 的新功能。

 
-- enjoy --

打赏

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