尽管我添加了冲突，但 postgres 中的串行正在增加 [英] serial in postgres is being increased even though I added on conflict do nothing

问题描述

我正在使用 Postgres 9.5 并在这里看到一些有线的东西.

I'm using Postgres 9.5 and seeing some wired things here.

我有一个 cron 作业，每运行 5 分钟就会触发一条 sql 语句，如果不存在则添加记录列表.

I've a cron job running ever 5 mins firing a sql statement that is adding a list of records if not existing.

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

sometable.customer 是主键(文本)

sometable.customer is a primary key (text)

某种结构是:
id:序列号
客户:文本
平衡:bigint

sometable structure is:
id: serial
customer: text
balance: bigint

现在似乎每次运行此作业时，id 字段都会无声地递增 +1.所以下一次，我真的添加了一个字段，它比我上一个值高出数千个数字.我认为此查询会检查是否存在冲突，如果是，则什么也不做，但目前它似乎尝试插入记录，增加 id 然后停止.

Now it seems like everytime this job runs, the id field is silently incremented +1. So next time, I really add a field, it is thousands of numbers above my last value. I thought this query checks for conflicts and if so, do nothing but currently it seems like it tries to insert the record, increased the id and then stops.

有什么建议吗?

推荐答案

这让你觉得奇怪的原因是你认为计数器上的增量是插入操作的一部分，因此DO NOTHING"应该意思是不要增加任何东西".你在想象这个:

The reason this feels weird to you is that you are thinking of the increment on the counter as part of the insert operation, and therefore the "DO NOTHING" ought to mean "don't increment anything". You're picturing this:

1. 检查要根据约束插入的值
2. 如果检测到重复，则中止
3. 递增序列
4. 插入数据

但实际上，增量必须在尝试插入之前发生.Postgres 中的 SERIAL 列实现为 DEFAULT，它在绑定的 SEQUENCE 上执行 nextval() 函数.在 DBMS 可以对数据做任何事情之前，它必须有一组完整的列，所以操作的顺序是这样的:

But in fact, the increment has to happen before the insert is attempted. A SERIAL column in Postgres is implemented as a DEFAULT which executes the nextval() function on a bound SEQUENCE. Before the DBMS can do anything with the data, it's got to have a complete set of columns, so the order of operations is like this:

1. 解决默认值，包括增加序列
2. 检查要根据约束插入的值
3. 如果检测到重复，则中止
4. 插入数据

如果重复键在自增字段本身中，可以直观地看出这一点:

This can be seen intuitively if the duplicate key is in the autoincrement field itself:

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;

显然，如果不增加序列就无法知道是否存在冲突，因此什么都不做"必须在该增量之后.

Clearly, this can't know if there's a conflict without incrementing the sequence, so the "do nothing" has to come after that increment.

这篇关于尽管我添加了冲突，但 postgres 中的串行正在增加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！