database - 将序列更新为下一个在 PostgresQL 中可用

Question

好的，我正在用PostgresQL做用户数据库，我已经用了一两个月了，但不是很熟悉。目前，每个注册的用户都通过 PostgresQL 序列自动分配一个唯一的 ID。这一直是惯例，并且一直运作良好，但是......我收到来自客户的请求，要求能够手动输入用户的某些 ID。我预计不会超过 300 个用户，因此我为手动输入的 ID 保留了 ID 500-600。 (如果他们在线注册，他们会得到自动递增的数字，很可能不会超过 300。如果他们需要手动 ID，他们只会获得一个介于 500 和 600 之间的预先确定的 ID。)是的，我 99.9999% 确定不会有超过 300 个“自动识别”用户。

我真的很想能够提供我想要的任何手动 ID，然后如果他们在线注册并且 ID 已经被使用，他们将获得下一个可用的 ID。我意识到这违背了序列的目的，但是，我不确定我的其他选择是什么。我只能提供一定数量的 ID，所以如果可能的话，我宁愿不只是“max+1”——我想“填补空白”。我相当确定答案在于手动输入 ID 时更新序列的某个地方，但这听起来像个坏主意，不知道为什么。

如果这不是一个好主意，就说——“嘿，你是个白痴。谁让你负责数据库的？”我们都会继续我们的一天。感谢您的宝贵时间。

Answer 1

我可能会完全DROP 序列并ALTER TABLE 从列中删除默认值。然后，在 INSERT 期间，我将编写如下内容:

BEGIN;
LOCK TABLE users IN EXCLUSIVE MODE;
INSERT INTO users (user_id, blah, blah)
VALUES (
   coalesce(
       requested_id_or_null_if_none_supplied,
       (SELECT coalesce(max(user_id),0) FROM users)+1
   ),
   'blah',
   'blah'
);
COMMIT;

这有不好的并发性。在任何给定时刻，只有一个事务可以插入用户。考虑到您正在处理的交易量，只要您保持交易简短，就应该完全没问题。

考虑添加一个用作公共(public)显示标识符 的新字段，与数据库的内部主键 分开。使用此字段显示客户的目的。让他们把他们想要的任何东西放进去，只需将它声明为 UNIQUE 作为合适的唯一键的一部分。

如果这样做，您可以使用相同的序列保持基础编号分配不变，这样您就不必更改在数据库中其他地方引用用户的方式。您不必更改应用程序其余部分在内部引用用户的方式。显示用户标识符时，只需查找显示的“用户号”并使用即可。当用户输入用户号时，查找匹配行的主键。用户编号仅作为最终用户的标识符用于输入/输出，不会在数据库的其余部分中引用。

这就是为什么数据库/应用程序设计智慧强调您通常不应向用户公开生成的 key 。最终，如果他们能看到它们，他们将不可避免地希望能够用“有趣”的结果来改变它们。更容易给他们自己的公共(public)标识符来玩。如果他们突然决定它应该是字母数字的，但只能在满月的星期二 - 没关系，你可以这样做。

一旦您将“显示标识符”和“唯一内部行键”拆分成不同的东西，您就可以自由地使用显示标识符做任何您想做的事情，包括轻松地让他们更改它。