Postgres - Find Columns which are nearing capacity

Postgres - Find Columns which are nearing capacity.sql

DO $$

DECLARE

sequence text;
columntype text;
warning_threshold bigint;
result bigint;
alerts text[];

BEGIN

DROP TABLE IF EXISTS _monitoring_integer_overflow_records;

CREATE TEMP TABLE _monitoring_integer_overflow_records (sequence text, columntype text, result integer);

FOR sequence, columntype, warning_threshold IN 
select
            split_part(pg_get_expr(d.adbin, d.adrelid),E'\'', 2)  AS sequence,
            format_type(a.atttypid, a.atttypmod) AS column_type,
            CASE 
                  WHEN format_type(a.atttypid, a.atttypmod) = 'integer'  THEN (POWER(2,31) * 0.80)::bigint
                  WHEN format_type(a.atttypid, a.atttypmod) = 'bigint'  THEN (POWER(2,61) * 0.80)::bigint
                  ELSE 0
            END
          FROM
            pg_catalog.pg_attribute a
          INNER JOIN
            pg_catalog.pg_class c ON c.oid = a.attrelid
          INNER JOIN
            pg_catalog.pg_namespace n ON n.oid = c.relnamespace
          INNER JOIN
            pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid,  d.adnum)
          WHERE
            NOT a.attisdropped
            AND a.attnum > 0
            AND pg_get_expr(d.adbin, d.adrelid) LIKE 'nextval%'
            AND n.nspname NOT LIKE 'pg\\_temp\\_%'  LOOP

EXECUTE 'select last_value from ' || sequence  INTO result;

IF result > warning_threshold THEN
    insert into _monitoring_integer_overflow_records (sequence, columntype, result) values (sequence, columntype, result);
END IF;


END LOOP;

END;

$$;

select * from _monitoring_integer_overflow_records;