New report: check int2 and int4 PK columns to be out-of-capacity soon

[NikolayS]

Report name: c1_integer_pk_columns_check

Idea: check int4 PKs to be out-of-capacity in the nearest future (is max value is approaching 2^31 soon?). The same for int2 PKs (limit: 2^15).

Advanced idea: if created or created_at column is present in the table, forecast reaching the limit.

The goal of this report: warn about approaching int4/int2 limits, propose using int8/in4 instead.

[NikolayS]

The very 1st draft (requires plpgsql, now works checks only int4 PKs):

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;

[NikolayS]

TODO:

• [ ] if there is a sequence there, use it instead of max(..) (select last_value from ...;) – because max(id) can lead to wrong expectations if a lot of recently-inserted rows were deleted
• [ ] order by ratio?
• [ ] severity levels (OK/WARNING/CRITICAL) ?
• [ ] use created / created_at to forecast reaching the limits?

[NikolayS]

Also to consider:

• is it worth to add this check to 2_tables report (in addition to or instead of having a separate one)? In this case, how to get the current values? Use row count estimate? (can lead to wrong results if a lot of rows were deleted)

[NikolayS]

Implemented in https://gitlab.com/postgres-ai/postgres-checkup (report L003)