您好,登錄后才能下訂單哦!
這篇文章主要介紹“PostgreSQL12的pg_partition_tree和pg_partition_root系統(tǒng)函數(shù)有什么作用”,在日常操作中,相信很多人在PostgreSQL12的pg_partition_tree和pg_partition_root系統(tǒng)函數(shù)有什么作用問題上存在疑惑,小編查閱了各式資料,整理出簡單好用的操作方法,希望對大家解答”PostgreSQL12的pg_partition_tree和pg_partition_root系統(tǒng)函數(shù)有什么作用”的疑惑有所幫助!接下來,請跟著小編一起來學(xué)習(xí)吧!
在PG 12以前的版本,獲取分區(qū)表中的分區(qū)以及子分區(qū)等信息需要使用遞歸CTE查詢腳本來獲取,不直觀而且麻煩,在PG 12中新增了pg_partition_tree和pg_partition_root系統(tǒng)函數(shù)分別用于獲取分區(qū)樹和分區(qū)的root relation.
下面以一個(gè)簡單的例子進(jìn)行說明.
測試腳本
-- Hash Partition drop table if exists t_hash2; create table t_hash2 (c1 int not null,c2 varchar(40),c3 varchar(40)) partition by hash(c1); -- Level 1 create table t_hash2_1 partition of t_hash2 for values with (modulus 6,remainder 0) partition by hash(c1); create table t_hash2_2 partition of t_hash2 for values with (modulus 6,remainder 1) partition by hash(c1); create table t_hash2_3 partition of t_hash2 for values with (modulus 6,remainder 2); create table t_hash2_4 partition of t_hash2 for values with (modulus 6,remainder 3); create table t_hash2_5 partition of t_hash2 for values with (modulus 6,remainder 4); create table t_hash2_6 partition of t_hash2 for values with (modulus 6,remainder 5); -- Level 2 create table t_hash2_1_1 partition of t_hash2_1 for values with (modulus 2,remainder 0); create table t_hash2_1_2 partition of t_hash2_1 for values with (modulus 2,remainder 1); create table t_hash2_2_1 partition of t_hash2_2 for values with (modulus 2,remainder 0); create table t_hash2_2_2 partition of t_hash2_2 for values with (modulus 2,remainder 1);
t_hash2是一張Hash分區(qū)表,有6個(gè)子分區(qū),其中子分區(qū)中的t_hash2_1和t_hash2_2也是分區(qū)表,分別有2個(gè)分區(qū).
在PG 11中,需要使用CTE遞歸查詢來查詢該分區(qū)的相關(guān)信息:
-- PG11 WITH RECURSIVE partition_info (relid, -- oid relname, -- 名稱 relsize, -- 大小 relispartition, -- 是否分區(qū)表 relkind) AS ( SELECT oid AS relid, relname, pg_relation_size(oid) AS relsize, relispartition, relkind FROM pg_catalog.pg_class WHERE relname = 't_hash2' AND -- 最頂層的分區(qū)表 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 -- 從最頂層的分區(qū)表(即t_hash2)開始遞歸 c.oid = i.inhrelid AND -- 尋找子分區(qū) c.relispartition -- 分區(qū)表標(biāo)記 ) SELECT * FROM partition_info; relid | relname | relsize | relispartition | relkind -------+-------------+---------+----------------+--------- 57457 | t_hash2 | 0 | f | p 57466 | t_hash2_3 | 0 | t | r 57469 | t_hash2_4 | 0 | t | r 57472 | t_hash2_5 | 0 | t | r 57475 | t_hash2_6 | 0 | t | r 57460 | t_hash2_1 | 0 | t | p 57463 | t_hash2_2 | 0 | t | p 57487 | t_hash2_2_2 | 0 | t | r 57478 | t_hash2_1_1 | 0 | t | r 57481 | t_hash2_1_2 | 0 | t | r 57484 | t_hash2_2_1 | 0 | t | r (11 rows)
而在PG 12中,則可以直接使用系統(tǒng)函數(shù)獲取相關(guān)信息:
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_hash2'); pg_partition_tree ----------------------------- (t_hash2,,f,0) (t_hash2_1,t_hash2,f,1) (t_hash2_2,t_hash2,f,1) (t_hash2_3,t_hash2,t,1) (t_hash2_4,t_hash2,t,1) (t_hash2_5,t_hash2,t,1) (t_hash2_6,t_hash2,t,1) (t_hash2_1_1,t_hash2_1,t,2) (t_hash2_1_2,t_hash2_1,t,2) (t_hash2_2_1,t_hash2_2,t,2) (t_hash2_2_2,t_hash2_2,t,2) (11 rows)
返回的信息包括:
relid -> 該分區(qū)的relid
parentrelid -> 父分區(qū)
isleaf —> 是否葉子節(jié)點(diǎn)
level —> 層次
通過pg_partition_root可以獲取分區(qū)表的root節(jié)點(diǎn)
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_hash2_2_2'); pg_partition_root ------------------- t_hash2 (1 row)
到此,關(guān)于“PostgreSQL12的pg_partition_tree和pg_partition_root系統(tǒng)函數(shù)有什么作用”的學(xué)習(xí)就結(jié)束了,希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí),快去試試吧!若想繼續(xù)學(xué)習(xí)更多相關(guān)知識,請繼續(xù)關(guān)注億速云網(wǎng)站,小編會繼續(xù)努力為大家?guī)砀鄬?shí)用的文章!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。