Re: BUG #15245: pg_stat_all_tables does not include partition master tables

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