Checking the sequences status on a single pass
The catalogpg_sequence keeps track about the definition of a single sequence, including the increment value and boundaries. Combined with pg_class and a few other functions it is possible to create a very simple administrative function to keep track about the overall sequences status.
I’ve created a
seq_check() function that provides an output as follows:
testdb=# select * from seq_check() ORDER BY remaining;
seq_name | current_value | lim | remaining
------------------------|---------------|------------|------------
public.persona_pk_seq | 5000000 | 2147483647 | 214248
public.root_pk_seq | 50000 | 2147483647 | 2147433647
public.students_pk_seq | 7 | 2147483647 | 2147483640
(3 rows)
persona_pk_seq has remained with 214248 values to provide. Combined with the current value, that is 5000000, this provides hint about the fact that the sequence has probably a too large increment interval.
The code of the function is as follows:
CREATE OR REPLACE FUNCTION seq_check()
RETURNS TABLE( seq_name text, current_value bigint, lim bigint, remaining bigint )
AS $CODE$
DECLARE
query text;
schemaz name;
seqz name;
seqid oid;
BEGIN
FOR schemaz, seqz, seqid IN SELECT n.nspname, c.relname, c.oid
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'S' --sequence
LOOP
RAISE DEBUG 'Inspecting %.%', schemaz, seqz;
query := format( 'SELECT ''%s.%s'', last_value, s.seqmax AS lim, (s.seqmax - last_value) / s.seqincrement AS remaining FROM %I.%I, pg_sequence s WHERE s.seqrelid = %s',
quote_ident( schemaz ),
quote_ident( seqz ),
schemaz,
seqz,
seqid );
RAISE DEBUG 'Query [%]', query;
RETURN QUERY EXECUTE query;
END LOOP;
END
$CODE$
LANGUAGE plpgsql
STRICT;
pg_sequence and data extracted directly from pg_class. The function iterates on all sequences within the system, and this means the function must run with administrator privileges.
I use this handy function to check the status on other machines, and quite frankly I’ve not yet come to
remaining being near to zero, therefore I can sleep well at night:
=# select * from seq_check() order by remaining;
seq_name | current_value | lim | remaining
---------------------------|---------------|---------------------|---------------------
t.root_pk_seq | 201338 | 2147483647 | 2147282309
respi.rosseni_tmp_pk_seq | 16673 | 2147483647 | 2147466974
respi.pull_status_pk_seq | 14603 | 2147483647 | 2147469044
respi.tipo_rossene_pk_seq | 8 | 2147483647 | 2147483639
respi.root_pk_seq | 140509487 | 9223372036854775807 | 9223372036714266320
cron.jobid_seq | 1 | 9223372036854775807 | 9223372036854775806