pg 10由于没有hash分区，而pg_pathman一直都是支持多种分区的。所以如果某些pg 11以前的系统，可能会混合部署pg原生分区和pg_pathman。
要检查这种混合部署环境中的分区情况，可以用下面的sql：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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 |
显示结果如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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，并且还可以看到分区的类型，和分区数量。