gpt4 book ai didi

ruby-on-rails - PostgreSQL 无缝序列

转载 作者:行者123 更新时间:2023-11-29 13:38:41 27 4
gpt4 key购买 nike

我正在从 MySql 迁移到 Postgres,我注意到当您从 MySql 中删除行时,这些行的唯一 ID 会在您创建新行时重新使用。使用 Postgres,如果您创建行并删除它们,则不会再次使用唯一 ID。

在 Postgres 中有这种行为的原因吗?在这种情况下,我可以让它更像 MySql 吗?

最佳答案

序列有间隙以允许并发插入。试图避免间隙或重新使用已删除的 ID 会产生可怕的性能问题。查看PostgreSQL wiki FAQ .

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

无论如何,合成 key (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';

用法:

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

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

如果要在一个表中存储多个不同用途的计数器,只需要在上面的函数中添加一个参数,在计数器表中添加一列,并添加一个WHERE即可。 UPDATE 的条款将参数与添加的列相匹配。这样您就可以拥有多个独立锁定的计数器行。 不要为新计数器添加额外的列。

此函数不会重新使用已删除的 ID,它只是避免引入间隙。

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

如果你真的需要,你可以通过添加 ON INSERT OR UPDATE OR DELETE 来实现在感兴趣的表上触发,将已删除的 ID 添加到空闲列表边表,并在它们为 INSERT 时将它们从空闲列表中删除编辑。对待UPDATE作为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;

关于ruby-on-rails - PostgreSQL 无缝序列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/58705444/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com