每次更新时PostgreSQL自动递增

Question

每次我执行INSERT或UPSERT（ON CONFLICT UPDATE）时，每个表上的增量列都会增加之前的更新数量。

例如，如果我有这张表:

id int4
title text
description text
updated_at timestamp
created_at timestamp

然后运行这些查询：

INSERT INTO notifications (title, description) VALUES ('something', 'whatever'); // Generates increments ID=1

UPDATE notifications title='something else' WHERE id = 1; // Repeat this query 20 times with different values.

INSERT INTO notifications (title, description) VALUES ('something more', 'whatever again'); // Generates increments ID=22

这是一个相当大的问题。我们运行的脚本每天处理100000个通知。这会在每一次插入之间产生大约10000行的间隙，所以我们可能从100行开始，但当我们达到1000行时，最后一行的主键ID值会自动增加到100000以上。

如果这种情况继续下去，我们将很快用完表上的自动增量值。

我们的PostgreSQL服务器配置错误吗？使用Postgres 9.5.3。

我正在使用Eloquent Schema Builder（例如$table-




Answer 1

您可以通过在插入命令之前运行以下命令将自动增量列重置为最大插入值：

SELECT setval('notifications_id_seq', MAX(id)) FROM notifications;

Answer 2

匿名用户

无论插入成功与否，只要尝试插入，序列就会增加。简单的更新（如您的示例）不会增加它，但冲突更新上的插入会增加它，因为插入是在更新之前尝试的。

一种解决方案是将 id 更改为“比金特”。另一种方法是不要使用序列并自己管理它。另一个是手动更新：

with s as (
    select id
    from notifications
    where title = 'something'
), i as (
    insert into notifications (title, description)
    select 'something', 'whatever'
    where not exists (select 1 from s)
)
update notifications
set title = 'something else'
where id = (select id from s)

这假设标题是唯一的。