Re: pg_relation_size on partitioned table

On Fri, Mar 25, 2022 at 6:23 PM Japin Li <japinli@hotmail.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;

Regards,
Bharath Rupireddy.