On Fri, 25 Mar 2022 at 21:21, Bharath Rupireddy <bharath.rupireddyforpostg...@gmail.com> wrote: > On Fri, Mar 25, 2022 at 6:23 PM Japin Li <japi...@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; >
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.
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql index 81bac6f581..81cab4c21c 100644 --- a/src/backend/catalog/system_functions.sql +++ b/src/backend/catalog/system_functions.sql @@ -281,6 +281,32 @@ CREATE OR REPLACE FUNCTION pg_relation_size(regclass) PARALLEL SAFE STRICT COST 1 RETURN pg_relation_size($1, 'main'); +CREATE OR REPLACE FUNCTION pg_partition_relation_size(regclass, text default 'main') +RETURNS bigint +LANGUAGE sql +PARALLEL SAFE STRICT COST 1 +BEGIN ATOMIC + SELECT + sum(pg_relation_size(i.inhrelid, $2)) + FROM + pg_class c JOIN pg_inherits i ON c.oid = i.inhparent + WHERE + oid = $1; +END; + +CREATE OR REPLACE FUNCTION pg_partition_table_size(regclass) +RETURNS bigint +LANGUAGE sql +PARALLEL SAFE STRICT COST 1 +BEGIN ATOMIC + SELECT + sum(pg_table_size(i.inhrelid)) + FROM + pg_class c JOIN pg_inherits i ON c.oid = i.inhparent + WHERE + oid = $1; +END; + CREATE OR REPLACE FUNCTION obj_description(oid, name) RETURNS text LANGUAGE sql
