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
