Re: partition tree inspection functions

On 2018/08/01 22:21, Robert Haas wrote:
> 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.

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