logo标签列表

尽管我添加了"on conflict do nothing"，但是Postgres中的序列仍在增加。

postgresqlauto-incrementupsert
52

我正在使用Postgres 9.5，并且在这里看到一些奇怪的东西。

我有一个cron job每5分钟运行一次，执行一个SQL语句，如果不存在，则添加一系列记录。

INSERT INTO 
   sometable (customer, balance)
VALUES
    (:customer, :balance)
ON CONFLICT (customer) DO NOTHING

sometable.customer是一个主键（text）

sometable结构如下：
id：连续的编号
customer：文本
balance：大整数

现在似乎每次运行此作业时，id字段会自动静默地增加1。所以下一次，如果我真的添加一个字段，它将比我的最后一个值高出数千个数字。我原以为这个查询会检查冲突，如果有冲突，则不做任何操作，但目前看来它似乎会尝试插入记录，增加id然后停止。

有什么建议吗？

7
这是出于设计考虑：http://postgresql.nabble.com/BUG-14126-INSERT-ON-CONFLICT-DO-NOTHING-auto-increments-serial-primary-key-when-no-insert-happens-td5902224.html 但您不需要担心这些间隙，它们并不是问题。序列仅保证不会生成两次相同的数字。 - user330315
1
如果由于某种原因，您实际上需要一个无间隙的序列（罕见的要求），那么Postgres的SERIAL/SEQUENCE支持不是您应该使用的东西。请参见Craig Ringer的此答案。 - IMSoP
4
如果您基于哈希键对另一个表进行分区，那么序列中的间隙会成为一个问题。无法保证平衡的分区集合。我现在正在面临这个问题。 - egalot
我觉得应该有人向你指出UUID密钥作为序列密钥的替代方案。它们很可能会解决你的问题。 - sage88
3
这些空缺对我来说不是问题，但我想使用 "smallserial"，然而插入冲突使得 "smallint" 的最大大小被超出，尽管实际上少于32k行。由于有32个并行进程加载数据，这些冲突会在几秒钟内达到最大值...（由于该表格具有数十亿行，因此希望将约100个字符的varchar转换为 "smallint"，因为只有约20k个不同的值） - J. Dimeo
显示剩余2条评论
4个回答
52
这感觉对你来说很奇怪，是因为你认为计数器上的递增部分是插入操作的一部分，因此“什么也不做”应该意味着“不要递增任何东西”。 你正在想象这样的操作流程：
  1. 检查要插入的值是否符合约束条件
  2. 如果检测到重复项，则中止操作
  3. 递增序列
  4. 插入数据
但事实上，递增必须在尝试插入之前发生。在Postgres中，一个SERIAL列是作为SEQUENCE绑定执行nextval()函数的DEFAULT。在DBMS可以处理数据之前，它必须有一个完整的列集，因此操作的顺序如下：
  1. 解析包括递增序列在内的默认值
  2. 检查要插入的值是否符合约束条件
  3. 如果检测到重复项，则中止操作
  4. 插入数据
如果自动递增字段本身存在重复键，则可以直观地看出这一点。
CREATE TABLE foo ( id SERIAL NOT NULL PRIMARY KEY, bar text );
-- Insert row 1
INSERT INTO foo ( bar ) VALUES ( 'test' );
-- Reset the sequence
SELECT setval(pg_get_serial_sequence('foo', 'id'), 0, true);
-- Attempt to insert row 1 again
INSERT INTO foo ( bar ) VALUES ( 'test 2' )
     ON CONFLICT (id) DO NOTHING;

很明显，在递增序列之前无法确定是否存在冲突，因此“什么也不做”必须在递增之后进行。

很好的解释！你知道这个在PostgreSQL文档中是否有记录吗？我搜索过但没找到。 - GG.
1
@GG。Serial伪类型在此处有文档记录，并解释了它实际上是作为列上的默认值实现的，调用一个函数；但是ON CONFLICT子句的文档没有明确提到默认值将首先被解析。也许您可以提出编辑建议。 - IMSoP
1
很好的答案。我注意到我们的主键中有很大的间隔。我不确定是否是我们自己犯了一些错误，但我们正在大量使用ON CONFLICT。所以这解释了这些观察结果。还要注意，序列有一个CYCLE选项，当超过最大值时启用环绕行为。 - Matt Hancock
5

如@a_horse_with_no_name和@Serge Ballesta所说，即使INSERT失败，序列号也总是递增的。

您可以尝试通过更改相应的序列来将序列值“回滚”到使用的最大id：

SELECT setval('sometable_id_seq', MAX(id), true) FROM sometable;
1
请注意，将true作为setval的第三个参数传递与不传递第三个参数是相同的。setval（'sometable_id_seq'，MAX（id），true）等同于setval（'sometable_id_seq'，MAX（id））。 - BrDaHa
2
正如@a_horse_with_no_name所说，这是有意设计的。串行类型字段是通过序列在后台实现的，由于明显的原因，一旦您从序列中获取了新值，就无法回滚上一个值。想象以下场景：
  • 序列为n
  • A需要一个新值：得到n+1
  • 在并发事务B中需要一个新值：得到n+2
  • 由于任何原因A回滚了其事务——您会觉得重置序列安全吗？
这就是为什么序列（和串行字段）只记录在回滚的事务中可能出现返回值空洞的情况。仅保证唯一性。
2
是的，谢谢，我明白了。但在我看来，如果查询中没有提供任何内容，则+1 没有意义。应该在实际添加之前检查是否需要添加任何内容。此外，我担心会出现间隙。这个任务每 5 分钟运行一次... - Christian
1
我能做的唯一事情就是自己检查是否需要通过选择所有记录来添加此记录 :-/ - Christian
2
@Christian：你没有必要做任何事情。只需忽略这些间隔。ID 值存在（较大的间隔）是没有问题的。 - user330315
7
@a_horse_with_no_name，除非你在短时间内插入足够的数据以使' SERIAL'溢出。我原本计划针对一个每秒运行约50次的查询进行此操作，但我将在大约1.36年后用完唯一值。我希望有一条命令类似于“SELECT id FROM table WHERE uniqueCol ='val' ON EMPTY INSERT INTO table(uniqueCol) VALUES('val') ON CONFLICT(uniqueCol) DO UPDATE SET uniqueCol = EXCLUDED.uniqueCol RETURNING id”。这将显着减少浪费的id数量。 - Jeff G
这似乎是使用UUID而不是序列键的用例。 - sage88
-4
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接