ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(39) - PG 12 Functions for partitions

PostgreSQL DBA(39) - PG 12 Functions for partitions

翻译 PostgreSQL 作者:husthxd 时间:2019-06-19 15:00:36 0 删除 编辑

在PG 12以前的版本,获取分区表中的分区以及子分区等信息需要使用递归CTE查询脚本来获取,不直观而且麻烦,在PG 12中新增了pg_partition_tree和pg_partition_root系统函数分别用于获取分区树和分区的root relation.

下面以一个简单的例子进行说明.

测试脚本


-- Hash Partition
drop table if exists t_hash1;
create table t_hash1 (c1 int not null,c2  varchar(40),c3 varchar(40)) partition by hash(c1);
-- Level 1
create table t_hash1_1 partition of t_hash1 for values with (modulus 6,remainder 0) partition by hash(c1);
create table t_hash1_2 partition of t_hash1 for values with (modulus 6,remainder 1) partition by hash(c1);
create table t_hash1_3 partition of t_hash1 for values with (modulus 6,remainder 2);
create table t_hash1_4 partition of t_hash1 for values with (modulus 6,remainder 3);
create table t_hash1_5 partition of t_hash1 for values with (modulus 6,remainder 4);
create table t_hash1_6 partition of t_hash1 for values with (modulus 6,remainder 5);
-- Level 2
create table t_hash1_1_1 partition of t_hash1_1 for values with (modulus 2,remainder 0);
create table t_hash1_1_2 partition of t_hash1_1 for values with (modulus 2,remainder 1);
create table t_hash1_2_1 partition of t_hash1_2 for values with (modulus 2,remainder 0);
create table t_hash1_2_2 partition of t_hash1_2 for values with (modulus 2,remainder 1);

t_hash1是一张Hash分区表,有6个子分区,其中子分区中的t_hash1_1和t_hash1_2也是分区表,分别有2个分区.

在PG 11中,需要使用CTE递归查询来查询该分区的相关信息:


-- PG11
WITH RECURSIVE partition_info
      (relid,             -- oid
       relname,            -- 名称
       relsize,            -- 大小
       relispartition,     -- 是否分区表
       relkind) AS (
    SELECT oid AS relid,
           relname,
           pg_relation_size(oid) AS relsize,
           relispartition,
           relkind
    FROM pg_catalog.pg_class
WHERE relname = 't_hash1' AND -- 最顶层的分区表
      relkind = 'p' 
  UNION ALL
    SELECT
         c.oid AS relid,
         c.relname AS relname,
         pg_relation_size(c.oid) AS relsize,
         c.relispartition AS relispartition,
         c.relkind AS relkind
    FROM partition_info AS p,
         pg_catalog.pg_inherits AS i,
         pg_catalog.pg_class AS c
    WHERE p.relid = i.inhparent AND -- 从最顶层的分区表(即t_hash1)开始递归
         c.oid = i.inhrelid AND -- 寻找子分区
         c.relispartition -- 分区表标记
  )
SELECT * FROM partition_info;
 relid |   relname   | relsize | relispartition | relkind 
-------+-------------+---------+----------------+---------
 57457 | t_hash1     |       0 | f              | p
 57466 | t_hash1_3   |       0 | t              | r
 57469 | t_hash1_4   |       0 | t              | r
 57472 | t_hash1_5   |       0 | t              | r
 57475 | t_hash1_6   |       0 | t              | r
 57460 | t_hash1_1   |       0 | t              | p
 57463 | t_hash1_2   |       0 | t              | p
 57487 | t_hash1_2_2 |       0 | t              | r
 57478 | t_hash1_1_1 |       0 | t              | r
 57481 | t_hash1_1_2 |       0 | t              | r
 57484 | t_hash1_2_1 |       0 | t              | r
(11 rows)

而在PG 12中,则可以直接使用系统函数获取相关信息:


testdb=# \sf pg_partition_tree
CREATE OR REPLACE FUNCTION pg_catalog.pg_partition_tree(rootrelid regclass, OUT relid regclass, OUT parentrelid regclass, OUT isleaf boolean, OUT level integer)
 RETURNS SETOF record
 LANGUAGE internal
 PARALLEL SAFE STRICT
AS $function$pg_partition_tree$function$
testdb=# select pg_partition_tree('t_hash1');
      pg_partition_tree      
-----------------------------
 (t_hash1,,f,0)
 (t_hash1_1,t_hash1,f,1)
 (t_hash1_2,t_hash1,f,1)
 (t_hash1_3,t_hash1,t,1)
 (t_hash1_4,t_hash1,t,1)
 (t_hash1_5,t_hash1,t,1)
 (t_hash1_6,t_hash1,t,1)
 (t_hash1_1_1,t_hash1_1,t,2)
 (t_hash1_1_2,t_hash1_1,t,2)
 (t_hash1_2_1,t_hash1_2,t,2)
 (t_hash1_2_2,t_hash1_2,t,2)
(11 rows)

返回的信息包括:
relid -> 该分区的relid
parentrelid -> 父分区
isleaf —> 是否叶子节点
level —> 层次

通过pg_partition_root可以获取分区表的root节点


testdb=# \sf pg_partition_root
CREATE OR REPLACE FUNCTION pg_catalog.pg_partition_root(regclass)
 RETURNS regclass
 LANGUAGE internal
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$pg_partition_root$function$
testdb=# select pg_partition_root('t_hash1_2_2');
 pg_partition_root 
-------------------
 t_hash1
(1 row)

参考资料
Postgres 12 highlight - Functions for partitions

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2648092/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
长期从事政务、金融等行业产品研发和架构设计工作,ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1308
  • 访问量
    3785299