Re: pg_relation_size on partitioned table

On Fri, 25 Mar 2022 at 21:21, Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> On Fri, Mar 25, 2022 at 6:23 PM Japin Li <japinli(at)hotmail(dot)com> wrote:
>>
>> Hi, hackers
>>
>> When I try to get total size of partition tables though partitioned table
>> name using pg_relation_size(), it always returns zero. I can use the
>> following SQL to get total size of partition tables, however, it is a bit
>> complex.
>>
>> SELECT
>> pg_size_pretty(sum(pg_relation_size(i.inhrelid)))
>> FROM
>> pg_class c JOIN pg_inherits i ON c.oid = i.inhparent
>> WHERE
>> relname = 'parent';
>>
>> Could we provide a function to get the total size of the partition table
>> though the partitioned table name? Maybe we can extend
>> the pg_relation_size() to get the total size of partition tables through
>> the partitioned table name.
>
> If we want to have it in the core, why can't it just be a function (in
> system_functions.sql) something like below? Not everyone, would know
> how to get partition relation size, especially whey they are not using
> psql, they can't use the short forms that it provides.
>
> CREATE OR REPLACE FUNCTION pg_partition_relation_size(regclass)
> RETURNS bigint
> LANGUAGE sql
> PARALLEL SAFE STRICT COST 1
> BEGIN ATOMIC
> SELECT
> pg_size_pretty(sum(pg_relation_size(i.inhrelid)))
> FROM
> pg_class c JOIN pg_inherits i ON c.oid = i.inhparent
> WHERE
> relname = '$1';
> END;
>

I add two functions (as suggested by Bharath Rupireddy)
pg_partition_relation_size and pg_partition_table_size to get partition tables
size through partitioned table name. It may reduce the complexity to get the
size of partition tables.

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.