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