L003 Integer (int2, int4) out-of-range risks in PKs
Analyze all int2 and int4 primary keys in the observed database and show risks to hit int2/int4 limits.
TODO / Implementation details
It seems that we need to use PLpgSQL code to be able to process all tables/PKs in a single request.
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)
Get values from the query avove, store in the JSON report
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 ....
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).