Postgres Pro
Downloads
Home   >   mailing lists

Thread: reltoastidxid altenates in postgresql 9.4

reltoastidxid altenates in postgresql 9.4

From
"Yelai, Ramkumar IN BLR STS"
Date:
Hi All,
 
I am using the following code to know how much disk space could be saved after deleting certain tables (as a parameter to this function )
 
CREATE OR REPLACE FUNCTION Get_Tables_Recovery_Size(  IN tableNames text[] )
RETURNS TABLE( table_size bigint )
AS
$$
DECLARE
BEGIN
        RETURN QUERY
                (
                        SELECT COALESCE(SUM( ALLTABLE.totalsize ),0)::bigint FROM
                        (
                                SELECT
                                        relname,
                                        (tablesize+indexsize+toastsize+toastindexsize) AS totalsize
                                FROM
                                (
                                        SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize,
                                        COALESCE(
                                                        (SELECT SUM(pg_relation_size(indexrelid))::bigint FROM pg_index WHERE cl.oid=indrelid), 0
                                                ) AS indexsize,
                                        CASE
                                                WHEN reltoastrelid = 0 THEN 0
                                                ELSE
                                                        pg_relation_size(reltoastrelid)
                                                END AS toastsize,
                                               
                                        CASE
                                                WHEN reltoastrelid = 0 THEN 0
                                                ELSE
                                                        pg_relation_size((SELECT reltoastidxid FROM pg_class ct WHERE ct.oid = cl.reltoastrelid))
                                                END AS toastindexsize
                                        FROM
                                                pg_class cl,
                                                pg_namespace ns
                                               
                                        WHERE   
                                                pg_relation_size(cl.oid) != 0 AND
                                                cl.relnamespace = ns.oid AND
                                                ns.nspname NOT IN ('pg_catalog', 'information_schema') AND
                                                cl.relname IN
                                                (SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE')
                                ) ss
                                WHERE
                                        relname IN ( SELECT $1[i] FROM generate_subscripts($1, 1) g(i) )
                        ) ALLTABLE
                );
END;
$$ LANGUAGE plpgsql;
 
After migrated 9.4. I am getting error that reltoastidxid is not present in pg_class. Due to REINDEX CONCURRENTLY this column removed. http://www.postgresql.org/message-id/E1UuRj8-0001au-F9@gemulon.postgresql.org
 
Would you please tell me how to modify this code.
 
With best regards,
Ramkumar Yelai
 
 
Siemens Technology and Services Private Limited
CT DC AA I HOUSE DEV GL4
84, Hosur Road
Bengaluru 560100, Indien
Tel.: +91 80 33136494
Fax: +91 80 33133389
Mobil: +91 9886182031
 
Registered Office: 130, Pandurang Budhkar Marg, Worli, Mumbai 400 018. Telephone +91 22 39677000. Fax +91 22 39677075. Other Offices: Bangalore, Chennai, Gurgaon, Noida, Kolkata, Pune. Corporate Identity number:U99999MH1986PLC093854
 
 

Re: reltoastidxid altenates in postgresql 9.4

From
Josh Kupershmidt
Date:
On Thu, Mar 5, 2015 at 2:02 AM, Yelai, Ramkumar IN BLR STS
<ramkumar.yelai@siemens.com> wrote:
> Hi All,
>
> I am using the following code to know how much disk space could be saved
> after deleting certain tables (as a parameter to this function )
>
> CREATE OR REPLACE FUNCTION Get_Tables_Recovery_Size(  IN tableNames text[] )

Why not just use the built-in pg_total_relation_size?

Josh