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
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-26 08:47:37
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi Jesper,

On 2018/07/20 21:26, Jesper Pedersen wrote:
> On 07/19/2018 10:27 PM, Amit Langote wrote:
>> On 2018/07/19 23:18, Jesper Pedersen wrote:
>>> 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.

Alright, I have replaced pg_partition_tree_tables with
pg_partition_children with an 'include_all' argument, as you suggested,
but I implemented it as an optional argument. So, one would use that
argument only if need to get *all* partitions. I have also added a
pg_partition_leaf_children() that returns just the leaf partitions, which
wasn't there in the previous versions.

Further, I've added a pg_partition_level that returns the level of a
partition in the partition tree wrt to the root of the *whole* partition
tree. But maybe we want this function to accept one more argument,
'rootoid', the OID of the root table against which to measure the level?

>> 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.

Yeah, that would be appreciated.

>>> 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.

OK, I fixed it to return just the count of leaf partitions and renamed it
as such (pg_partition_children_leaf_count), but wonder if it's been made
redundant by the addition of pg_partition_leaf_children.

Thanks for the feedback.


Attachment Content-Type Size
v5-0001-Add-assorted-partition-reporting-functions.patch text/plain 25.8 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Chris Travers 2018-07-26 09:23:37 Re: How can we submit code patches that implement our (pending) patents?
Previous Message Sergei Kornilov 2018-07-26 08:35:57 Re: Log query parameters for terminated execute