From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: jesper.pedersen@redhat.com
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Jeevan Ladhe <jeevan.ladhe@enterprisedb.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: partition tree inspection functions
Date: Fri, 20 Jul 2018 11:27:11 +0900
Message-ID: <5686970f-b4e8-bbe5-9109-b97763714c19@lab.ntt.co.jp> (raw)
In-Reply-To: <38183355-ea7c-9443-0c38-6124fcf5f38b@redhat.com>
References: <8d00e51a-9a51-ad02-d53e-ba6bf50b2e52@lab.ntt.co.jp>
<5a560c9c-3722-1914-f34b-1de0ff3310d1@lab.ntt.co.jp>
<CAOgcT0P8FC4Sat1ytVZgm09qd2bgMv-7pWpvU1HdBMKSah9r-w@mail.gmail.com>
<d5f5b9a8-0c88-8043-2007-be37096bed39@lab.ntt.co.jp>
<20180627121615.GF2667@paquier.xyz>
<45e28062-976d-45bf-56f8-9746f551da02@lab.ntt.co.jp>
<20180628034341.GH11054@paquier.xyz>
<6baeb45a-6222-6b88-342d-37fc7d3cf89a@lab.ntt.co.jp>
<4f4a2518-6c62-5463-6ac2-1241ae9d6041@redhat.com>
<e12ff710-dcf5-18ff-b4a4-cc42432d84c4@lab.ntt.co.jp>
<38183355-ea7c-9443-0c38-6124fcf5f38b@redhat.com>
Hi Jesper,
On 2018/07/19 23:18, Jesper Pedersen wrote:
> I'm thinking about how to best use these functions to generate a graph
> that represents the partition hierarchy.
>
> What about renaming pg_partition_tree_tables() to pg_partition_children(),
> and have it work like
>
> select * from pg_partition_children('p', true);
> ---------
> p
> p0
> p1
> p00
> p01
> p10
> p11
> (7 rows)
>
> select * from pg_partition_children('p', false);
> ---------
> p0
> p1
> (2 rows)
>
> e.g. if 'bool include_all' is true all nodes under the node, including
> itself, are fetched. With false only nodes directly under the node,
> excluding itself, are returned. If there are no children NULL is returned.
That's a big change to make to what this function does, but if that's
what's useful we could make it. As an alternative, wouldn't it help to
implement the idea that Dilip mentioned upthread of providing a function
to report the level of a given table in the partition hierarchy -- 0 for
root, 1 for its partitions and so on?
Basically, as also discussed before, users can already use SQL to get the
information they want out of the relevant catalogs (pg_inherits, etc.).
But, such user queries might not be very future-proof as we might want to
change the catalog organization in the future, so we'd like to provide
users a proper interface to begin with. Keeping that in mind, it'd be
better to think carefully about what we ought to be doing here. Input
like yours is greatly helpful for that.
>>> Maybe a function like pg_partition_number_of_partitions() could be of
>>> benefit to count the number of actual partitions in a tree. Especially
>>> useful in complex scenarios,
>>>
>>> select pg_partition_number_of_partitions('p') as number;
>>>
>>> number
>>> ---------
>>> 4
>>> (1 row)
>>
>> Okay, adding one more function at this point may not be asking for too
>> much. Although, select count(*) from pg_partition_tree_tables('p') would
>> give you the count, a special function seems nice.
>
> Yeah, but I was thinking that the function would only return the number of
> actual tables that contains data, e.g. not include 'p', 'p0' and 'p1' in
> the count; otherwise you could use 'select count(*) from
> pg_partition_children('p', true)' like you said.
Maybe call it pg_partition_tree_leaf_count() or some such then?
Thanks,
Amit
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: Langote_Amit_f8@lab.ntt.co.jp, jesper.pedersen@redhat.com, michael@paquier.xyz, jeevan.ladhe@enterprisedb.com
Subject: Re: partition tree inspection functions
In-Reply-To: <5686970f-b4e8-bbe5-9109-b97763714c19@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox