Subject: Re: partition tree inspection functions
References: <8d00e51a-9a51-ad02-d53e-ba6bf50b2e52@lab.ntt.co.jp>
 <5a560c9c-3722-1914-f34b-1de0ff3310d1@lab.ntt.co.jp>
 <CAOgcT0P8FC4Sat1ytVZgm09qd2bgMv-7pWpvU1HdBMKSah9r-w@mail.gmail.com>
 <d5f5b9a8-0c88-8043-2007-be37096bed39@lab.ntt.co.jp>
 <20180627121615.GF2667@paquier.xyz>
 <45e28062-976d-45bf-56f8-9746f551da02@lab.ntt.co.jp>
 <20180628034341.GH11054@paquier.xyz>
 <6baeb45a-6222-6b88-342d-37fc7d3cf89a@lab.ntt.co.jp>
 <4f4a2518-6c62-5463-6ac2-1241ae9d6041@redhat.com>
 <e12ff710-dcf5-18ff-b4a4-cc42432d84c4@lab.ntt.co.jp>
 <38183355-ea7c-9443-0c38-6124fcf5f38b@redhat.com>
From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Message-ID: <5686970f-b4e8-bbe5-9109-b97763714c19@lab.ntt.co.jp>
Date: Fri, 20 Jul 2018 11:27:11 +0900
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101
 Thunderbird/52.9.0
MIME-Version: 1.0
In-Reply-To: <38183355-ea7c-9443-0c38-6124fcf5f38b@redhat.com>
Content-Type: text/plain; charset=windows-1252
Content-Language: en-US
Content-Transfer-Encoding: 8bit
To: jesper.pedersen@redhat.com
Cc: Michael Paquier <michael@paquier.xyz>,
        Jeevan Ladhe <jeevan.ladhe@enterprisedb.com>,
        Pg Hackers <pgsql-hackers@postgresql.org>
Precedence: bulk

Hi Jesper,

On 2018/07/19 23:18, Jesper Pedersen wrote:
> 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.

That's a big change to make to what this function does, but if that's
what's useful we could make it.  As an alternative, wouldn't it help to
implement the idea that Dilip mentioned upthread of providing a function
to report the level of a given table in the partition hierarchy -- 0 for
root, 1 for its partitions and so on?

Basically, as also discussed before, users can already use SQL to get the
information they want out of the relevant catalogs (pg_inherits, etc.).
But, such user queries might not be very future-proof as we might want to
change the catalog organization in the future, so we'd like to provide
users a proper interface to begin with.  Keeping that in mind, it'd be
better to think carefully about what we ought to be doing here.  Input
like yours is greatly helpful for that.

>>> 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.

Maybe call it pg_partition_tree_leaf_count() or some such then?

Thanks,
Amit