Re: partition tree inspection functions

Hi,

On 2018/08/03 21:35, Jesper Pedersen wrote:
> Hi Amit,
>
> On 08/03/2018 04:28 AM, Amit Langote wrote:
>> That's a good idea, thanks.
>>
>> Actually, by the time I sent the last version of the patch or maybe few
>> versions before that, I too had started thinking if we shouldn't just have
>> a SETOF RECORD function like you've outlined here, but wasn't sure of the
>> fields it should have.  (relid, parentid, level) seems like a good start,
>> or maybe that's just what we need.
>>
>
> I think there should be a column that identifies leaf partitions (bool
> isleaf), otherwise it isn't obvious in complex scenarios.

Ah, getting isleaf directly from pg_partition_children would be better
than an application figuring that out by itself.

>> Note that the level that's returned for each table is computed wrt the
>> root table passed to the function and not the actual root partition.
>
> If you are given a leaf partition as input, then you will have to keep
> executing the query until you find the root, and count those. So, I think
> it should be either be the level to the root, or there should be another
> column that lists that (rootlevel).

The function pg_partition_children is to get partitions found under a
given root table. If you pass a leaf partition to it, then there is
nothing under, just the leaf partition itself, and its level wrt itself is
0. That's what Robert said too, to which you replied:

On 2018/08/03 22:11, Jesper Pedersen wrote:
> We had the 2 pg_partition_level() functions and
> pg_partition_leaf_children() in v8, so it would be good to get those back.

Do we need a pg_partition_level that expects the individual partition OID
to be passed to it or can we do with the information we get from the
revised pg_partition_children? In earlier revisions,
pg_partition_children returned only the partition OIDs, so we needed to
provide pg_partition_* functions for getting the parent, root parent,
level, etc. separately. I mean to ask if is there a need for having these
functions separately if the revised pg_partition_children already outputs
that information?

pg_partition_leaf_children()'s output can be obtained as follows, after
adding isleaf column to pg_partition_children's output:

select * from pg_partition_children('<root>') where isleaf;

Attached updated patch adds isleaf to pg_partition_children's output.

Thanks,
Amit