每次更新时PostgreSQL自动增加 [英] PostgreSQL auto-increment increases on each update

问题描述

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

Every time I do an INSERT or UPSERT (ON CONFLICT UPDATE), the increments column on each table increments by the number of updates that came before it.

对于例如，如果我有此表：

For instance, if I have this table:

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

这是一个很大的问题。我们正在运行的脚本每天处理100,000多个通知。这可能会在每个插入之间造成10,000个数量级的间隙，因此我们可能从100行开始，但是到1,000行时，最后一行的主键ID值会自动递增，超过100000。

This is a pretty big issue. The script we are running processes 100,000+ notifications every day. This can create gaps between each insert on the order of 10,000, so we might start off with 100 rows but by the time we reach 1,000 rows we have an auto-incremented primary key ID value over 100000 for that last row.

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

We will quickly run out of auto-increment values on our tables if this continues.

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

Is our PostgreSQL server misconfigured? Using Postgres 9.5.3.

我正在使用Eloquent Schema Builder（例如 $ table-> increments（'id'））创建表，我不知道这是否与它有关。

I'm using Eloquent Schema Builder (e.g. $table->increments('id')) to create the table and I don't know if that has something to do with it.

推荐答案

A无论插入成功与否，只要尝试插入，序列都会增加。一个简单的 update （如您的示例）不会使它递增，但是在冲突更新中插入会因为<$在更新之前尝试c $ c> insert 。

A sequence will be incremented whenever an insertion is attempted regardless of its success. A simple update (as in your example) will not increment it but an insert on conflict update will since the insert is tried before the update.

一种解决方案是将 id 更改为 bigint 。另一个是不要使用序列并自己进行管理。另一种方法是进行手动更新：

One solution is to change the id to bigint. Another is not to use a sequence and manage it yourself. And another is to do a manual upsert:

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)

这假设标题是唯一的。

这篇关于每次更新时PostgreSQL自动增加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！