PostgreSQL:10:新增 pg_sequence 系统表

Pg_sequence patch 说明

Add pg_sequence system catalog
Move sequence metadata (start, increment, etc.) into a proper system
catalog instead of storing it in the sequence heap object. This
separates the metadata from the sequence data. Sequence metadata is now
operated on transactionally by DDL commands, whereas previously
rollbacks of sequence-related DDL commands would be ignored.
Reviewed-by: Andreas Karlsson

Release Note 中的说明

Create a pg_sequence system catalog to store sequence metadata (Peter Eisentraut)

Sequence metadata includes start, increment, etc, which is now transactional. Sequence counters are still stored in separate heap relations. Also add pg_sequences view to show all sequences.

PostgreSQL10 增加 pg_sequence 系统表存储序列的元数据,元数据是指序列的 seqstart、seqincremnet、seqmax、seqmin、seqcache 等值,但序列的 last value 依然存储在序列本身中;10版本之前序列所有以上信息都存储在序列中。

PostgreSQL9.6 序列

创建测试表, serial 类型默认创建序列

1
2
3
4
5
6
7
8
9
francs=> create table test_seq(id serial,name text);
CREATE TABLE

francs=> \d test_seq
Table "francs.test_seq"
Column | Type | Modifiers
--------+---------+-------------------------------------------------------
id | integer | not null default nextval('test_seq_id_seq'::regclass)
name | text|

查看序列, 注意 last_value 值为 1

1
2
3
4
5
6
7
8
9
10
11
12
francs=>  select  *  from test_seq_id_seq ;
-[ RECORD 1 ]-+--------------------
sequence_name | test_seq_id_seq
last_value | 1
start_value | 1
increment_by | 1
max_value | 9223372036854775807
min_value | 1
cache_value | 1
log_cnt | 0
is_cycled | f
is_called | f

插入三条数据

1
2
francs=> insert into test_seq(name) values('a'),('b'),('c');
INSERT 0 3

再次查看序列, 注意 last_value 值为 3

1
2
3
4
5
6
7
8
9
10
11
12
francs=>  select  *  from test_seq_id_seq ;
-[ RECORD 1 ]-+--------------------
sequence_name | test_seq_id_seq
last_value | 3
start_value | 1
increment_by | 1
max_value | 9223372036854775807
min_value | 1
cache_value | 1
log_cnt | 30
is_cycled | f
is_called | t

备注:PostgreSQL9.6 序列的元数据、last_values 值都存在序列中。

PostgreSQL10 序列

创建测试表并查看序列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
francs=> create table test_seq(id serial,name text);
CREATE TABLE

francs=> \d test_seq
Table "francs.test_seq"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('test_seq_id_seq'::regclass)
name | text| | |

francs=> select * from test_seq_id_seq ;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f

备注:序列仅存储 last_value、log_cnt、is_called 值,序列的其它值存储在哪了呢?

查看 pg_sequence

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
francs=> \d pg_sequence
Table "pg_catalog.pg_sequence"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
seqrelid | oid | | not null |
seqtypid | oid | | not null |
seqstart | bigint| | not null |
seqincrement | bigint| | not null |
seqmax | bigint| | not null |
seqmin | bigint| | not null |
seqcache | bigint| | not null |
seqcycle | boolean | | not null |
Indexes:
"pg_sequence_seqrelid_index" UNIQUE, btree (seqrelid)

francs=> select seqrelid::regclass,* from pg_sequence where seqrelid='test_seq_id_seq'::regclass;
-[ RECORD 1 ]+----------------
seqrelid | test_seq_id_seq
seqrelid | 24996
seqtypid | 23
seqstart | 1
seqincrement | 1
seqmax | 2147483647
seqmin | 1
seqcache | 1
seqcycle | f

备注:序列的 seqstart、seqincrement、seqmax、seqmin、seqcache、seqcycle 元数据存储在了新增加 pg_sequence 系统表中, 10版本的这个特性变化值得关注,可通过 pg_sequence 系统表查看一个数据库中所有序列属性;而序列的last_value、log_cnt、is_called 值依然存储在序列中。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%