在 Postgresql 中环绕后，将主键与循环序列一起使用会导致“重复键违反唯一约束”错误

问：

背景/背景

如果我定义一个序列和一个表，如下所示：

CREATE SEQUENCE public.test_sequence
    CYCLE
    MINVALUE 1
    MAXVALUE 2147483647;

CREATE TABLE public.test_table
(
    id integer NOT NULL DEFAULT nextval('test_sequence'::regclass),
    name text,
    CONSTRAINT test_table_pkey PRIMARY KEY (id)
);

并插入大量值。最终，我将到达序列的末尾，并且由于 CYCLE 关键字，nextval 将返回序列的开头并再次返回 1、2 等。不幸的是，如果我正在插入并且旧记录仍然存在相同的 id，那么我最终会遇到冲突，插入调用将导致“重复键违反唯一约束”错误。我对这个结果感到有些惊讶，因为我做出了明显错误的假设，认为“nextval”稍微更聪明一些，意味着下一个自由的 id 值，这在这种情况下显然更有用。

根据以下线程，似乎我可能不是唯一一个做出这种假设的人，其他人可能会遇到同样的问题：如何避免 nextval（） 在包装时与现有条目发生冲突？

显然，此问题有几种可能的解决方法：

1. 迁移到更大的数据类型，例如 bigint。
2. 改用 UUID 值。
3. 将 ON CONFLICT DO NOTHING 子句引入 INSERT。
4. 将 ON CONFLICT DO UPDATE 子句引入 INSERT。
5. 添加一个 BEFORE INSERT 触发器以递增序列的 nextval，直到我们到达一个空闲的 nextval。
6. 等。？

对于那些感兴趣的人，以下文章探讨了上述几个选项之间的差异： 选择 Postgres 主键

出于各种原因，无论好坏，我选择了选项 5 来解决这个问题。如果我们能避免争论哪个是更好的解决方案，我将不胜感激。特别是考虑到最佳选择可能取决于一个人的特殊要求。但是，如果还有其他我上面没有考虑的独特解决方法，我会很感兴趣？

进一步明确我们的要求。我们在嵌入式设备上运行，空间是有限的。我们最近空间用完了，我们才刚刚开始填充我们的主要桌子。因此，任何增加空间使用率的选择都不一定是我们的最佳选择。我们目前对我们的时间没有任何限制，所以我们目前可以在这里承受性能损失。因此，为了满足我们的需要，我们已经选择了备选方案5。对于其他人的要求，上述任何其他选项可能确实是更好的选择。

问题

考虑到我们已经选择了选项 5。实现这一点的最佳方法是什么？

我有兴趣获得有关我为处理这种情况而编写的数据库触发器的反馈。具体说来：

• 如果有人注意到我使用的锁定解决方案存在并发问题？

• 如果有更好的方法来编写此触发器？

• 等。

• 这种方法有哪些陷阱/脚枪？

  A1 - @Ry- 提出了一个很好的观点，即密钥重用可能会导致一些竞争条件（如果我在这里正确解释的话）。例如，当您有 3 个事务时，一个特定场景是：第一个事务对 id 5 执行 DELETE，第二个事务稍后对 id 5 执行 INSERT，第三个事务尝试对 id 5 执行 UPDATE。根据第三笔交易到达的时间（在其他两笔交易之前或之后），它将更新旧记录或新记录，其中一项可能不是预期的更新。因此，种族和可能的数据损坏。我想这种特殊情况可以通过在执行“更新”（在同一事务中）之前始终执行“SELECT FOR UPDATE”来检查以前的状态来缓解。但是，这显然会产生进一步的性能开销，并有可能使用户忘记这样做。

A2 - @Atmo提出了一个很好的观点，即此函数的性能会受到损害，具体取决于换行后的值的满度（或稀疏程度）。如果没有更多的免费 ID，这将在最坏的情况下达到高潮，如果您正在锁定，您将死锁，或者如果您不锁定，则会无限循环（至少在其他人删除记录之前）。应该注意的是，这个时间惩罚是线性时间，因为它只需要检查每条记录一次，并且只会击中索引而不是记录。此方案的缓解技术包括定期清理（如果可能）以维护表中的条目较少，并检查触发器中的换行以避免死锁/循环。

A3 - 循环/换行 id 值意味着您不能再依赖 id 值进行按时间顺序排序/排序（如果您碰巧这样做的话）。

以下是相关的触发器代码：

CREATE OR REPLACE FUNCTION fix_id_collision()
RETURNS TRIGGER AS $$
DECLARE
  found_id integer;
  column_default text;
BEGIN
  -- Loop until we find a free id value.
  LOOP
    -- Check if the id already exists in the table.
    -- Use row level "FOR UPDATE" locking to hopefully ensure
    -- that concurrent INSERT queries don't receive the same id
    -- and collide.
    EXECUTE format('SELECT id FROM %I.%I WHERE id = %L FOR UPDATE', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.id) INTO found_id;
    IF found_id IS NULL THEN
      RETURN NEW;
    END IF;
    EXECUTE format('SELECT column_default FROM
information_schema.columns WHERE table_schema=%L AND table_name=%L', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO column_default;
    EXECUTE format('SELECT %s', column_default) INTO NEW.id;
  END LOOP;
END;
$$
LANGUAGE plpgsql;

还有一些代码来为我的数据库中的所有表安装它：

CREATE OR REPLACE FUNCTION install_id_collision_triggers()
RETURNS VOID
AS $$
DECLARE
  tbl_schema text;
  tbl_name text;
BEGIN
  FOR tbl_schema, tbl_name IN SELECT table_schema, table_name FROM information_schema.columns WHERE column_name='id'
  LOOP
    EXECUTE format('DROP TRIGGER IF EXISTS id_collision_trigger_%s ON %I.%I', tbl_name, tbl_schema, tbl_name);
    EXECUTE format('CREATE TRIGGER id_collision_trigger_%s BEFORE INSERT ON %I.%I FOR EACH ROW EXECUTE FUNCTION fix_id_collision()', tbl_name, tbl_schema, tbl_name);
  END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT install_id_collision_triggers();

PS 我已经知道我可以在最近的 PostgreSQL 版本中执行“CREATE OR REPLACE TRIGGER”，但我碰巧使用旧版本进行测试，所以请原谅额外的步骤。

跟进

@klin在这里提出了一个有趣的观点，即由于数据类型对齐问题，使用 integer 和 bigint/bigserial 数据类型（在 64 位系统上）之间可能没有空间差异，如他的以下帖子中所述： 基准测试：PostgreSQL 上的 bigint vs int 。我运行了一个非常非常基本的测试，并确认在选择整数与 bigint 之间空间使用量只有非常小的增加。在我的情况下，bigint 的空间使用量增加了大约 4%。因此，在选择这种特定方法时，并没有像我最初想象的那样节省空间，我可以建议不要使用这种解决方法。特别是考虑到它引入的数据损坏的可能性。