Re: BUG #15245: pg_stat_all_tables does not include partition mastertables

On 2018/06/18 15:05, Michael Paquier wrote:
> On Mon, Jun 18, 2018 at 05:49:47PM +1200, David Rowley wrote:
>> FWIW there was a similar discussion of whether
>> pg_relation_size('<partitioned table>') should report the sizes of all
>> its partitions in [1].  Most people seemed to vote to leave it
>> returning 0.
>>
>> Some of the same reasoning might apply to this case.
>>
>> [1] https://www.postgresql.org/message-id/495cec7e-f8d9-7e13-4807-90dbf4eec4ea@lab.ntt.co.jp
> 
> Having a wrapper on top of find_all_inheritors() which grabs all the
> relations in a partition tree would be more helpful for all those cases
> in my opinion, and this could just aggregate with pg_relation_size.
> That's also discussed at the bottom of the thread David is mentioning.
> It would be nice to get that into v12.

Yeah.  Looking at that patch and subsequent comments on it, it seems we'd
like to have a function, say, pg_get_inheritance_tables
(pg_partition_tree_tables was what was used in one of the patches that
were posted), which can be used as follows:

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 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);
create index on p (a);
insert into p select i % 3 + 1 from generate_series(1, 1000) i;

select  p as relname,
        pg_partition_parent(p) as parent,
        pg_partition_root(p) as root_parent,
        pg_total_relation_size(p) as size
from    pg_get_inheritance_tables('p') p
order by 4;
 relname | parent | root_parent | size
---------+--------+-------------+-------
 p       |        | p           |     0
 p123    | p      | p           |     0
 p12     | p123   | p           |     0
 p3      | p123   | p           | 57344
 p1      | p12    | p           | 57344
 p2      | p12    | p           | 57344
(6 rows)

select  pg_size_pretty(sum(pg_total_relation_size(p))) as size
from    pg_get_inheritance_tables('p') p
  size
--------
 168 kB
(1 row)

Thanks,
Amit