Rubyonrails PostgreSQL 无间隙序列

序列有间隙，允许同时插入.试图避免漏洞或重复使用已删除的ID会造成可怕的性能问题.看PostgreSQL wiki FAQ.

PostgreSQL SEQUENCEs用于分配ID.它们只会不断增加，并且不受常见事务回滚规则的约束，以允许多个事务同时获取新ID.这意味着，如果事务回滚，这些ID将被"丢弃"；没有"免费"ID的列表，只有当前的ID计数器.如果数据库不干净地关闭，序列通常也会增加.

合成密钥(ID)无论如何都是meaningless.它们的顺序并不重要，唯一重要的特性是唯一性.你不能有意义地测量两个ID之间的"距离"，也不能有意义地说一个ID大于或小于另一个ID.你所能做的就是说"平等"或"不平等".其他任何东西都是不安全的.你不应该在意差距.

如果你需要一个无间隙序列来重复使用被删除的ID，你可以有一个，你只需要为它放弃大量的性能——特别是，你根本不能在INSERT上有任何并发性，因为你必须扫描表以寻找最低的可用ID，锁定表进行写入，以便其他事务不能声明相同的ID.请try 搜索"postgresql无间隙序列".

最简单的方法是使用计数器表和获取下一个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时，try 调用get_next_id的所有其他事务将被阻止，直到第一个事务提交或回滚.这对于无间隙ID来说是不可避免的，而且是出于设计.

如果您想在一个表中存储多个用于不同目的的计数器，只需在上述函数中添加一个参数，在计数器表中添加一列，并在UPDATE中添加一个WHERE子句，使参数与添加的列相匹配.这样就可以有多个独立锁定的计数器行.只需为新计数器添加额外的列即可.

此函数不会重复使用已删除的ID，它只是避免引入间隙.

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

如果确实需要，可以在感兴趣的表上添加一个ON INSERT OR UPDATE OR DELETE触发器，将已删除的ID添加到自由列表边表中，并在删除它们时将其从自由列表表中删除.将UPDATE视为DELETE，然后是INSERT.现在修改上面的ID生成函数，使其执行SELECT free_id INTO next_value FROM free_ids FOR UPDATE LIMIT 1，如果找到，则执行DELETE.IF NOT FOUND像往常一样从生成器表中获取新ID.以下是Previor函数的一个未经测试的扩展，以支持重用:

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;