为什么Postgres id列serial4类型不递增1甚至不删除

Question

在postgres 11.2中，我用id serial4 NOT NULL创建了一个表，我认为id总是会增加1，但我注意到事实并非如此。

表Order创建SQL

CREATE TABLE public.order (
"createdAt" timestamptz NOT NULL DEFAULT now(),
"updatedAt" timestamptz NOT NULL DEFAULT now(),
"deletedAt" timestamptz NULL,
id serial4 NOT NULL,
.....
);

例如，当我查询select id from device d where id < 4000000 order by "id" desc limit 10时，一些ID不会按1递增

id
3999794
3999791
3999668
3999660
3999585
3999578
3999543
3999541
3999334
3999023

尽管在某些id范围内看起来是这样，但id总是以1的增量相加。

例如：

select id from device d where id < 2000 order by "id" desc limit 10或

select id from device d where id < 5000000 order by "id" desc limit 10

id
1999
1998
1997
1996
1995
1994
1993
1992
1991
1990

id
4999984
4999983
4999982
4999981
4999980
4999979
4999978
4999977
4999976
4999975

都是CCD_ 9添加的序列id行。

由于我总是在deletedAt列旁边使用soft delete，并且我已经检查了我的代码，它永远不会删除行。

在某些条件下，postgres 11.2是否会使数据列serial4不增加1，而是增加其他值？我们如何才能防止这种情况发生？

Answer 1

感谢@Laurenz Albe提供链接到POSTGRESQL中的精彩文章GAPS IN SEQUENCES.

在测试之后，在我的情况下，间隙是由列唯一约束引起的，每当插入一行数据违反唯一约束时，serial4列就会转到下一个值。

下面的一些脚本用于复制我的案例：

在docker:中运行postgres11.2

#!/bin/bash
docker rm -f pg-test
docker run -d --name pg-test postgres:11.2
sleep 5
docker exec -it pg-test bash -c 'psql -U postgres'

在psql 中

e
CREATE TABLE public.order (
"createdAt" timestamptz NOT NULL DEFAULT now(),
"updatedAt" timestamptz NOT NULL DEFAULT now(),
"deletedAt" timestamptz NULL,
id serial4 NOT NULL,
n1 varchar UNIQUE
);
INSERT INTO "order" (n1) VALUES (1); 
// return INSERT 0 1
INSERT INTO "order" (n1) VALUES (1); 
// return ERROR:  duplicate key value violates unique constraint "order_n1_key" DETAIL:  Key (n1)=(4) already exists.
INSERT INTO "order" (n1) VALUES (2);
// return INSERT 0 1
select * from "order";

选择结果为：

createdAt           |           updatedAt           | deletedAt | id | n1
-------------------------------+-------------------------------+-----------+----+----
2022-08-26 02:18:03.716084+00 | 2022-08-26 02:18:03.716084+00 |           |  1 | 1
2022-08-26 02:18:46.831221+00 | 2022-08-26 02:18:46.831221+00 |           |  3 | 2

idserial4列具有从1到3的间隙，因为INSERT违反了唯一约束。