代码之家 › 专栏 › 技术社区 › szabgab Brandon Fosdick

如果在postgresql中插入失败,有没有办法避免调用nextval()呢?

postgresql sql

szabgab Brandon Fosdick · 技术社区 · 14 年前

在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');

第二次插入失败,但序列组id seq已经递增,因此当添加“bar”时,它会在id号中留下一个空白。

有没有办法告诉postgresql只有在满足其他约束的情况下才能获取下一个值,或者如果名称不重复,我应该首先使用select进行检查?这仍然不能保证没有空白,但至少当有另一个进程试图同时插入相同的名称时,可以将它们的数量减少到罕见的情况

3 回复 | 直到 14 年前

bortzmeyer 14 年前

我不这么认为:序列的一个基本特征是可能存在间隙(想想两个并发事务,其中一个执行回滚)。你应该忽略差距。为什么他们是你的问题?

user80168 14 年前

如果你需要无间隙序列-有办法做到这一点,但它不是微不足道的,而且肯定要慢得多。

另外-如果您担心“使用过多的id”-只需将id定义为bigserial。

Community Egal 7 年前

这样做是可能的,尽管很麻烦。AS bortzmeyer says ,依赖序列中的值是连续的是很危险的,因此如果可以的话,最好保持它们的原样。

如果你不能:

对表的每次访问都可能导致行具有特定的名称(即 INSERT 如果你允许的话 UPDATE 这可能会改变 name 字段)必须这样做 在首先锁定soemthing的事务中 . 最简单、性能最低的选项是使用 LOCK users IN EXCLUSIVE MODE (添加最后3个字允许其他进程并发读取访问,这是安全的)。

但是,这是一个非常粗糙的锁,如果对 users ;更好的选择是锁定另一个表中必须已存在的单个对应行。这一行可以用 SELECT ... FOR UPDATE . 这只有在使用一个“子”表时才有意义,该表对另一个“父”表具有FK依赖关系。

例如,暂时设想一下,我们实际上正在尝试安全地创建新的 orders 对于一个 customer ,并且这些订单不知何故具有识别“名称”的功能。(我知道,可怜的例子……) 命令 具有FK依赖关系 customers . 然后,为了防止为给定客户创建两个同名订单,您可以执行以下操作:

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;

注意它是不足以简单地将相应的行锁定在 用户 具有 选择…用于更新 --如果该行不存在,则多个并发进程可能同时报告该行不存在,然后尝试同时插入,从而导致事务失败,从而导致序列间隙。

两种锁定方案都可以工作;最重要的是 任何试图创建同名行的人都必须尝试锁定同一对象 .