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>
From: Jesper Pedersen <jesper.pedersen@redhat.com>
Organization: Red Hat
Message-ID: <38183355-ea7c-9443-0c38-6124fcf5f38b@redhat.com>
Date: Thu, 19 Jul 2018 10:18:51 -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: <e12ff710-dcf5-18ff-b4a4-cc42432d84c4@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 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