Re: partition tree inspection functions

On Thu, Jul 26, 2018 at 4:47 AM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> 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?

I have another idea. Suppose we just have one function, and that
function a set of records, and each record contains (1) the OID of a
table, (2) the OID of the immediate parent or NULL for the root, and
(3) the level (0 = root, 1 = child, 2 = grandchild, etc.).

So then to get the immediate children you would say:

SELECT * FROM pg_whatever() WHERE level = 1

And to get everything you would just say:

SELECT * FROM pg_whatever();

And if you wanted grandchildren or everything but the root or whatever
you could just adjust the WHERE clause.

By including the OID of the immediate parent, there's enough
information for application code to draw an actual graph if it wants,
which doesn't work so well if you just know the levels.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company