From: | "Yelai, Ramkumar IN BLR STS" <ramkumar(dot)yelai(at)siemens(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | reltoastidxid altenates in postgresql 9.4 |
Date: | 2015-03-05 07:02:33 |
Message-ID: | 8D15F77F211D7D4786182E1C8E679FAD26A09E1A62@INBLRK77M1MSX.in002.siemens.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
mailto:ramkumar(dot)yelai(at)siemens(dot)com
http://www.siemens.co.in/STS
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2015-03-05 07:54:29 | Re: Postgres not using GiST index in a lateral join |
Previous Message | Jim Nasby | 2015-03-05 06:59:34 | Re: Partitioning with the index on the master table. |