目录导读

• 适用范围
• 问题概述
• 解决方案
• 参考文档

适用范围

测试PostgreSQL 12.8及以上版本

问题概述

问题起源于看到表pg_class中字段relam 的注释：

If this is a table or an index, the access method used (heap, B-tree, hash, etc.); otherwise zero (zero occurs for sequences, as well as relations without storage, such as views)

序列究竟占用存储空间吗？
如果不占用存储空间那序列的各项属性值，尤其lastvalue如何持久化。
sequence 属于relation，是否也会如table一样relfilenode会变化。
带着疑问进行测试验证。

解决方案

1.查看官方文档关于序列定义

序列是bigint整数，取值范围为：-2^63~2^2^63-1
Tables, sequences, views, foreign tables, materialized views, composite types, and indexes are all relations.

2.创建序列

postgres@[local]:1931=#23674 create sequence seq3 minvalue 1 start 1 maxvalue 100;
CREATE SEQUENCE

3.查看序列

 postgres@[local]:1931=#23674 \dsS+ seq3
                              List of relations
 Schema | Name |   Type   |  Owner   | Persistence |    Size    | Description 
--------+------+----------+----------+-------------+------------+-------------
 public | seq3 | sequence | postgres | permanent   | 8192 bytes | 
(1 row)

可以看到序列大小为8K。

postgres@[local]:1931=#23674 select pg_relation_filepath(oid),relname from pg_class where relname='seq3';
 pg_relation_filepath | relname 
----------------------+---------
 base/13892/509370    | seq3
(1 row)

postgres@[local]:1931=#23674 exit
[postgres@pg14 ~]$ ls -l $PGDATA/base/13892/509370
-rw------- 1 postgres postgres 8192 Jun 21 10:28 /pgdata/14/data/base/13892/509370

可以看到序列对象：seq3存在物理文件，并且物理文件大小为：8k。
查看当前序列值，并使它增长。通过命令md5sum hexdump查看文件是否产生变化。

[postgres@pg14 ~]$ psql
psql (14.2)
Type "help" for help.

postgres@[local]:1931=#24309 select * from seq3;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | f
(1 row)
postgres@[local]:1931=#24274 exit
[postgres@pg14 ~]$ hexdump /pgdata/14/data/base/13892/509370
0000000 0003 0000 bb80 9800 0000 0000 001c 1fc8
0000010 1ff8 2004 0000 0000 9fc8 0052 0000 0000
0000020 0000 0000 0000 0000 0000 0000 0000 0000
*
0001fc0 0000 0000 0000 0000 0002 0000 0000 0000
0001fd0 0000 0000 0000 0000 0001 0003 0b00 0018
0001fe0 0001 0000 0000 0000 0000 0000 0000 0000   <<<<<< 0001 就是序列lastval
0001ff0 0000 0000 0000 0000 1717 0000 0000 0000
0002000

postgres@[local]:1931=#24366 select nextval('seq3');
 nextval 
---------
      10
(1 row)
# 执行checkpoint落盘
postgres@[local]:1931=#24366 checkpoint;
CHECKPOINT

[postgres@pg14 ~]$ hexdump /pgdata/14/data/base/13892/509370
0000000 0003 0000 0328 9900 0000 0000 001c 1fc8
0000010 1ff8 2004 0000 0000 9fc8 0052 0000 0000
0000020 0000 0000 0000 0000 0000 0000 0000 0000
*
0001fc0 0000 0000 0000 0000 0002 0000 0000 0000
0001fd0 0000 0000 0000 0000 0001 0003 0b00 0018
0001fe0 000a 0000 0000 0000 001b 0000 0000 0000  <<< 000a 换算成十进制就是10
0001ff0 0001 0000 0000 0000 1717 0000 0000 0000
0002000

以上证明PostgreSQL关系对象序列有物理存储文件.

4.既然验证了序列和表或索引一样有物理文件存储数据，那是否也和表一样，relfilenode oid 也会产生变化呢。

#修改序列maxvalue
postgres@[local]:1931=#24420 alter sequence seq3 maxvalue 9999999999999999;
ALTER SEQUENCE
postgres@[local]:1931=#24551 select oid,pg_relation_filepath(oid),relname from pg_class where relname='seq3';
  oid   | pg_relation_filepath | relname 
--------+----------------------+---------
 509370 | base/13892/509372    | seq3  <<<可以看到relfilenode oid 变为509372
(1 row)

#修改序列lastvalue
postgres@[local]:1931=#24441 select setval('seq3',999999,true);
 setval 
--------
 999999
(1 row)
postgres@[local]:1931=#24551 select oid,pg_relation_filepath(oid),relname from pg_class where relname='seq3';
  oid   | pg_relation_filepath | relname 
--------+----------------------+---------
 509370 | base/13892/509374    | seq3  <<<可以看到relfilenode oid 变为509374
(1 row)

# 修改序列cache
postgres@[local]:1931=#24551 alter sequence seq3 cache 10;
ALTER SEQUENCE
postgres@[local]:1931=#24551 checkpoint;
CHECKPOINT
postgres@[local]:1931=#24551 select oid,pg_relation_filepath(oid),relname from pg_class where relname='seq3';
  oid   | pg_relation_filepath | relname 
--------+----------------------+---------
 509370 | base/13892/509375    | seq3 <<<可以看到relfilenode oid 变为509375
(1 row)

# 
postgres@[local]:1931=#24551 alter sequence seq3 restart;
ALTER SEQUENCE
postgres@[local]:1931=#24551 checkpoint;
CHECKPOINT
postgres@[local]:1931=#24551 select oid,pg_relation_filepath(oid),relname from pg_class where relname='seq3';
  oid   | pg_relation_filepath | relname 
--------+----------------------+---------
 509370 | base/13892/509376    | seq3  <<<可以看到relfilenode oid 变为509376
(1 row)

结论，只要对sequence属性重新设置时，就会生成新的relfilenode。此结论在源码文件 postgresql-14.2/src/backend/commands/sequence.c 也有类似说明。

/*
 * Reset a sequence to its initial value.
 *
 * The change is made transactionally, so that on failure of the current
 * transaction, the sequence will be restored to its previous state.
 * We do that by creating a whole new relfilenode for the sequence; so this
 * works much like the rewriting forms of ALTER TABLE.
 *
 * Caller is assumed to have acquired AccessExclusiveLock on the sequence,
 * which must not be released until end of transaction.  Caller is also
 * responsible for permissions checking.
 */

参考文档

https://www.postgresql.org/docs/14/catalog-pg-class.html
https://www.postgresql.org/docs/current/glossary.html#GLOSSARY-RELATION
源码文件:postgresql-14.2/src/backend/commands/sequence.c