From: | Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com> |
---|---|
To: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
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-07-20 12:26:03 |
Message-ID: | da9f8b6d-13bb-18fe-7777-b3be7bdd327b@redhat.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi Amit,
On 07/19/2018 10:27 PM, Amit Langote wrote:
> 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?
>
Yes, Dilip's idea could work. I just don't think that
pg_partition_tree_tables() as is would have a benefit over time.
> 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.
>
We could have the patch include pg_partition_root_parent and
pg_partition_parent, and leave the rest for a future CommitFest such
that more people could provide feedback on what they would like to see
in this space.
>>>> 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?
>
That could work.
Best regards,
Jesper
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2018-07-20 12:27:34 | Re: Faster str to int conversion (was Table with large number of int columns, very slow COPY FROM) |
Previous Message | Alexander Korotkov | 2018-07-20 11:42:34 | Re: Flexible configuration for full-text search |