Re: partition tree inspection functions

On 2018/10/04 9:27, Michael Paquier wrote:
> On Wed, Oct 03, 2018 at 08:12:59AM -0400, Jesper Pedersen wrote:
>> Removing isleaf would require extra round trips to the server to get
>> that information. So, I think we should keep it.
>
> I don't really get your point about extra round trips with the server,
> and getting the same level of information is as simple as a join between
> the result set of pg_partition_tree() and pg_class (better to add schema
> qualification and aliases to relations by the way):
> =# SELECT relid::regclass,
> parentrelid::regclass, level,
> relkind != 'p' AS isleaf
> FROM pg_partition_tree('ptif_test'::regclass), pg_class
> WHERE oid = relid;
> relid | parentrelid | level | isleaf
> -------------+-------------+-------+--------
> ptif_test | null | 0 | f
> ptif_test0 | ptif_test | 1 | f
> ptif_test1 | ptif_test | 1 | f
> ptif_test2 | ptif_test | 1 | t
> ptif_test01 | ptif_test0 | 2 | t
> ptif_test11 | ptif_test1 | 2 | t
> (6 rows)

As mentioned in my other reply, that might be considered as asking the
user to know inner details like relkind. Also, if a database has many
partitioned tables with lots of partitions, the pg_class join might get
expensive. OTOH, computing and returning it with other fields of
pg_partition_tree is essentially free.

Thanks,
Amit