L003 Integer (int2, int4) out-of-range risks in PKs
Goal
===
Analyze all int2 and int4 primary keys in the observed database and show risks to hit int2/int4 limits.
See https://www.postgresql.org/docs/current/datatype-numeric.html
TODO / Implementation details
===
It seems that we need to use PLpgSQL code to be able to process all tables/PKs in a single request.
- [x] Take this as a basis to get data:
```
do $$
declare
rec record;
out text;
val int8;
ratio numeric;
begin
out := '';
for rec in
select
c.oid,
(select spcname from pg_tablespace where oid = reltablespace) as tblspace,
nspname as schema_name,
relname as table_name,
t.typname,
attname
from pg_index i
join pg_class c on c.oid = i.indrelid
left join pg_namespace n on n.oid = c.relnamespace
join pg_attribute a on
a.attrelid = i.indrelid
and a.attnum = any(i.indkey)
join pg_type t on t.oid = atttypid
where
i.indisprimary
and t.typname in ('int2', 'int4')
and nspname <> 'pg_toast'
loop
execute format('select max(%I) from %I.%I;', rec.attname, rec.schema_name, rec.table_name) into val;
if rec.typname = 'int4' then
ratio := (val::numeric / 2^31)::numeric;
elsif rec.typname = 'int2' then
ratio := (val::numeric / 2^15)::numeric;
else
assert false, 'unreachable point';
end if;
if ratio > 0.01 then -- report only if > 1% of capacity is reached
out := out || format(
e'\nTable: %I.%I, column: %I, type: %s, reached value: %s (%s%%)',
rec.schema_name,
rec.table_name,
rec.attname,
rec.typname,
val,
round(100 * ratio, 2)
);
end if;
end loop;
raise info '%', out;
end;
$$ language plpgsql;
```
- [ ] Check its behavior on tables with a multi-column PK (optional)
- [x] Get values from the query avove, store in the JSON report
- [x] Create .md report in the following form (order by "Capacity used"):
```
Table | PK (type) | Current max value | ▼ Capacity used, %
---------------------------------
table_in_public_schema | colname (int4) | ... | 12
custom_schema.table_in_custom_schema | colname (int2) | 23
....
```
Acceptance criteria
===
As a DBA, I can quickly check what are current risks to reach int2/int4 limits in PKs. I see what are the values reached in every PK listed. I also see % of max capacity (if it's more that 20%, I should plan conversion to int8 ASAP).
issue