时间戳

首页 > 解决方案 > psql upsert 导致 id 不连续

postgresql - psql upsert 导致 id 不连续

问题描述

我有一个带有 primary_keyid和唯一键的 postgresql (>9.5) 表col。当我使用

INSERT INTO table_a (col) VLUES('xxx') ON CONFLICT(col) DO NOTHING;

要执行 upsert，假设1生成了一个带有 id 的行。

2如果我再次运行sql，什么都不会发生，但实际上会生成并放弃id 。

然后，如果我插入一条新记录，例如，

INSERT INTO table_a (col) VLUES('yyy') ON CONFLICT(col) DO NOTHING;

3将生成另一行带有 id的行，并且 id2被浪费了！

有没有办法避免这种浪费？

标签： postgresqlpsql

解决方案

大概id是一个serial。在引擎盖下，这会导致nextval()来自序列的调用。一个号码nextval()一旦被退回，将永远不会再被退回。并且调用nextval()发生在检查可能的冲突之前。

来自“9.16. 序列操作函数”：

nextval

(...)

重要：为了避免阻塞从相同序列中获取数字的并发事务，nextval操作永远不会回滚；也就是说，一旦获取了一个值，它就被认为是已使用的，并且不会再次返回。即使周围的事务稍后中止，或者调用查询最终没有使用该值也是如此。例如，一个INSERTwithON CONFLICT子句将计算要插入的元组，包括执行任何必需的nextval调用，然后再检测任何可能导致它遵循ON CONFLICT规则的冲突。这种情况会在赋值序列中留下未使用的“漏洞”。因此，PostgreSQL 序列对象不能用于获得“无间隙”序列。

得出的结论是，您的问题的答案是否定的，除非您以某种方式自己生成值，否则无法避免这种情况。

推荐阅读