Re: partition tree inspection functions

Hi Amit,

On 07/19/2018 04:39 AM, Amit Langote wrote:
>> I think pg_partition_tree_tables should have an option to exclude the
>> table that is being queried from the result (bool include_self).
>
> Doesn't sound too bad, so added include_self.
>

I'm thinking about how to best use these functions to generate a graph
that represents the partition hierarchy.

What about renaming pg_partition_tree_tables() to
pg_partition_children(), and have it work like

select * from pg_partition_children('p', true);
---------
p
p0
p1
p00
p01
p10
p11
(7 rows)

select * from pg_partition_children('p', false);
---------
p0
p1
(2 rows)

e.g. if 'bool include_all' is true all nodes under the node, including
itself, are fetched. With false only nodes directly under the node,
excluding itself, are returned. If there are no children NULL is returned.

>> Maybe a function like pg_partition_number_of_partitions() could be of
>> benefit to count the number of actual partitions in a tree. Especially
>> useful in complex scenarios,
>>
>>  select pg_partition_number_of_partitions('p') as number;
>>
>>    number
>>  ---------
>>   4
>>  (1 row)
>
> Okay, adding one more function at this point may not be asking for too
> much. Although, select count(*) from pg_partition_tree_tables('p') would
> give you the count, a special function seems nice.
>

Yeah, but I was thinking that the function would only return the number
of actual tables that contains data, e.g. not include 'p', 'p0' and 'p1'
in the count; otherwise you could use 'select count(*) from
pg_partition_children('p', true)' like you said.

Thanks for considering.

Best regards,
Jesper