reltoastidxid altenates in postgresql 9.4

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

Responses

Browse pgsql-general by date

  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.