Reply-To: jesper.pedersen@redhat.com
Subject: Re: partition tree inspection functions
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Michael Paquier <michael@paquier.xyz>,
 Jeevan Ladhe <jeevan.ladhe@enterprisedb.com>,
 Pg Hackers <pgsql-hackers@postgresql.org>
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>
 <5686970f-b4e8-bbe5-9109-b97763714c19@lab.ntt.co.jp>
From: Jesper Pedersen <jesper.pedersen@redhat.com>
Organization: Red Hat
Message-ID: <da9f8b6d-13bb-18fe-7777-b3be7bdd327b@redhat.com>
Date: Fri, 20 Jul 2018 08:26:03 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <5686970f-b4e8-bbe5-9109-b97763714c19@lab.ntt.co.jp>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

Hi Amit,

On 07/19/2018 10:27 PM, Amit Langote wrote:
> 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?
>

Yes, Dilip's idea could work. I just don't think that 
pg_partition_tree_tables() as is would have a benefit over time.

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

We could have the patch include pg_partition_root_parent and 
pg_partition_parent, and leave the rest for a future CommitFest such 
that more people could provide feedback on what they would like to see 
in this space.

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

That could work.

Best regards,
  Jesper