Re: partition tree inspection functions

On 2018/06/26 14:08, Amit Langote wrote:
> Hi.
> 
> As discussed a little while back [1] and also recently mentioned [2], here
> is a patch that adds a set of functions to inspect the details of a
> partition tree.  There are three functions:
> 
> pg_partition_parent(regclass) returns regclass
> pg_partition_root_parent(regclass) returns regclass
> pg_partition_tree_tables(regclass) returns setof regclass
> 
> Here is an example showing how one may want to use them.
> 
> create table p (a int, b int) partition by range (a);
> create table p0 partition of p for values from (minvalue) to (0) partition
> by hash (b);
> create table p00 partition of p0 for values with (modulus 2, remainder 0);
> create table p01 partition of p0 for values with (modulus 2, remainder 1);
> create table p1 partition of p for values from (0) to (maxvalue) partition
> by hash (b);
> create table p10 partition of p1 for values with (modulus 2, remainder 0);
> create table p11 partition of p1 for values with (modulus 2, remainder 1);
> insert into p select i, i from generate_series(-5, 5) i;
> 
> select pg_partition_parent('p0') as parent;
>  parent
> --------
>  p
> (1 row)
> 
> Time: 1.469 ms
> select pg_partition_parent('p01') as parent;
>  parent
> --------
>  p0
> (1 row)
> 
> Time: 1.330 ms
> select pg_partition_root_parent('p01') as root_parent;
>  root_parent
> -------------
>  p
> (1 row)
> 
> select    p as relname,
>           pg_partition_parent(p) as parent,
>           pg_partition_root_parent(p) as root_parent
> from      pg_partition_tree_tables('p') p;
>  relname | parent | root_parent
> ---------+--------+-------------
>  p       |        | p
>  p0      | p      | p
>  p1      | p      | p
>  p00     | p0     | p
>  p01     | p0     | p
>  p10     | p1     | p
>  p11     | p1     | p
> (7 rows)
> 
> select    p as relname,
>           pg_partition_parent(p) as parent,
>           pg_partition_root_parent(p) as root_parent,
>           pg_relation_size(p) as size
> from      pg_partition_tree_tables('p') p;
>  relname | parent | root_parent | size
> ---------+--------+-------------+------
>  p       |        | p           |    0
>  p0      | p      | p           |    0
>  p1      | p      | p           |    0
>  p00     | p0     | p           | 8192
>  p01     | p0     | p           | 8192
>  p10     | p1     | p           | 8192
>  p11     | p1     | p           | 8192
> (7 rows)
> 
> 
> select    sum(pg_relation_size(p)) as total_size
> from      pg_partition_tree_tables('p') p;
>  total_size
> -------------
>        32768
> (1 row)
> 
> Feedback is welcome!

Added this to July CF.

Thanks,
Amit