reltoastidxid altenates in postgresql 9.4 - Mailing list 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.