我正在寻找一个列出所有范围分区信息的解决方案。已尝试以下查询。
SELECT c.relname as partition_list,p.relname as parent_tbl FROM pg_inherits i JOIN pg_class p ON i.inhparent = p.oid
JOIN pg_class c ON i.inhrelid = c.oid WHERE p.relkind IN ('r', 'p');
输出
"testpartpartition_1" "parentpartiontbl"
"testpartpartition_2" "parentpartiontbl"
但由于我创建了一个范围分区,我想知道范围值,例如:
CREATE TABLE testpartpartition_1 PARTITION OF parentpartiontbl FOR VALUES FROM (1) TO (5)
CREATE TABLE testpartpartition_2 PARTITION OF parentpartiontbl FOR VALUES FROM (6) TO (10)
还需要每个分区的状态为startvalue
和endvalue
的输出,如下面的
child_partition parent_tbl min_rangeval max_rangeval
---------------------------------------------------------------------------------
"testpartpartition_1" "parentpartiontbl" 1 5
"testpartpartition_2" "parentpartiontbl" 6 10
由于分区边界是以二进制解析的形式存储的,所以您所能做的就是对它们进行分解:
SELECT c.oid::regclass AS child_partition,
p.oid::regclass AS parent_tbl,
pg_get_expr(c.relpartbound, c.oid) AS boundaries
FROM pg_class AS p
JOIN pg_inherits AS i ON p.oid = i.inhparent
JOIN pg_class AS c ON i.inhrelid = c.oid
WHERE p.relkind = 'p';
child_partition │ parent_tbl │ boundaries
═════════════════╪════════════╪══════════════════════════════════════════════════════════════════════════
part_2022 │ part │ FOR VALUES FROM ('2022-01-01 00:00:00+01') TO ('2023-01-01 00:00:00+01')
(1 row)
分析边界字符串留给读者练习。
您可以在系统目录pg_class
的relpartbound
列中找到信息。使用函数pg_get_expr()
获得可读数据:
select
relname as partition_table,
pg_get_expr(relpartbound, oid) as partition_range
from pg_class
where relispartition
and relkind = 'r';
partition_table | partition_range
---------------------+-----------------------------
testpartpartition_1 | FOR VALUES FROM (1) TO (5)
testpartpartition_2 | FOR VALUES FROM (6) TO (10)
(2 rows)
使用regexp_matches()
提取括号中的数字
select
relname as partition_table,
matches[1] as min_rangeval,
matches[2] as max_rangeval
from pg_class
cross join regexp_matches(pg_get_expr(relpartbound, oid), '((.+?)).+((.+?))') as matches
where relispartition
and relkind = 'r';
partition_table | min_rangeval | max_rangeval
---------------------+--------------+--------------
testpartpartition_1 | 1 | 5
testpartpartition_2 | 6 | 10
(2 rows)
以下是d+
:生成的查询
edb=# SELECT c.oid::pg_catalog.regclass, c.relkind, false AS inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid = i.inhrelid AND i.inhparent = '33245'
ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
oid | relkind | inhdetachpending | pg_get_expr
---------------------+---------+------------------+-----------------------------
testpartpartition_1 | r | f | FOR VALUES FROM (1) TO (5)
testpartpartition_2 | r | f | FOR VALUES FROM (6) TO (10)
(2 rows)
看起来您需要使用pg_get_expr()
来解码pg_class.relpartbound
中的内容,以重建范围分区参数。
您还可以将i.inhparent = '33245'
替换为子查询,以按父表名称进行查询:
edb=# SELECT c.oid::pg_catalog.regclass, c.relkind, false AS inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid = i.inhrelid AND i.inhparent = (SELECT oid from pg_class where relname = 'parentpartitiontbl')
ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;