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@xxxxxxxxxxxxxxxxxxxxxx
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
