Finding and Fixing INT-Based Sequences in YugabyteDB Before They Overflow

				
					WITH base_cols AS (
  SELECT
      ns.nspname                AS schema_name,
      t.relname                 AS table_name,
      a.attnum                  AS attnum,
      a.attname                 AS column_name,
      a.atttypid::regtype::text AS column_type,
      (a.attidentity <> '')     AS is_identity,
      format('%I.%I', ns.nspname, t.relname) AS fq_table_name,
      t.oid                     AS table_oid
  FROM pg_class t
  JOIN pg_namespace ns ON ns.oid = t.relnamespace
  JOIN pg_attribute a  ON a.attrelid = t.oid
                       AND a.attnum > 0
                       AND NOT a.attisdropped
  WHERE t.relkind IN ('r','p')
    AND a.atttypid IN ('int2'::regtype, 'int4'::regtype)
),
col_defaults AS (
  SELECT
    b.*, ad.oid AS ad_oid,
    pg_get_expr(ad.adbin, ad.adrelid) AS def_expr
  FROM base_cols b
  LEFT JOIN pg_attrdef ad
    ON ad.adrelid = b.table_oid
   AND ad.adnum   = b.attnum
),
by_func AS (
  SELECT c.*, pg_get_serial_sequence(c.fq_table_name, c.column_name) AS seq_from_func
  FROM col_defaults c
),
by_depend_owned AS (
  SELECT c.schema_name, c.table_name, c.attnum, c.column_name, c.column_type, c.is_identity,
         sn.nspname || '.' || s.relname AS seq_from_owned
  FROM col_defaults c
  JOIN pg_depend d
    ON d.refobjid = c.table_oid
   AND d.refobjsubid = c.attnum
   AND d.deptype IN ('a','i')
  JOIN pg_class s ON s.oid = d.objid AND s.relkind = 'S'
  JOIN pg_namespace sn ON sn.oid = s.relnamespace
),
by_depend_default AS (
  SELECT c.schema_name, c.table_name, c.attnum, c.column_name, c.column_type, c.is_identity,
         sn.nspname || '.' || s.relname AS seq_from_default
  FROM col_defaults c
  JOIN pg_depend d
    ON d.refobjid = c.ad_oid
   AND d.deptype = 'n'
  JOIN pg_class s ON s.oid = d.objid AND s.relkind = 'S'
  JOIN pg_namespace sn ON sn.oid = s.relnamespace
),
by_regex AS (
  SELECT c.schema_name, c.table_name, c.attnum, c.column_name, c.column_type, c.is_identity,
         CASE
           WHEN c.def_expr ~* $$nextval\s*\('([^']+)'\s*::\s*regclass\)$$ THEN
             (SELECT n.nspname || '.' || cls.relname
              FROM pg_class cls
              JOIN pg_namespace n ON n.oid = cls.relnamespace
              WHERE cls.oid = to_regclass(
                regexp_replace(c.def_expr,
                               $$.*nextval\s*\('([^']+)'\s*::\s*regclass\).*$$,
                               '\1')
              ))
         END AS seq_from_regex
  FROM col_defaults c
),
risky AS (
  SELECT DISTINCT
    f.schema_name, f.table_name, f.column_name, f.column_type, f.is_identity,
    COALESCE(f.seq_from_func, o.seq_from_owned, d.seq_from_default, r.seq_from_regex) AS sequence_name
  FROM by_func f
  LEFT JOIN by_depend_owned   o USING (schema_name, table_name, attnum)
  LEFT JOIN by_depend_default d USING (schema_name, table_name, attnum)
  LEFT JOIN by_regex          r USING (schema_name, table_name, attnum)
  WHERE COALESCE(f.seq_from_func, o.seq_from_owned, d.seq_from_default, r.seq_from_regex) IS NOT NULL
),
seqs AS (
  SELECT schemaname, sequencename, last_value, min_value, max_value, increment_by, cache_size
  FROM pg_sequences
)
SELECT
  r.schema_name, r.table_name, r.column_name, r.column_type, r.is_identity,
  r.sequence_name,
  s.last_value, s.min_value, s.max_value, s.increment_by, s.cache_size,
  CASE
    WHEN r.column_type = 'smallint' THEN (32767      - COALESCE(s.last_value, 0))::bigint
    WHEN r.column_type = 'integer'  THEN (2147483647 - COALESCE(s.last_value, 0))::bigint
  END AS approx_remaining_until_overflow,
  CASE
    WHEN r.column_type = 'smallint' AND s.max_value > 32767 THEN '⚠ mismatch: seq max > SMALLINT max'
    WHEN r.column_type = 'integer'  AND s.max_value > 2147483647 THEN '⚠ mismatch: seq max > INT max'
  END AS mismatch_warning
FROM risky r
LEFT JOIN seqs s
  ON s.schemaname   = split_part(r.sequence_name, '.', 1)
 AND s.sequencename = split_part(r.sequence_name, '.', 2)
ORDER BY r.schema_name, r.table_name, r.column_name;