在PostgreSQL中生成20字节长度的DECIMAL类型序列号

开发者阿泽

2026-5-22

嘿，我注意到你为了生成20字节的超大序列号，用了DECIMAL类型加PL/pgSQL函数的方案——这个思路是对的，但这里有几个关键问题和优化点值得聊聊：

1. 初始记录缺失会直接报错

你的ca_params表如果没有预先插入一条初始值（比如INSERT INTO ca_params VALUES (0);），第一次调用generate_serial()就会触发空值错误，因为SELECT serial+1查不到任何记录，赋值给serial_后后续的更新也会失败。

修复建议：
要么建表后立刻插入初始记录，要么修改函数自动初始化：

CREATE OR REPLACE FUNCTION generate_serial() RETURNS NUMERIC AS $$
DECLARE
    serial_ NUMERIC;
BEGIN
    -- 尝试获取当前序列号并加1，无记录则初始化
    SELECT serial + 1 INTO serial_ FROM ca_params FOR UPDATE;
    IF NOT FOUND THEN
        serial_ := 1;
        INSERT INTO ca_params VALUES (serial_);
    ELSE
        UPDATE ca_params SET serial = serial_;
    END IF;
    RETURN serial_;
END;
$$ LANGUAGE plpgsql;

2. 高并发下会有严重的性能瓶颈

当前方案每次调用函数都会对ca_params的唯一记录加排他锁（FOR UPDATE），高并发场景下所有请求都会排队等锁，这会成为系统的性能瓶颈。

优化思路：
如果业务允许序列号存在一定的“预分配”空间，可以改成批量获取序列号段，比如每次预取100个号，大幅减少锁竞争的频率：

CREATE OR REPLACE FUNCTION generate_serial(p_batch_size INT DEFAULT 100) RETURNS NUMERIC AS $$
DECLARE
    current_serial NUMERIC;
BEGIN
    SELECT serial INTO current_serial FROM ca_params FOR UPDATE;
    UPDATE ca_params SET serial = current_serial + p_batch_size;
    -- 返回当前批次的起始号，业务侧可自行分配到 current_serial + p_batch_size - 1
    RETURN current_serial + 1;
END;
$$ LANGUAGE plpgsql;

3. DECIMAL的精度设置不足

你创建表时用的是无参数的DECIMAL，PostgreSQL会默认用DECIMAL(10,0)，只能存10位十进制数，远达不到20字节对应的数值范围（20字节无符号数约等于48位十进制数）。

正确做法：
建表时明确指定足够的精度，比如：

CREATE TABLE public.ca_params ( serial DECIMAL(50, 0) NOT NULL );

4. 异常场景下的序列号一致性

如果函数执行时事务回滚，SELECT ... FOR UPDATE和UPDATE属于同一个事务，更新会被撤销，所以序列号不会乱跳。但要注意：如果业务侧拿到序列号后自己回滚了事务，已经生成的序列号不会被回退——这是序列生成的常见特性，你需要确认业务是否接受这种“跳号”情况。

5. 可选的替代思路

虽然bigserial只有8字节，但如果你需要20字节的序列号，也可以考虑用多个序列组合生成，或者用bytea存储二进制形式，但你的DECIMAL方案显然更直观易读，只要解决上面的问题就可以稳定使用。

内容的提问来源于stack exchange，提问作者Anton Litvinov