Greenplum partition table to view the Occupied Space

When using the greenplum database, the following two functions are used to check the space occupied by the table: pg_relation_size and pg_size_pretty.

The former is used to view the data size, and the latter is the adjustment of human readable. The method is as follows:

Select pg_size_pretty (pg_relation_size ('relation _ name '));

Select pg_size_pretty (pg_relation_size (OID ));

However, this method is useless for partitioned tables, and the table size is 0 bytes. the reason is that the main table of the GP partition table is only a table definition, and the actual data content is stored in the partition sub-table that inherits the parent table. I checked it online and did not find the corresponding function. I simply wrote a function to implement it.

 

-- 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;