11if [[! -z ${IS_LARGE_DB+x} ]]&& [[${IS_LARGE_DB} == " 1" ; then
2- MIN_RELPAGES=1000
2+ MIN_RELPAGES=100
33else
44 MIN_RELPAGES=0
55fi
@@ -10,11 +10,14 @@ f_stderr=$(mktemp)
1010(${CHECK_HOST_CMD} " ${_PSQL} -f -" << SQL
1111do\$ $
1212declare
13+ MIN_RELPAGES int8 =${MIN_RELPAGES} ; -- skip tables with small number of pages
1314 rec record;
1415 out text;
16+ out1 json;
1517 i numeric;
1618 val int8;
1719 ratio numeric;
20+ sql text;
1821begin
1922 out := '';
2023 i := 0;
2528 nspname as schema_name,
2629 relname as table_name,
2730 t.typname,
28- attname,
29- (select pg_get_serial_sequence(quote_ident(nspname) || '.' || quote_ident(relname), attname)) asseq
31+ (select pg_get_serial_sequence(quote_ident(nspname) || '.' || quote_ident(relname), attname)) as seq ,
32+ min( attname) asattname
3033 from pg_index i
3134 join pg_class c on c.oid = i.indrelid
3235 left join pg_namespace n on n.oid = c.relnamespace
@@ -36,25 +39,35 @@ begin
3639 join pg_type t on t.oid = atttypid
3740 where
3841 i.indisprimary
39- and (c.relpages >${ MIN_RELPAGES}
42+ and (c.relpages > MIN_RELPAGES or (select pg_get_serial_sequence(quote_ident(nspname) || '.' || quote_ident(relname), attname)) is not null)
4043 and t.typname in ('int2', 'int4')
4144 and nspname <> 'pg_toast'
45+ group by 1, 2, 3, 4, 5, 6
46+ having count(*) = 1 -- skip PKs with 2+ columns
4247 loop
48+ raise debug 'table: %', rec.table_name;
49+
4350 if rec.seq is null then
44- execute format('select max(%I) from %I.%I;', rec.attname, rec.schema_name, rec.table_name) into val ;
51+ sql := format('select max(%I) from %I.%I;', rec.attname, rec.schema_name, rec.table_name);
4552 else
46- execute format('SELECT last_valueFROM %s;', rec.seq) into val ;
53+ sql := format('select last_valuefrom %s;', rec.seq);
4754 end if;
55+
56+ raise debug 'sql: %', sql;
57+ execute sql into val;
58+
4859 if rec.typname = 'int4' then
4960 ratio := (val::numeric / 2^31)::numeric;
5061 elsif rec.typname = 'int2' then
5162 ratio := (val::numeric / 2^15)::numeric;
5263 else
5364 assert false, 'unreachable point';
5465 end if;
66+
5567 if ratio > 0.1 then -- report only if > 10% of capacity is reached
5668 i := i + 1;
57- out := out || '{"' || rec.table_name || '":' || json_build_object(
69+
70+ out1 := json_build_object(
5871 'table',
5972 coalesce(nullif(quote_ident(rec.schema_name), 'public') || '.', '') || quote_ident(rec.table_name),
6073 'pk',
6578 val,
6679 'capacity_used_percent',
6780 round(100 * ratio, 2)
68- ) || '}';
81+ );
82+
83+ raise debug 'cur: %', out1;
84+
85+ if out <> '' then out := out || ', '; end if;
86+
87+ out := out || '"' || rec.table_name || '":' || out1 || '';
6988 end if;
7089 end loop;
90+
91+ out := '{' || out || '}';
92+
7193 raise info '%', out;
7294end;
7395\$ $ language plpgsql;