在使用greenplum数据库的时候,有的时候想要查看表所占用空间的大小,会使用如下二个函数pg_relation_size和pg_size_pretty.
前者用来查看数据大小,后者是human readable的调整.方法如下:
select pg_size_pretty(pg_relation_size('relation_name')) ;
select pg_size_pretty(pg_relation_size(oid)) ;
但是,对于分区表而言,这个方法就没有用了,会发现使用后表的大小为0bytes.原因在于:GP的分区表的主表只是一个表定义,其实际数据内容存储在继承父表的分区子表里面了.上网查了一下,没发现有相应的函数,干脆写了个函数来实现.
--
Function: calc_partition_table(character varying, character varying)
-- DROP FUNCTION calc_partition_table(character varying, character varying);
CREATE OR REPLACE FUNCTION calc_partition_table(v_schemaname character varying , v_tablename character varying )
RETURNS bigint AS
$BODY$
DECLARE
v_calc BIGINT : = 0 ;
v_total BIGINT : = 0 ;
v_tbname VARCHAR ( 200 );
cur_tbname cursor for select schemaname || ' . ' || partitiontablename as tb from pg_partitions
where schemaname = v_schemaname and tablename = v_tablename;
BEGIN
OPEN cur_tbname;
loop
FETCH cur_tbname into v_tbname;
if not found THEN
exit ;
end if ;
EXECUTE ' select pg_relation_size( ''' || v_tbname || ''' ) ' into v_calc;
v_total: = v_total + v_calc;
end loop;
CLOSE cur_tbname;
RETURN v_total;
end ;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION calc_partition_table( character varying , character varying ) OWNER TO gpadmin;
-- DROP FUNCTION calc_partition_table(character varying, character varying);
CREATE OR REPLACE FUNCTION calc_partition_table(v_schemaname character varying , v_tablename character varying )
RETURNS bigint AS
$BODY$
DECLARE
v_calc BIGINT : = 0 ;
v_total BIGINT : = 0 ;
v_tbname VARCHAR ( 200 );
cur_tbname cursor for select schemaname || ' . ' || partitiontablename as tb from pg_partitions
where schemaname = v_schemaname and tablename = v_tablename;
BEGIN
OPEN cur_tbname;
loop
FETCH cur_tbname into v_tbname;
if not found THEN
exit ;
end if ;
EXECUTE ' select pg_relation_size( ''' || v_tbname || ''' ) ' into v_calc;
v_total: = v_total + v_calc;
end loop;
CLOSE cur_tbname;
RETURN v_total;
end ;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION calc_partition_table( character varying , character varying ) OWNER TO gpadmin;
