Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the function of PostgreSQL12's pg_partition_tree and pg_partition_root system functions". In daily operation, it is believed that many people have doubts about the function of PostgreSQL12's pg_partition_tree and pg_partition_root system functions. The editor consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful for you to answer the question of "what is the function of PostgreSQL12's pg_partition_tree and pg_partition_root system functions?" Next, please follow the editor to study!

In the previous version of PG 12, the partition and sub-partition information in the partition table needed to be obtained by using recursive CTE query script, which was not intuitive and troublesome. Pg_partition_tree and pg_partition_root system functions were added in PG 12 to get the partition tree and partition root relation respectively.

The following is illustrated by a simple example.

Test script

-- Hash Partitiondrop table if exists tincture hash2: create table t_hash2 (C1 int not null,c2 varchar (40), c3 varchar (40)) partition by hash (C1);-- Level 1create table t_hash2_1 partition of t_hash2 for values with (modulus 6) partition by hash (C1); create table t_hash2_2 partition of t_hash2 for values with (modulus 6) partition by hash (C1); create table t_hash2_3 partition of t_hash2 for values with (modulus 6) Create table t_hash2_4 partition of t_hash2 for values with (modulus 6); create table t_hash2_5 partition of t_hash2 for values with (modulus 6); create table t_hash2_6 partition of t_hash2 for values with (modulus 6);-- Level 2create table t_hash2_1_1 partition of t_hash2_1 for values with (modulus 2); create table t_hash2_1_2 partition of t_hash2_1 for values with (modulus 2) Create table t_hash2_2_1 partition of t_hash2_2 for values with (modulus 2); create table t_hash2_2_2 partition of t_hash2_2 for values with (modulus 2)

T_hash2 is a Hash partition table with 6 sub-partitions. T_hash2_1 and t_hash2_2 in its sub-partition are also partition tables with 2 partitions respectively.

In PG 11, you need to use a CTE recursive query to query information about the partition:

-- PG11WITH RECURSIVE partition_info (relid,-- oid relname,-- name relsize,-- size relispartition,-- whether to partition the table relkind) AS (SELECT oid AS relid, relname, pg_relation_size (oid) AS relsize, relispartition Relkind FROM pg_catalog.pg_classWHERE relname = 'tweehash2' AND-the topmost partition table 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-- Recursive c.oid = i.inhrelid AND from the topmost partition table (that is, t_hash2)-- looking for sub-partition c.relispartition-- partition table tag) SELECT * FROM partition_info Relid | relname | relsize | relispartition | relkind-+-57457 | t_hash2 | 0 | f | p 57466 | t_hash2_3 | 0 | t | r 57469 | tweehash2 _ | 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)

In PG 12, you can use system functions directly to get the relevant information:

Testdb=#\ sf pg_partition_treeCREATE 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 STRICTAS $function$pg_partition_tree$function$testdb=# select pg_partition_tree ('tasking hash2') Pg_partition_tree-- (tweak hash2) (tactile hash2) (tincture hash2) (tactile hash2) (tactile 1) (tonal hash2) (tincture hash2) (tactile hash2) (tincture hash2)

The information returned includes:

Relid-> relid of the partition

Parentrelid-> parent partition

Whether isleaf-> is a leaf node

Level-> hierarchy

The root node of the partition table can be obtained through pg_partition_root

Testdb=#\ sf pg_partition_rootCREATE OR REPLACE FUNCTION pg_catalog.pg_partition_root (regclass) RETURNS regclass LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICTAS $function$pg_partition_root$function$testdb=# select pg_partition_root ('tweak hash2') Pg_partition_root-t_hash2 (1 row) at this point, the study on "what is the role of PostgreSQL12's pg_partition_tree and pg_partition_root system functions" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.