Postgresql之产生序列间隙的几种情况
2021/11/1 19:11:16
本文主要是介绍Postgresql之产生序列间隙的几种情况,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
- 由回滚导致的序列间隙
如下例子:插入2的时候失败,后续成功插入的值为3
第二个语句回滚了,但是序列值2没有回滚,形成了一个间隙
CREATE TABLE be_positive ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, value integer CHECK (value > 0) ); -- the identity column is backed by a sequence: SELECT pg_get_serial_sequence('be_positive', 'id'); pg_get_serial_sequence ════════════════════════════ laurenz.be_positive_id_seq (1 row) INSERT INTO be_positive (value) VALUES (42); INSERT 0 1 INSERT INTO be_positive (value) VALUES (-99); ERROR: new row for relation "be_positive" violates check constraint "be_positive_value_check" DETAIL: Failing row contains (2, -99). INSERT INTO be_positive (value) VALUES (314); INSERT 0 1 TABLE be_positive; id │ value ════╪═══════ 1 │ 42 3 │ 314 (2 rows)
- 缓存序列导致的序列间隙
即使nextval的代价很低,序列仍然可能是高并发环境中的瓶颈。为了缓解瓶颈,可以定义一个具有大于1的CACHE子句的序列。然后,数据库会话中对nextval的第一次调用实际上会在单个操作中获取cache设置的序列值。对nextval的后续调用将使用这些缓存的值,不需要访问序列。
实例如下:
一个会话创建序列并取值
CREATE SEQUENCE seq CACHE 20; SELECT nextval('seq'); nextval ═════════ 1 (1 row) SELECT nextval('seq'); nextval ═════════ 2 (1 row)
另外一个会话打开并取值:
SELECT nextval('seq'); nextval ═════════ 21 (1 row)
- 由崩溃引起的序列间隙
与所有其他数据库对象一样,对序列的更改会被记录到WAL中,因此恢复可以从备份或崩溃后恢复状态。因为写WAL会影响性能,所以不是每次调用nextval都会记录到WAL。相反,第一次调用记录当前值之前的32个数字,而对nextval的下32次调用不记录任何东西。这意味着在从崩溃中恢复之后,序列可能跳过了一些值。
为了演示,将使用一个简单的PL/Python函数,通过向当前进程发送KILL信号使服务器崩溃:
这里如果没有安装plpython3u,plpython2u也可以。
CREATE FUNCTION seppuku() RETURNS void LANGUAGE plpython3u AS 'import os, signal os.kill(os.getpid(), signal.SIGKILL)';
CREATE SEQUENCE seq; SELECT nextval('seq'); nextval ═════════ 1 (1 row) SELECT seppuku(); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
在重新连接时,我们发现一些值丢失了:
SELECT nextval('seq'); nextval ═════════ 34 (1 row)
- 崩溃后向后跳转的序列
如果记录序列值前进的WAL记录还没有持久化到磁盘上,就会发生向后跳。为什么?因为包含调用nextval的事务还没有提交:
CREATE SEQUENCE seq; BEGIN; SELECT nextval('seq'); nextval ═════════ 1 (1 row) SELECT nextval('seq'); nextval ═════════ 2 (1 row) SELECT nextval('seq'); nextval ═════════ 3 (1 row) SELECT seppuku(); psql:seq.sql:9: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. #作者实验结果如下: SELECT nextval('seq'); nextval ═════════ 1 (1 row) #我实验结果和上一个一致,并没有回滚,还是跳过了一些值: postgres=# SELECT nextval('seq'); nextval --------- 34 (1 row)
- 如何构建无间隙序列
首先:在决定构建无间隙序列之前,请三思。它将序列化所有使用该“序列”的事务。这将大大降低您的数据修改性能。例如,从插入行时的当前时间戳开始。然后,您可以使用row_number窗口函数来计算间隙排序,而您查询的数据:
SELECT created_ts, value, row_number() OVER (ORDER BY created_ts) AS gapless_seq FROM mytable;
当然也可以通过以下update实现一个无间隙的序列,但是性能不好,因为会有行锁冲突。
CREATE TABLE seq (id bigint NOT NULL); INSERT INTO seq (id) VALUES (0); CREATE FUNCTION next_val() RETURNS bigint LANGUAGE sql AS 'UPDATE seq SET id = id + 1 RETURNING id';
结论:
以上展示了几种使序列跳过值的不同方法——有时甚至是倒过来的。但是,如果您所需要的只是唯一的主键值,那么序列间隙并不是问题。不要尝试“无间隙序列”。我们可以通过一些方法实现,但性能影响很大。
这篇关于Postgresql之产生序列间隙的几种情况的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-01-05快速清空 PostgreSQL 数据库中的所有表格,让你的数据库重新焕然一新!
- 2024-01-04在PostgreSQL中创建角色:判断角色是否存在并创建
- 2023-05-16PostgreSQL一站式插件推荐 -- pg_enterprise_views
- 2022-11-22PostgreSQL 实时位置跟踪
- 2022-11-22如何将PostgreSQL插件移植到openGauss
- 2022-11-11PostgreSQL:修改数据库用户的密码
- 2022-11-06Windows 环境搭建 PostgreSQL 物理复制高可用架构数据库服务
- 2022-10-27Windows 环境搭建 PostgreSQL 逻辑复制高可用架构数据库服务
- 2022-10-11PostgreSql安装(Windows10版本)
- 2022-09-13PostgreSQL-Network Address类型操作和函数