Re: partition tree inspection functions

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: jesper(dot)pedersen(at)redhat(dot)com, Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Michael Paquier <michael(at)paquier(dot)xyz>, Jeevan Ladhe <jeevan(dot)ladhe(at)enterprisedb(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: partition tree inspection functions
Date: 2018-08-07 07:32:36
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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.


Attachment Content-Type Size
v10-0001-Add-pg_partition_children-to-report-partitions.patch text/plain 12.7 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2018-08-07 08:09:46 Re: Fix hints on CREATE PROCEDURE errors
Previous Message Andrey Lepikhov 2018-08-07 07:19:35 Re: [WIP] [B-Tree] Retail IndexTuple deletion