k5in1CGr

1DO $$
2
3DECLARE
4
5sequence text;
6columntype text;
7warning_threshold bigint;
8result bigint;
9alerts text[];
10
11BEGIN
12
13DROP TABLE IF EXISTS _monitoring_integer_overflow_records;
14
15CREATE TEMP TABLE _monitoring_integer_overflow_records (sequence text, columntype text, result integer);
16
17FOR sequence, columntype, warning_threshold IN 
18select
19            split_part(pg_get_expr(d.adbin, d.adrelid),E'\'', 2)  AS sequence,
20            format_type(a.atttypid, a.atttypmod) AS column_type,
21            CASE 
22                  WHEN format_type(a.atttypid, a.atttypmod) = 'integer'  THEN (POWER(2,31) * 0.80)::bigint
23                  WHEN format_type(a.atttypid, a.atttypmod) = 'bigint'  THEN (POWER(2,61) * 0.80)::bigint
24                  ELSE 0
25            END
26          FROM
27            pg_catalog.pg_attribute a
28          INNER JOIN
29            pg_catalog.pg_class c ON c.oid = a.attrelid
30          INNER JOIN
31            pg_catalog.pg_namespace n ON n.oid = c.relnamespace
32          INNER JOIN
33            pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid,  d.adnum)
34          WHERE
35            NOT a.attisdropped
36            AND a.attnum > 0
37            AND pg_get_expr(d.adbin, d.adrelid) LIKE 'nextval%'
38            AND n.nspname NOT LIKE 'pg\\_temp\\_%'  LOOP
39
40EXECUTE 'select last_value from ' || sequence  INTO result;
41
42IF result > warning_threshold THEN
43    insert into _monitoring_integer_overflow_records (sequence, columntype, result) values (sequence, columntype, result);
44END IF;
45
46
47END LOOP;
48
49END;
50
51$$;
52
53select * from _monitoring_integer_overflow_records;