Thread

1. reltoastidxid altenates in postgresql 9.4

   Yelai, Ramkumar <ramkumar.yelai@siemens.com> — 2015-03-05T07:02:33Z

   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.yelai@siemens.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
   
   
   

2. Re: reltoastidxid altenates in postgresql 9.4

   Josh Kupershmidt <schmiddy@gmail.com> — 2015-03-10T20:50:50Z

   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