即使我添加了冲突，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。客户是主键（文本）

sometable.customer is a primary key (text)

某种表的结构是：

id： serial

客户：文本

余额：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.

有什么建议吗？

推荐答案

这对您来说很奇怪，原因是您认为计数器的增量是插入操作的一部分，因此，什么都不做应该表示什么都不增加。您正在构图：

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. 插入数据

1. Check values to insert against constraint
2. If duplicate detected, abort
3. Increment sequence
4. Insert data

但是实际上，增量必须在尝试插入之前发生。 Postgres中的 SERIAL 列被实现为 DEFAULT ，它执行 nextval（） SEQUENCE 上的code>函数。在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. 插入数据

1. Resolve default values, including incrementing the sequence
2. Check values to insert against constraint
3. If duplicate detected, abort
4. Insert data

如果重复键本身位于自动增量字段中，则可以直观地看到：

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屋！