postgres=# select * from testa;
id | info
----+-----------
1 | abc
2 | abcd
11 | test
3 | asdfghjkl
4 | asdf4
5 | asdf5
6 | asdf6
7 | asdf7
8 | asdf8
9 | asdf9
10 | asdf10
11 | asdf11
9 | asdf9
(13 rows)
postgres=#
postgres=# select sequencename,sequenceowner,cache_size,last_value from pg_sequences;
sequencename | sequenceowner | cache_size | last_value
--------------+---------------+------------+------------
testa_id_seq | postgres | 100 | 100
(1 row)
postgres=# \q
[postgres@pgserver ~]$ pg_ctl stop -m fast --->>>>注意这是fast关闭,也就是“干净的”关闭PG Cluster。
waiting for server to shut down.... done
server stopped
[postgres@pgserver ~]$ pg_ctl start--->>>>启动PG Cluster
waiting for server to start....2018-04-23 09:32:08.316 EDT [19339] LOG: listening on IPv4 address "0.0.0.0", port 5432
2018-04-23 09:32:08.317 EDT [19339] LOG: listening on IPv6 address "::", port 5432
2018-04-23 09:32:08.327 EDT [19339] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-04-23 09:32:08.403 EDT [19340] LOG: database system was shut down at 2018-04-23 09:32:02 EDT
2018-04-23 09:32:08.412 EDT [19339] LOG: database system is ready to accept connections
done
server started
[postgres@pgserver ~]$ psql
psql (10beta2)
Type "help" for help.
postgres=# select sequencename,sequenceowner,cache_size,last_value from pg_sequences;
sequencename | sequenceowner | cache_size | last_value
--------------+---------------+------------+------------
testa_id_seq | postgres | 100 | 100 ---->>>此时与“pg_ctl stop -m fast”之前一样。
(1 row)
postgres=# insert into testa(info) values('asdf???');
INSERT 0 1
postgres=# select * from testa;
id | info
-----+-----------
1 | abc
2 | abcd
11 | test
3 | asdfghjkl
4 | asdf4
5 | asdf5
6 | asdf6
7 | asdf7
8 | asdf8
9 | asdf9
10 | asdf10
11 | asdf11
9 | asdf9
101 | asdf??? --->>>注意插入的id值是101,并不是预想的12。
(14 rows)
postgres=# select sequencename,sequenceowner,cache_size,last_value from pg_sequences;
sequencename | sequenceowner | cache_size | last_value
--------------+---------------+------------+------------
testa_id_seq | postgres | 100 | 200---->>>此处从100变为200。
(1 row)
postgres=#
Oracle 带cache的 sequence 在正常关闭和异常关闭时的表现:
http://www.xifenfei.com/2015/04/%E6%AD%A3%E5%B8%B8%E5%85%B3%E9%97%AD%E6%95%B0%E6%8D%AE%E5%BA%93sequence-cache%E4%B8%8D%E4%B8%BA0-sequence%E4%B8%8D%E8%B7%B3%E8%B7%83.html
结论:PG Cluster正常关闭时,没用真正用过的cache并不回写到数据字典中,这样的后果就是在PG Cluster起来之后,该sequence被插入的第一个值就是pg_sequences.last_value+1
估计PG对sequence的设计是这样的:只要sequence有cache了,那么PG就视为这些cache已经实际上被使用了,虽然可能在实际上并没有真正使用。参见:https://www.postgresql.org/docs/10/static/view-pg-sequences.html