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,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,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
