Re: [Sender Address Forgery]Re: pg_(total_)relation_size andpartitioned tables

On 2018/01/02 22:45, Peter Eisentraut wrote:
> On 12/28/17 16:24, David Rowley wrote:
>>> select pg_partition_root(c.oid), c.relname, pg_table_size(c.oid)
>>>   from pg_class c
>>>   order by 1
>>>
>>> select pg_partition_root(c.oid), sum(pg_table_size(c.oid))
>>>   from pg_class c
>>>   group by 1
>>
>> That seems much nicer. I assume "root" would mean the top level
>> partitioned table. If so, would we also want
>> pg_partition_parent(regclass)? Or maybe something to control the
>> number of "levels-up" the function would run for. If we had that then
>> maybe -1 could mean "go until you find a table with no parent".
> 
> Hmm, we need to think through some scenarios for what one would really
> want to do with this functionality.
> 
> Clearly, the existing behavior is useful for management tasks like bloat
> and vacuum monitoring.
> 
> And on the other hand you might want to have a logical view of, how big
> is this partitioned table altogether.
> 
> But what are the uses for dealing with partial partition hierarchies?
> How easy do we need to make that?

I think having pg_partition_root() and pg_partition_parent() will give
users enough to get useful views as follows:

drop table p;
create table p (a int) partition by list (a);
create table p123 partition of p for values in (1, 2, 3) partition by list
(a);
create table p12 partition of p1 for values in (1, 2) partition by list (a);
create table p12 partition of p123 for values in (1, 2) partition by list (a);
create table p1 partition of p12 for values in (1);
create table p2 partition of p12 for values in (2);
create table p3 partition of p123 for values in (3);

insert into p select 1 from generate_series(1, 100);
insert into p select 2 from generate_series(1, 100);
insert into p select 3 from generate_series(1, 100);

select  pg_partition_root(oid) as root_parent,
        pg_partition_parent(oid) as parent,
        relname as relname,
        pg_total_relation_size(oid) as size
from    pg_class
where   relnamespace = 'public'::regnamespace
order by 4;
 root_parent | parent | relname | size
-------------+--------+---------+------
 p           |        | p       |    0
 p           | p      | p123    |    0
 p           | p123   | p12     |    0
 p           | p12    | p1      | 8192
 p           | p12    | p2      | 8192
 p           | p123   | p3      | 8192
(6 rows)

select  pg_partition_root(oid) as root_parent,
        sum(pg_total_relation_size(oid)) as size
from    pg_class
where   relnamespace = 'public'::regnamespace
group by 1
order by 1;
 root_parent | size
-------------+-------
 p           | 24576
(1 row)

Attached a WIP patch.

Thanks,
Amit