Skip to content
GitLab
  • Menu
Projects Groups Snippets
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
    • Switch to GitLab Next
  • Sign in / Register
  • postgres-checkup – automated PostgreSQL health checks postgres-checkup – automated PostgreSQL health checks
  • Project information
    • Project information
    • Activity
    • Labels
    • Members
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributors
    • Graph
    • Compare
    • Locked Files
  • Issues 164
    • Issues 164
    • List
    • Boards
    • Service Desk
    • Milestones
    • Iterations
    • Requirements
  • Merge requests 11
    • Merge requests 11
  • CI/CD
    • CI/CD
    • Pipelines
    • Jobs
    • Schedules
    • Test Cases
  • Deployments
    • Deployments
    • Environments
    • Releases
  • Packages & Registries
    • Packages & Registries
    • Package Registry
    • Container Registry
    • Infrastructure Registry
  • Monitor
    • Monitor
    • Incidents
  • Analytics
    • Analytics
    • Value stream
    • CI/CD
    • Code review
    • Insights
    • Issue
    • Repository
  • Wiki
    • Wiki
  • Snippets
    • Snippets
  • Activity
  • Graph
  • Create a new issue
  • Jobs
  • Commits
  • Issue Boards
Collapse sidebar
  • Postgres.ai
  • postgres-checkup – automated PostgreSQL health checkspostgres-checkup – automated PostgreSQL health checks
  • Issues
  • #237
Closed
Open
Created Jan 28, 2019 by Nikolay Samokhvalov@NikolayS🇺🇦Owner3 of 4 tasks completed3/4 tasks

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

Screen_Shot_2019-01-27_at_20.17.08

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
....

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).

Edited Feb 26, 2019 by Oleg
Assignee
Assign to
Time tracking