January 24, 2024
Summary: in this tutorial, you will learn how to check sequences near to be exhausted in PostgreSQL.
Table of Contents
Introduction
It is not uncommon to use a 4-byte integer as a primary key when defining a new table. This can cause problems if the value to be represented is more than 4-bytes can hold. If a sequence’s limit is reached you might see an error in your logs that looks like this:
ERROR: nextval: reached maximum value of sequence "test_id_seq" (2147483647)
The error code in this scenario is 2200H: sequence_generator_limit_exceeded. You can catch the exception in the application when the sequence is exhausted.
Which sequences are risky?
Sequence metadata is stored separately from the associated tables, so is relatively easy to access without having to check each table individually. The catalog pg_sequence contains information about sequences. Some of the information about sequences, such as the name and the schema, is in pg_class. The default sequence naming makes it obvious which table/column the sequence is associated with, though it’s also possible to create a query which returns the actual table/column name as well.
We can check how much room is left on all sequences in the database. This can be measured as the percent of total possible values and values that have been used for each sequence. Generally, when the percentage exceeds 85%, it’s a warning, after exceeds 95%, it’s critical.
Find sequences near to be exhausted
The following query will identify any auto-incrementing columns, which sequence object it owns, data types of the column and sequence object, and percent until the sequence value exceeds the sequence or column data type:
SELECT
seq.relname AS sequence,
format_type(s.seqtypid, NULL) sequence_datatype,
CONCAT(tbl.relname, '.', att.attname) AS owned_by,
format_type(att.atttypid, atttypmod) AS column_datatype,
pg_sequence_last_value(seq.oid::regclass) AS last_sequence_value,
TO_CHAR((
CASE WHEN format_type(s.seqtypid, NULL) = 'smallint' THEN
(pg_sequence_last_value(seq.relname::regclass) / 32767::float)
WHEN format_type(s.seqtypid, NULL) = 'integer' THEN
(pg_sequence_last_value(seq.relname::regclass) / 2147483647::float)
WHEN format_type(s.seqtypid, NULL) = 'bigint' THEN
(pg_sequence_last_value(seq.relname::regclass) / 9223372036854775807::float)
END) * 100, 'fm9999999999999999999990D00%') AS sequence_percent,
TO_CHAR((
CASE WHEN format_type(att.atttypid, NULL) = 'smallint' THEN
(pg_sequence_last_value(seq.relname::regclass) / 32767::float)
WHEN format_type(att.atttypid, NULL) = 'integer' THEN
(pg_sequence_last_value(seq.relname::regclass) / 2147483647::float)
WHEN format_type(att.atttypid, NULL) = 'bigint' THEN
(pg_sequence_last_value(seq.relname::regclass) / 9223372036854775807::float)
END) * 100, 'fm9999999999999999999990D00%') AS column_percent
FROM
pg_depend d
JOIN pg_class AS seq ON seq.relkind = 'S'
AND seq.oid = d.objid
JOIN pg_class AS tbl ON tbl.relkind = 'r'
AND tbl.oid = d.refobjid
JOIN pg_attribute AS att ON att.attrelid = d.refobjid
AND att.attnum = d.refobjsubid
JOIN pg_sequence s ON s.seqrelid = seq.oid
WHERE
d.deptype = 'a'
AND d.classid = 'pg_class'::regclass::oid;
To show this query in action, let’s set up a test table with an integer primary key, where the sequence has been artificially advanced to 2 Billion:
postgres=# create table test(id serial primary key, value integer);
CREATE TABLE
postgres=# select setval('test_id_seq', 2000000000);
setval
------------
2000000000
(1 row)
postgres=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+----------------------------------
id | integer | | not null | nextval('test_id_seq'::regclass)
value | integer | | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
Now when running the query above to find the integer overflow percent, we can see that that the data types for both the column and the sequence are both integer, and since the sequence’s next value is 2 Billion, it is 93% through the acceptable range:
sequence | sequence_datatype | owned_by | column_datatype | last_sequence_value | sequence_percent | column_percent
-------------+-------------------+----------+-----------------+---------------------+------------------+----------------
test_id_seq | integer | test.id | integer | 2000000001 | 93.13% | 93.13%
(1 row)