Обсуждение: pgsql: Add pg_partition_root to display top-most parent of apartition

Add pg_partition_root to display top-most parent of a partition tree

This is useful when looking at partition trees with multiple layers, and
combined with pg_partition_tree, it provides the possibility to show up
an entire tree by just knowing one member at any level.

Author: Michael Paquier
Reviewed-by: Álvaro Herrera, Amit Langote
Discussion: https://postgr.es/m/20181207014015.GP2407@paquier.xyz

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/3677a0b26bb2f3f72d16dc7fa6f34c305badacce

Modified Files
--------------
doc/src/sgml/func.sgml                       | 11 ++++
src/backend/utils/adt/partitionfuncs.c       | 75 ++++++++++++++++++++++++----
src/include/catalog/catversion.h             |  2 +-
src/include/catalog/pg_proc.dat              |  5 ++
src/test/regress/expected/partition_info.out | 58 +++++++++++++++++++++
src/test/regress/sql/partition_info.sql      | 13 +++++
6 files changed, 153 insertions(+), 11 deletions(-)

On 2019-Feb-08, Michael Paquier wrote:

> Add pg_partition_root to display top-most parent of a partition tree
> 
> This is useful when looking at partition trees with multiple layers, and
> combined with pg_partition_tree, it provides the possibility to show up
> an entire tree by just knowing one member at any level.

I noticed days ago that if you call pg_partition_root on the topmost
partitioned table, the server crashes :-)

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Fri, Mar 22, 2019 at 12:26:12AM -0300, Alvaro Herrera wrote:
> I noticed days ago that if you call pg_partition_root on the topmost
> partitioned table, the server crashes :-)

It's when you think that the thing is actually done that another issue
pops up.  The attached fixes the issue, I suggest to return the
top-most parent as result if the input is the top-most parent itself.
Returning NULL does not make sense as in this case the relation can be
part of a partition tree.
--
Michael

Hi,

On 2019/03/22 12:55, Michael Paquier wrote:
> On Fri, Mar 22, 2019 at 12:26:12AM -0300, Alvaro Herrera wrote:
>> I noticed days ago that if you call pg_partition_root on the topmost
>> partitioned table, the server crashes :-)

I thought we already fixed that last month, but that was pg_partition_tree().

> It's when you think that the thing is actually done that another issue
> pops up.  The attached fixes the issue, I suggest to return the
> top-most parent as result if the input is the top-most parent itself.
> Returning NULL does not make sense as in this case the relation can be
> part of a partition tree.

Thanks for writing the patch.

     /* Fetch the top-most parent */
     ancestors = get_partition_ancestors(relid);

Maybe, the patch should update this comment to say "Fetch the list of
ancestors".

+
+    /*
+     * If the listed partition is already the top-most parent, just
+     * return itself.
+     */

What does "listed partition" mean?  Maybe, you mean "If the input
relation..."?  How about write this as:

If the input relation is itself the top-most parent, just return itself.

Regards,
Amit

On Fri, Mar 22, 2019 at 01:09:23PM +0900, Amit Langote wrote:
>      /* Fetch the top-most parent */
>      ancestors = get_partition_ancestors(relid);
>
> Maybe, the patch should update this comment to say "Fetch the list of
> ancestors".

Perhaps so.  Note that actually the list is fetched to grab the
top-most parent from it, so it sort of makes sense?

> What does "listed partition" mean?  Maybe, you mean "If the input
> relation..."?  How about write this as:

Input relation sounds better.
--
Michael

Sorry about the messed up subject string of my previous reply.  I failed
to notice that our internal mailing software occasionally adds that to
email headers before the emails get to my machine.  I've been asked to be
careful before, but I didn't notice it again today.  :-(

Thanks,
Amit

On 2019/03/22 13:12, Michael Paquier wrote:
> On Fri, Mar 22, 2019 at 01:09:23PM +0900, Amit Langote wrote:
>>      /* Fetch the top-most parent */
>>      ancestors = get_partition_ancestors(relid);
>>
>> Maybe, the patch should update this comment to say "Fetch the list of
>> ancestors".
> 
> Perhaps so.  Note that actually the list is fetched to grab the
> top-most parent from it, so it sort of makes sense?

It looked fine before, but the new lines added by patch makes it look
wrong/misplaced somehow.

Thanks,
Amit

On Fri, Mar 22, 2019 at 01:28:19PM +0900, Amit Langote wrote:
> It looked fine before, but the new lines added by patch makes it look
> wrong/misplaced somehow.

Okay, what do you think about the attached then?
--
Michael

On 2019/03/22 14:13, Michael Paquier wrote:
> On Fri, Mar 22, 2019 at 01:28:19PM +0900, Amit Langote wrote:
>> It looked fine before, but the new lines added by patch makes it look
>> wrong/misplaced somehow.
> 
> Okay, what do you think about the attached then?

Thanks, looks fine.

Regards,
Amit

On Fri, Mar 22, 2019 at 04:05:20PM +0900, Amit Langote wrote:
> Thanks, looks fine.

OK, committed.
--
Michael