PostgreSQL 教程: 检查即将用尽的序列

一月 24, 2024

摘要:在本教程中,您将学习如何在 PostgreSQL 中检查即将用尽的序列。

介绍

在定义一个新表时,使用 4 字节整数作为主键的情况并不少见。如果要表示的值超出 4 个字节可允许的最大值,这可能会发生问题。如果达到了一个序列的上限,您可能会在日志中看到如下所示的错误:

ERROR:  nextval: reached maximum value of sequence "test_id_seq" (2147483647)

此场景中的错误代码为2200Hsequence_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)