在PostgreSQL中，如果插入失败，有没有避免调用nextval()的方法？

Question

在PostgreSQL数据库中，我有一张带有主键和另一个需要唯一的字段的表。

  CREATE TABLE users (
    id      INTEGER PRIMARY KEY DEFAULT nextval('groups_id_seq'::regclass),
    name    VARCHAR(255) UNIQUE NOT NULL
  );

  INSERT users (name) VALUES ('foo');
  INSERT users (name) VALUES ('foo');
  INSERT users (name) VALUES ('bar');

第二次插入失败了，但序列groups_id_seq已经增加，因此当添加'bar'时，它会在ID编号中留下一个间隙。

是否有一种方法告诉PostgreSQL只有在满足其他约束条件时才获取下一个值，或者我应该先使用SELECT检查名称是否不重复？这仍然不能保证缺口的缺乏，但至少可以将它们减少到极少数情况，即当有其他进程尝试同时插入相同名称时。

- szabgab

---

参见：https://dev59.com/i2kw5IYBdhLWcg3wUI1W#9985219 - Craig Ringer

可能是 SQL 表中主键 ID 之间的间隙 的重复问题。 - e4c5

Answer 1

我不这么认为：序列的一个基本特征是可能存在间隙（例如考虑两个并发事务，其中一个执行ROLLBACK）。你应该忽略这些间隙。那么在你的情况下，它们为什么会成为问题呢？

- bortzmeyer

---

其实这不是什么问题，只是我很惊讶看到它，如果大部分可以避免，为什么不投入精力去学习呢？我知道我不能百分之百地避免它。 - szabgab

不，它们无法避免。还有其他情况也会导致序列之间出现相当大的间隔-只需设计您的应用程序以适应这样一个事实：序列保证会持续递增（直到达到您数据类型的最大值，32位或64位），但不保证是连续的。 - Magnus Hagander

@Magnus：它们可以被避免，但在复杂性方面代价很大（请参见我的答案）。我完全同意，如果可能的话最好设计允许间隙。 - j_random_hacker

Answer 2

如果您需要无缝序列 - 有方法可以实现，但这并不是很简单，而且肯定会慢得多。

此外 - 如果您担心“使用太多id” - 只需将id定义为bigserial即可。

- user80168

Answer 3

这是可能的，但非常麻烦。正如bortzmeyer所说，依赖于序列值连续是危险的，因此最好如果可以的话就保持现状。

如果不能保持现状：

每次访问该表时都可能导致某一行具有某个名称(也就是说，每次对该表进行INSERT，并且如果允许(尽管这是不好的实践)每次可以更改name字段的UPDATE)必须在锁定某些内容的事务内进行。最简单和最低效的选择是使用LOCK users IN EXCLUSIVE MODE锁定整个表(添加最后3个单词可以允许其他进程并发读取访问，这是安全的)。

然而，这是一个非常粗略的锁，如果有许多并发修改users，它将减慢性能；更好的选择是锁定另一个必须已经存在的相应表中的单个相应行。可以使用SELECT ... FOR UPDATE锁定此行。这只有在使用"子"表与另一个"父"表具有FK依赖关系时才有意义。

例如，暂时假设我们实际上正在尝试安全地为customer创建新的orders，并且这些订单以某种方式具有标识名称。(我知道，这是个糟糕的例子...) orders具有对customers的FK依赖关系。然后，为了防止在给定客户的两个订单中创建相同的名称，可以执行以下操作：

BEGIN;

-- Customer 'jbloggs' must exist for this to work.  
SELECT 1 FROM customers
WHERE id = 'jbloggs'
FOR UPDATE

-- Provided every attempt to create an order performs the above step first,
-- at this point, we will have exclusive access to all orders for jbloggs.
SELECT 1 FROM orders
WHERE id = 'jbloggs'
AND order_name = 'foo'

-- Determine if the preceding query returned a row or not.
-- If it did not:
INSERT orders (id, name) VALUES ('jbloggs', 'foo');

-- Regardless, end the transaction:
END;

请注意，仅使用SELECT ... FOR UPDATE锁定users中对应的行是不够的。如果该行不存在，则可能会有多个并发进程同时报告该行不存在，然后尝试同时插入，导致事务失败，从而出现序列间隙。

任何一种锁定方案都可以使用；重要的是任何尝试创建具有相同名称的行的人都必须尝试锁定相同的对象。

- j_random_hacker