检查PostgreSQL中所有分区表

pg 10由于没有hash分区，而pg_pathman一直都是支持多种分区的。所以如果某些pg 11以前的系统，可能会混合部署pg原生分区和pg_pathman。
要检查这种混合部署环境中的分区情况，可以用下面的sql：

select b.parent::text as part_table,
     'native partition' as part_tool,
CASE WHEN a.partstrat='r' THEN 'range'
     WHEN a.partstrat='l' THEN 'list'
     WHEN a.partstrat='h' THEN 'hash'
     ELSE 'other'
     END as part_type,
     b.cnt as part_cnt
from
pg_partitioned_table a,
(SELECT
    parent.oid,
    parent.relname          AS parent,
    COUNT(*) as cnt
FROM pg_inherits
    JOIN pg_class parent                 ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child                  ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent        ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child         ON nmsp_child.oid   = child.relnamespace
GROUP BY  oid,parent) b
where a.partrelid=b.oid
union all
select
     parent::text,
     'pg_pathman',
CASE WHEN parttype=1 THEN 'hash'
     WHEN parttype=2 THEN 'range'
     ELSE 'other'
END ,
count(*)
from pathman_partition_list
group by parent,parttype

显示结果如下：

dyats=> select version();
                                                 version                                                
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)
 
      part_table       |    part_tool     | part_type | part_cnt
-----------------------+------------------+-----------+----------
 mytabaa1_kkj_dbddak   | native partition | range     |        7
 kkj_dbddak            | native partition | range     |       59
 drop_sgs_djaygsrf_bak | native partition | range     |        9
 sgs_djaygsrf          | pg_pathman       | hash      |     1024
 mumybaad_sgs_djaygsrf | pg_pathman       | hash      |      128
(5 rows)
 
dyats=>
dyats=>
dyats=>

dyats=>

可以看到有5个partition table，其中3个是native partition，2个是用来pg_pathman，并且还可以看到分区的类型，和分区数量。

https://oracleblog.org/working-case/how-to-check-all-partition-in-pg/