PostgreSQL 无缝序列

序列具有间隙，允许并发插入。试图避免间隙或重复使用已删除的 ID 会产生可怕的性能问题。请参阅 PostgreSQL wiki 常见问题解答。

PostgreSQL SEQUENCE用于分配 ID。这些只会增加，并且它们不受通常的交易回滚规则的约束，以允许多个交易同时获取新 ID。这意味着，如果交易回滚，这些 ID 将被“丢弃”;没有保留“免费”ID 列表，只有当前的 ID 计数器。如果数据库以不干净的方式关闭，序列通常也会递增。

无论如何，合成密钥 （ID） 都毫无意义。它们的顺序并不重要，它们唯一重要的属性是唯一性。你无法有意义地衡量两个 ID 的“距离”，也无法有意义地说出一个 ID 是大于还是小于另一个。你所能做的就是说“相等”或“不相等”。其他任何事情都是不安全的。你不应该关心差距。

如果你需要一个重复使用已删除 ID 的无间隙序列，你可以有一个，你只需要为它放弃大量的性能 - 特别是，你根本不能在 s 上有任何并发性，因为你必须扫描表以查找最低的可用 ID，锁定表进行写入，这样其他事务就不能声明相同的 ID。 尝试搜索“postgresql gapless sequence”。INSERT

最简单的方法是使用计数器表和获取下一个 ID 的函数。这是一个通用版本，它使用计数器表来生成连续的无间隙 ID;不过，它不会重用 ID。

CREATE TABLE thetable_id_counter ( last_id integer not null );
INSERT INTO thetable_id_counter VALUES (0);

CREATE OR REPLACE FUNCTION get_next_id(countertable regclass, countercolumn text) RETURNS integer AS $$
DECLARE
    next_value integer;
BEGIN
    EXECUTE format('UPDATE %s SET %I = %I + 1 RETURNING %I', countertable, countercolumn, countercolumn, countercolumn) INTO next_value;
    RETURN next_value;
END;
$$ LANGUAGE plpgsql;

COMMENT ON get_next_id(countername regclass) IS 'Increment and return value from integer column $2 in table $1';

用法：

INSERT INTO dummy(id, blah) 
VALUES ( get_next_id('thetable_id_counter','last_id'), 42 );

请注意，当一个打开的事务获得 ID 时，所有其他尝试调用的事务都将被阻止，直到第一个事务提交或回滚。这是不可避免的，对于无缝 ID，并且是设计使然。get_next_id

如果要在一个表中存储多个用于不同目的的计数器，只需在上述函数中添加一个参数，在计数器表中添加一列，并在 中添加一个与该参数匹配的子句即可添加添加到添加的列中。这样，您可以拥有多个独立锁定的计数器行。不要只为新计数器添加额外的列。WHEREUPDATE

此功能不会重复使用已删除的 ID，它只是避免引入间隙。

要重复使用 ID，我建议......不重复使用 ID。

如果确实必须，可以通过在感兴趣的表上添加一个触发器来执行此操作，该触发器将已删除的 ID 添加到自由列表侧表中，并在编辑后将其从自由列表表中删除。将 视为 后跟 .现在修改上面的 ID 生成函数，以便它执行该行，如果找到，则执行该行。 像往常一样从生成器表中获取新 ID。下面是支持重用的先前函数的未经测试的扩展：ON INSERT OR UPDATE OR DELETEINSERTUPDATEDELETEINSERTSELECT free_id INTO next_value FROM free_ids FOR UPDATE LIMIT 1DELETEIF NOT FOUND

CREATE OR REPLACE FUNCTION get_next_id_reuse(countertable regclass, countercolumn text, freelisttable regclass, freelistcolumn text) RETURNS integer AS $$
DECLARE
    next_value integer;
BEGIN
    EXECUTE format('SELECT %I FROM %s FOR UPDATE LIMIT 1', freelistcolumn, freelisttable) INTO next_value;
    IF next_value IS NOT NULL THEN
        EXECUTE format('DELETE FROM %s WHERE %I = %L', freelisttable, freelistcolumn, next_value);
    ELSE
        EXECUTE format('UPDATE %s SET %I = %I + 1 RETURNING %I', countertable, countercolumn, countercolumn, countercolumn) INTO next_value;
    END IF;
    RETURN next_value;
END;
$$ LANGUAGE plpgsql;