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