PostgreSQL教程

Postgresql之产生序列间隙的几种情况

本文主要是介绍Postgresql之产生序列间隙的几种情况,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
  1. 由回滚导致的序列间隙
    如下例子:插入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)
  1. 缓存序列导致的序列间隙
    即使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)
  1. 由崩溃引起的序列间隙
    与所有其他数据库对象一样,对序列的更改会被记录到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)
  1. 崩溃后向后跳转的序列

如果记录序列值前进的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)
  1. 如何构建无间隙序列
    首先:在决定构建无间隙序列之前,请三思。它将序列化所有使用该“序列”的事务。这将大大降低您的数据修改性能。例如,从插入行时的当前时间戳开始。然后,您可以使用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之产生序列间隙的几种情况的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!