Re: partition tree inspection functions

Hi Dilip,

Sorry it took me a while to reply.

On 2018/06/29 14:30, Dilip Kumar wrote:
> On Tue, Jun 26, 2018 at 10:38 AM, Amit Langote wrote:
>> 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
>>
>>
>> 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)
>>
>
> Is it a good idea to provide a function or an option which can provide
> partitions detail in hierarchical order?
>
> i.e
> relname level
> p 0
> p0 1
> p00 2
> p01 2
> p1 1

Yeah, might be a good idea. We could have a function
pg_partition_tree_level(OID) which will return the level of the table
that's passed to it the way you wrote above, meaning 0 for the root
parent, 1 for the root's immediate partitions, 2 for their partitions, and
so on.

Thanks,
Amit