序列有间隙以允许并发插入。尝试避免间隙或重新使用已删除的 ID 会产生可怕的性能问题。请参阅PostgreSQL 维基常见问题解答 https://wiki.postgresql.org/wiki/FAQ#Why_are_there_gaps_in_the_numbering_of_my_sequence.2FSERIAL_column.3F_Why_aren.27t_my_sequence_numbers_reused_on_transaction_abort.3F.
PostgreSQLSEQUENCEs http://www.postgresql.org/docs/current/static/sql-createsequence.html用于分配ID。这些只会不断增加,并且不受通常的事务回滚规则的约束,以允许多个事务同时获取新的 ID。这意味着如果事务回滚,这些 ID 将被“丢弃”;没有保留“免费”ID 列表,只有当前的 ID 计数器。如果数据库非正常关闭,序列通常也会增加。
合成密钥 (ID) 是无意义的反正。它们的顺序并不重要,它们唯一重要的属性是唯一性。您无法有意义地测量两个 ID 之间的“距离”有多远,也无法有意义地判断一个 ID 是否大于或小于另一个 ID。你所能做的就是说“等于”或“不等于”。其他任何事情都是不安全的。你不应该关心差距。
如果您需要一个重复使用已删除 ID 的无缝序列,您可以拥有一个,只需为此放弃大量性能 - 特别是,您不能在INSERT
根本不需要,因为您必须扫描表以查找最低的可用 ID,锁定表以进行写入,这样其他事务就无法声明相同的 ID。尝试搜索“postgresql 无间隙序列”。
最简单的方法是使用计数器表和获取下一个 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';
Usage:
INSERT INTO dummy(id, blah)
VALUES ( get_next_id('thetable_id_counter','last_id'), 42 );
请注意,当一个打开的事务已获得 ID 时,所有其他尝试调用get_next_id
将阻塞,直到第一个事务提交或回滚。对于无间隙 ID,这是不可避免的,并且是设计使然。
如果想在一个表中存储多个不同用途的计数器,只需在上述函数中添加一个参数,在计数器表中添加一列,并添加一个WHERE
条款至UPDATE
与添加的列的参数相匹配。这样您就可以拥有多个独立锁定的计数器行。做not只需为新计数器添加额外的列即可。
此功能不会重新使用已删除的 ID,它只是避免引入间隙。
要重复使用 ID,我建议...不要重复使用 ID。
如果您确实必须这样做,您可以通过添加ON INSERT OR UPDATE OR DELETE
感兴趣的表上的触发器,将已删除的 ID 添加到空闲列表侧表,并在删除它们时将其从空闲列表表中删除INSERT
编辑。治疗一个UPDATE
as a DELETE
随后是一个INSERT
。现在修改上面的 ID 生成函数,使其执行SELECT free_id INTO next_value FROM free_ids FOR UPDATE LIMIT 1
如果找到的话,DELETE
是那行。IF NOT FOUND
像平常一样从生成器表中获取一个新的 ID。这是先前函数的未经测试的扩展,以支持重用:
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;