Re: partition tree inspection functions - Mailing list pgsql-hackers

On 2018/08/01 22:21, Robert Haas wrote:
> On Thu, Jul 26, 2018 at 4:47 AM, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.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.

That's a good idea, thanks.

Actually, by the time I sent the last version of the patch or maybe few
versions before that, I too had started thinking if we shouldn't just have
a SETOF RECORD function like you've outlined here, but wasn't sure of the
fields it should have.  (relid, parentid, level) seems like a good start,
or maybe that's just what we need.

I tried to implement such a function.  Example usage:

create table q (a int, b int, c int) partition by list (a);
create table q1 partition of q for values in (1) partition by hash (b);
create table q11 partition of q1 for values with (modulus 1, remainder 0)
partition by hash (c);
create table q111 partition of q11 for values with (modulus 1, remainder 0);
create table q2 partition of q for values in (2);
insert into q select i%2+1, i, i from generate_series(1, 1000) i;

select * from pg_partition_children('q');
 relid │ parentid │ level
───────┼──────────┼───────
 q     │          │     0
 q1    │ q        │     1
 q2    │ q        │     1
 q11   │ q1       │     2
 q111  │ q11      │     3
(5 rows)

select * from pg_partition_children('q') where level > 0;
 relid │ parentid │ level
───────┼──────────┼───────
 q1    │ q        │     1
 q2    │ q        │     1
 q11   │ q1       │     2
 q111  │ q11      │     3
(4 rows)

select * from pg_partition_children('q') where level = 1;
 relid │ parentid │ level
───────┼──────────┼───────
 q1    │ q        │     1
 q2    │ q        │     1
(2 rows)

select *, pg_relation_size(relid) as size from pg_partition_children('q');
 relid │ parentid │ level │ size
───────┼──────────┼───────┼───────
 q     │          │     0 │     0
 q1    │ q        │     1 │     0
 q2    │ q        │     1 │ 24576
 q11   │ q1       │     2 │     0
 q111  │ q11      │     3 │ 24576
(5 rows)

select sum(pg_relation_size(relid)) as size from pg_partition_children('q');
 size
───────
 49152
(1 row)

select *, pg_relation_size(relid) as size from pg_partition_children('q1');
 relid │ parentid │ level │ size
───────┼──────────┼───────┼───────
 q1    │ q        │     0 │     0
 q11   │ q1       │     1 │     0
 q111  │ q11      │     2 │ 24576
(3 rows)

select *, pg_relation_size(relid) as size from pg_partition_children('q11');
 relid │ parentid │ level │ size
───────┼──────────┼───────┼───────
 q11   │ q1       │     0 │     0
 q111  │ q11      │     1 │ 24576
(2 rows)

select *, pg_relation_size(relid) as size from pg_partition_children('q111');
 relid │ parentid │ level │ size
───────┼──────────┼───────┼───────
 q111  │ q11      │     0 │ 24576
(1 row)

Note that the level that's returned for each table is computed wrt the
root table passed to the function and not the actual root partition.

I have updated the patch to include just this one function, its
documentation, and tests.

Regards,
Amit