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)
实例如下:
一个会话创建序列并取值
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)
为了演示,将使用一个简单的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)
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';
结论:
以上展示了几种使序列跳过值的不同方法——有时甚至是倒过来的。但是,如果您所需要的只是唯一的主键值,那么序列间隙并不是问题。不要尝试“无间隙序列”。我们可以通过一些方法实现,但性能影响很大。