Re: pg_relation_size on partitioned table

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;

Regards,
Bharath Rupireddy.