一月 24, 2024
摘要：在本教程中，您将学习如何在 PostgreSQL 中检查即将用尽的序列。
目录
介绍
在定义一个新表时，使用 4 字节整数作为主键的情况并不少见。如果要表示的值超出 4 个字节可允许的最大值，这可能会发生问题。如果达到了一个序列的上限，您可能会在日志中看到如下所示的错误：
ERROR: nextval: reached maximum value of sequence "test_id_seq" (2147483647)
此场景中的错误代码为2200H：sequence_generator_limit_exceeded。当序列用尽时，可以在应用程序中捕获该异常。
哪些序列是有风险的？
序列的元数据与关联的表分开存储，因此相对容易访问，而无需单独检查每个表。系统表 pg_sequence 包含有关序列的信息。有些关于序列的信息（如名称和模式）在系统表 pg_class 中。序列默认的命名可以清楚地显示序列与哪个表/列相关联，尽管你也可以创建一个返回实际表/列名称的查询。
我们可以检查数据库中的所有序列还剩下多少可用的值。这可以通过每个序列已经用过的值和总共可能的值的百分比来衡量。一般来说，当百分比超过 85% 时，应该触发一条警告，超过 95% 后，问题就很严重了。
查找即将用尽的序列
以下查询将返回任何自动递增的列、它拥有的序列对象、列和序列对象的数据类型，以及序列值超出序列或列类型最大值前的百分比：
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;
为了显示此查询的实际效果，让我们来设置一个带有主键的测试表，其中的序列已被人为地推进到 20 亿：
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)
现在，当运行上面的查询来计算整数溢出百分比时，我们可以看到列和序列的数据类型都是integer，并且由于序列的下一个值是 20 亿，因此它在可允许的范围里面已经达到了 93%：
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)