rename 對 sequence 的影響
關聯列與 sequence 后，即 sequence 屬于該列后，drop 表或列時會自動 drop 相關 sequence，
但如果對表或列 rename 后，甚至 rename sequence后，會發生什么呢？
我們來做一下實驗，
創建測驗表 tb_test_sequence_rename
alvindb=>
CREATE TABLE tb_test_sequence_rename (
test_id BIGSERIAL PRIMARY KEY,
create_time TIMESTAMP DEFAULT clock_timestamp()
);
CREATE TABLE
查看表與 sequence：
alvindb=> \d tb_test_sequence_rename
Table "alvin.tb_test_sequence_rename"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+----------------------------------------------------------
test_id | bigint | | not null | nextval('tb_test_sequence_rename_test_id_seq'::regclass)
create_time | timestamp without time zone | | | clock_timestamp()
Indexes:
"tb_test_sequence_rename_pkey" PRIMARY KEY, btree (test_id)
alvindb=> \d tb_test_sequence_rename_test_id_seq
Sequence "alvin.tb_test_sequence_rename_test_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: alvin.tb_test_sequence_rename.test_id
alvindb=>
對表進行 rename ：
alvindb=> ALTER TABLE tb_test_sequence_rename RENAME TO tb_test_sequence_rename2;
ALTER TABLE
通過如下結果，我們可以看到， rename 表后 'Owned by' 也會隨之自動變化，
alvindb=> \d tb_test_sequence_rename2
Table "alvin.tb_test_sequence_rename2"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+----------------------------------------------------------
test_id | bigint | | not null | nextval('tb_test_sequence_rename_test_id_seq'::regclass)
create_time | timestamp without time zone | | | clock_timestamp()
Indexes:
"tb_test_sequence_rename_pkey" PRIMARY KEY, btree (test_id)
alvindb=> \d tb_test_sequence_rename_test_id_seq
Sequence "alvin.tb_test_sequence_rename_test_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: alvin.tb_test_sequence_rename2.test_id
alvindb=>
我們再 rename 列試一下，
alvindb=> ALTER TABLE tb_test_sequence_rename2 RENAME test_id TO test_id2;
ALTER TABLE
通過如下結果，我們可以看到， rename 列后 'Owned by' 也會隨之自動變化，
alvindb=> \d tb_test_sequence_rename2
Table "alvin.tb_test_sequence_rename2"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+----------------------------------------------------------
test_id2 | bigint | | not null | nextval('tb_test_sequence_rename_test_id_seq'::regclass)
create_time | timestamp without time zone | | | clock_timestamp()
Indexes:
"tb_test_sequence_rename_pkey" PRIMARY KEY, btree (test_id2)
alvindb=> \d tb_test_sequence_rename_test_id_seq
Sequence "alvin.tb_test_sequence_rename_test_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: alvin.tb_test_sequence_rename2.test_id2
alvindb=>
我們來 rename 一下 sequence，
alvindb=> ALTER SEQUENCE tb_test_sequence_rename_test_id_seq RENAME TO tb_test_sequence_rename_test_id_seq2;
ALTER SEQUENCE
通過如下結果，我們可以看到， rename sequence 后 'Owned by' 也會隨之自動變化，并且 Default 中的 sequence 也會隨之變化，
alvindb=> \d tb_test_sequence_rename2
Table "alvin.tb_test_sequence_rename2"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+-----------------------------------------------------------
test_id2 | bigint | | not null | nextval('tb_test_sequence_rename_test_id_seq2'::regclass)
create_time | timestamp without time zone | | | clock_timestamp()
Indexes:
"tb_test_sequence_rename_pkey" PRIMARY KEY, btree (test_id2)
alvindb=> \d tb_test_sequence_rename_test_id_seq2
Sequence "alvin.tb_test_sequence_rename_test_id_seq2"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: alvin.tb_test_sequence_rename2.test_id2
alvindb=>
通過以上三個 rename 實驗，可以發現，正常 rename 不會對 sequence 的使用產生影響，
無論是 rename 表名，列名，還是 sequence 的名字，如我們所期望，PostgreSQL 都會智能地作出相應的修改，
復制表或遷移表時 sequence 的相關操作
以下表為例，
alvindb=> \d tb_test_bigserial
Table "alvin.tb_test_bigserial"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+----------------------------------------------------
test_id | bigint | | not null | nextval('tb_test_bigserial_test_id_seq'::regclass)
create_time | timestamp without time zone | | | clock_timestamp()
Indexes:
"tb_test_bigserial_pkey" PRIMARY KEY, btree (test_id)
alvindb=> \d tb_test_bigserial_test_id_seq
Sequence "alvin.tb_test_bigserial_test_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: alvin.tb_test_bigserial.test_id
在一些情況下，為避免 DDL 或大量 DML 對表 tb_test_bigserial 的影響，我們可以通過 RENAME 表的方式實作：
- 根據表
tb_test_bigserial復制出相同表結構的表tb_test_bigserial_new - 對
tb_test_bigserial_new進行 DDL 或 大量 DML - 將
tb_test_bigserial_newRENAME 回tb_test_bigserial
根據表 tb_test_bigserial 復制出相同表結構的表 tb_test_bigserial_new：
alvindb=> CREATE TABLE tb_test_bigserial_new (LIKE tb_test_bigserial INCLUDING ALL);
CREATE TABLE
alvindb=> \d tb_test_bigserial_new
Table "alvin.tb_test_bigserial_new"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+----------------------------------------------------
test_id | bigint | | not null | nextval('tb_test_bigserial_test_id_seq'::regclass)
create_time | timestamp without time zone | | | clock_timestamp()
Indexes:
"tb_test_bigserial_new_pkey" PRIMARY KEY, btree (test_id)
對 tb_test_bigserial_new 進行 DDL 或 大量 DML 后，將 tb_test_bigserial_new RENAME 回 tb_test_bigserial：
alvindb=> BEGIN;
BEGIN
alvindb=> ALTER TABLE tb_test_bigserial RENAME TO tb_test_bigserial_old;
ALTER TABLE
alvindb=> ALTER TABLE tb_test_bigserial_new RENAME TO tb_test_bigserial;
ALTER TABLE
alvindb=> END;
COMMIT
這時，查看新表結構：
alvindb=> \d tb_test_bigserial
Table "alvin.tb_test_bigserial"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+----------------------------------------------------
test_id | bigint | | not null | nextval('tb_test_bigserial_test_id_seq'::regclass)
create_time | timestamp without time zone | | | clock_timestamp()
Indexes:
"tb_test_bigserial_new_pkey" PRIMARY KEY, btree (test_id)
此處，我們只關注 sequence，上述的索引的名字可以根據需要決定是否需要 RENAME 回原來的名字，
查看 sequence，
alvindb=> \d tb_test_bigserial_test_id_seq
Sequence "alvin.tb_test_bigserial_test_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: alvin.tb_test_bigserial_old.test_id
從以上 'Owned by' 可以看出，sequence tb_test_bigserial_test_id_seq 還是歸舊表 tb_test_bigserial_old 的列所有，
從上文“rename 對 sequence 的影響”我們知道，這是正常的，
此時 DROP 舊表，會提示新表 tb_test_bigserial 還在依賴 sequence tb_test_bigserial_test_id_seq，
alvindb=> DROP TABLE tb_test_bigserial_old;
ERROR: cannot drop table tb_test_bigserial_old because other objects depend on it
DETAIL: default value for column test_id of table tb_test_bigserial depends on sequence tb_test_bigserial_test_id_seq
HINT: Use DROP ... CASCADE to drop the dependent objects too.
alvindb=>
以下 RENAME 表時關鍵的一步：
alvindb=> ALTER SEQUENCE tb_test_bigserial_test_id_seq OWNED BY tb_test_bigserial.test_id;
ALTER SEQUENCE
通過上述 SQL，sequence tb_test_bigserial_test_id_seq 就歸新表的列所有了，
在日常操作中，我們有可能忘記修改 sequence 的所屬關系，以致后來 DROP 老表時加了 CASCADE，將 sequence 也一起 DROP 掉了，從而引發問題，
此時 DROP 表就不報錯了：
alvindb=> DROP TABLE tb_test_bigserial_old;
DROP TABLE
以下是 RENAME 后所期望的結果（注意 sequence 的 'Owned by'）：
alvindb=> \d tb_test_bigserial
Table "alvin.tb_test_bigserial"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+----------------------------------------------------
test_id | bigint | | not null | nextval('tb_test_bigserial_test_id_seq'::regclass)
create_time | timestamp without time zone | | | clock_timestamp()
Indexes:
"tb_test_bigserial_new_pkey" PRIMARY KEY, btree (test_id)
alvindb=> \d tb_test_bigserial_test_id_seq
Sequence "alvin.tb_test_bigserial_test_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: alvin.tb_test_bigserial.test_id
sequence cache
從上面可以看到，sequence 默認的 cache 是1，
在高并發的系統中，為了提高性能，可以增大 cache，如 10，即一次將 sequence 的10個數值 load 到記憶體中，這樣，只需要訪問 sequence 1 次，而不是10次，
下面，將 sequence tb_test_bigserial_test_id_seq 的 cache 改成 10：
alvindb=> SELECT pg_backend_pid();
pg_backend_pid
----------------
16649
(1 row)
alvindb=> ALTER SEQUENCE tb_test_bigserial_test_id_seq CACHE 10;
ALTER SEQUENCE
alvindb=> \d tb_test_bigserial_test_id_seq
Sequence "alvin.tb_test_bigserial_test_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 10
Owned by: alvin.tb_test_bigserial.test_id
在 session 1(pid 16649)中插入資料
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> SELECT * FROM tb_test_bigserial ORDER BY 2 DESC;
test_id | create_time
---------+----------------------------
2 | 2021-05-01 15:21:00.116979
1 | 2021-05-01 15:20:56.405149
(2 rows)
alvindb=> SELECT * FROM tb_test_bigserial_test_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
10 | 32 | t
(1 row)
alvindb=> SELECT lastval();
lastval
---------
2
(1 row)
從以上結果可以看到，此 session 中一次取了10個資料，last_value 是10，is_called 是 true，即新的 session 中從11開始，
lastval() 是2，即當前 session 中，上一次 nextval 回傳的是2，需要注意，lastval() 并沒有引數，即它回傳最近任意 sequence 的 nextval 值，
如下，
alvindb=> SELECT nextval('tb_test_sequence_rename_test_id_seq2');
nextval
---------
1
(1 row)
alvindb=> SELECT lastval();
lastval
---------
1
(1 row)
在 session 2(pid 14287)中插入資料：
alvindb=> SELECT pg_backend_pid();
pg_backend_pid
----------------
14287
(1 row)
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> SELECT * FROM tb_test_bigserial ORDER BY 2 DESC;
test_id | create_time
---------+----------------------------
12 | 2021-05-01 15:22:02.582036
11 | 2021-05-01 15:22:01.313919
2 | 2021-05-01 15:21:00.116979
1 | 2021-05-01 15:20:56.405149
(4 rows)
alvindb=> SELECT * FROM tb_test_bigserial_test_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
20 | 32 | t
(1 row)
alvindb=> SELECT lastval();
lastval
---------
12
(1 row)
從以上結果可以看到，雖然在 session 1中，sequence tb_test_bigserial_test_id_seq 只取了兩次值，但 session 2中是從11開始取值的，
因為 cache 是10，所以1-10是預留給了 session 1，11-20是預留給了 session 2，
在 session 2中，last_value 是20，lastval()是12，
如果現在再看 session 1呢？
alvindb=> SELECT pg_backend_pid();
pg_backend_pid
----------------
16649
(1 row)
alvindb=> SELECT now();
now
-------------------------------
2021-05-01 15:23:54.674943+08
(1 row)
alvindb=> SELECT * FROM tb_test_bigserial_test_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
20 | 32 | t
(1 row)
alvindb=> SELECT lastval();
lastval
---------
2
(1 row)
現在看到 session 1中的 last_value 變成了 20，而 lastval() 還是2，
對比 session 1 和 session 2，可以看出，last_value 是與 session 無關的，而 lastval() 是與 session 有關，但與是哪個 sequence 無關，
現在，在 session 3 中驗證一下，
alvindb=> SELECT pg_backend_pid();
pg_backend_pid
----------------
15809
(1 row)
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> SELECT * FROM tb_test_bigserial ORDER BY 2 DESC;
test_id | create_time
---------+----------------------------
22 | 2021-05-01 15:25:08.643196
21 | 2021-05-01 15:25:04.005421
12 | 2021-05-01 15:22:02.582036
11 | 2021-05-01 15:22:01.313919
2 | 2021-05-01 15:21:00.116979
1 | 2021-05-01 15:20:56.405149
(6 rows)
alvindb=> SELECT now();
now
-------------------------------
2021-05-01 15:25:24.499995+08
(1 row)
alvindb=> SELECT * FROM tb_test_bigserial_test_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
30 | 32 | t
(1 row)
alvindb=> SELECT lastval();
lastval
---------
22
(1 row)
可以看出，以上推論是正確的，session 3 中 sequence 是從21開始的，插入兩行資料后，last_value 變成 30，lastval() 在當前 session 中是22，
現在有一個問題，后插入的資料的 test_id 一定比先插入的大嗎？能否根據 test_id 排序來確定是哪一行資料先插入的呢？
下面，我們回到 session 2 繼續插入資料，
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> SELECT * FROM tb_test_bigserial ORDER BY 2 DESC;
test_id | create_time
---------+----------------------------
14 | 2021-05-01 15:28:16.500833
13 | 2021-05-01 15:28:04.276826
22 | 2021-05-01 15:25:08.643196
21 | 2021-05-01 15:25:04.005421
12 | 2021-05-01 15:22:02.582036
11 | 2021-05-01 15:22:01.313919
2 | 2021-05-01 15:21:00.116979
1 | 2021-05-01 15:20:56.405149
(8 rows)
可以看出，在 session 2中，sequence 繼續從 13 開始，這是因為 session 2中一次性 cache 的 10 個數值(11-20)還沒有用完，
所以，如果 cache 設定大于1，就不能以 sequence 的大小判斷其插入的順序，
可以用 create_time 來排序，但需要注意，在事務中，now() 回傳的時間是一樣的，而 clock_timestamp() 回傳的則是實際的時間，
這也是在創建表 tb_test_bigserial 時采用 clock_timestamp() 而不是 now() 的原因，
現在如果呼叫 setval 會發生什么呢？
我們在 session 1中呼叫 setval，
alvindb=> SELECT pg_backend_pid();
pg_backend_pid
----------------
16649
(1 row)
alvindb=> SELECT setval('tb_test_bigserial_test_id_seq', 51);
setval
--------
51
(1 row)
alvindb=> SELECT * FROM tb_test_bigserial_test_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
51 | 0 | t
(1 row)
alvindb=> SELECT lastval();
lastval
---------
51
(1 row)
上述 SQL 已將 last_value 設為 51，即下一個回傳 52，
現在我們在 session 2中繼續插入，
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> SELECT * FROM tb_test_bigserial ORDER BY 2 DESC;
test_id | create_time
---------+----------------------------
18 | 2021-05-01 15:28:22.692747
17 | 2021-05-01 15:28:22.371691
16 | 2021-05-01 15:28:22.021806
15 | 2021-05-01 15:28:20.917822
14 | 2021-05-01 15:28:16.500833
13 | 2021-05-01 15:28:04.276826
22 | 2021-05-01 15:25:08.643196
21 | 2021-05-01 15:25:04.005421
12 | 2021-05-01 15:22:02.582036
11 | 2021-05-01 15:22:01.313919
2 | 2021-05-01 15:21:00.116979
1 | 2021-05-01 15:20:56.405149
(12 rows)
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> SELECT * FROM tb_test_bigserial ORDER BY 2 DESC;
test_id | create_time
---------+----------------------------
19 | 2021-05-01 15:28:26.485763
18 | 2021-05-01 15:28:22.692747
17 | 2021-05-01 15:28:22.371691
16 | 2021-05-01 15:28:22.021806
15 | 2021-05-01 15:28:20.917822
14 | 2021-05-01 15:28:16.500833
13 | 2021-05-01 15:28:04.276826
22 | 2021-05-01 15:25:08.643196
21 | 2021-05-01 15:25:04.005421
12 | 2021-05-01 15:22:02.582036
11 | 2021-05-01 15:22:01.313919
2 | 2021-05-01 15:21:00.116979
1 | 2021-05-01 15:20:56.405149
(13 rows)
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> SELECT * FROM tb_test_bigserial ORDER BY 2 DESC;
test_id | create_time
---------+----------------------------
20 | 2021-05-01 15:28:29.747721
19 | 2021-05-01 15:28:26.485763
18 | 2021-05-01 15:28:22.692747
17 | 2021-05-01 15:28:22.371691
16 | 2021-05-01 15:28:22.021806
15 | 2021-05-01 15:28:20.917822
14 | 2021-05-01 15:28:16.500833
13 | 2021-05-01 15:28:04.276826
22 | 2021-05-01 15:25:08.643196
21 | 2021-05-01 15:25:04.005421
12 | 2021-05-01 15:22:02.582036
11 | 2021-05-01 15:22:01.313919
2 | 2021-05-01 15:21:00.116979
1 | 2021-05-01 15:20:56.405149
(14 rows)
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> SELECT * FROM tb_test_bigserial ORDER BY 2 DESC;
test_id | create_time
---------+----------------------------
52 | 2021-05-01 15:28:32.084771
20 | 2021-05-01 15:28:29.747721
19 | 2021-05-01 15:28:26.485763
18 | 2021-05-01 15:28:22.692747
17 | 2021-05-01 15:28:22.371691
16 | 2021-05-01 15:28:22.021806
15 | 2021-05-01 15:28:20.917822
14 | 2021-05-01 15:28:16.500833
13 | 2021-05-01 15:28:04.276826
22 | 2021-05-01 15:25:08.643196
21 | 2021-05-01 15:25:04.005421
12 | 2021-05-01 15:22:02.582036
11 | 2021-05-01 15:22:01.313919
2 | 2021-05-01 15:21:00.116979
1 | 2021-05-01 15:20:56.405149
(15 rows)
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
alvindb=> SELECT * FROM tb_test_bigserial ORDER BY 2 DESC;
test_id | create_time
---------+----------------------------
53 | 2021-05-01 15:28:45.026188
52 | 2021-05-01 15:28:32.084771
20 | 2021-05-01 15:28:29.747721
19 | 2021-05-01 15:28:26.485763
18 | 2021-05-01 15:28:22.692747
17 | 2021-05-01 15:28:22.371691
16 | 2021-05-01 15:28:22.021806
15 | 2021-05-01 15:28:20.917822
14 | 2021-05-01 15:28:16.500833
13 | 2021-05-01 15:28:04.276826
22 | 2021-05-01 15:25:08.643196
21 | 2021-05-01 15:25:04.005421
12 | 2021-05-01 15:22:02.582036
11 | 2021-05-01 15:22:01.313919
2 | 2021-05-01 15:21:00.116979
1 | 2021-05-01 15:20:56.405149
(16 rows)
可以看出，setval 后，session 2中并沒有立即生效，而是等 session 2中預分配的10個數值消耗完，才繼續從 sequence 又 cache 了10個數值，
另外，可以看出，上述資料中 test_id 列的資料并不是連續的，這也是使用 cache 的原因，
所以，在高并發的系統中，為了提高 sequence 的性能，不僅僅是增大 cache，以上提到的問題都需要注意，
sequence 權限
創建用戶 simon，
postgres=# CREATE USER simon WITH PASSWORD 'simon';
CREATE ROLE
現在將表 tb_test_bigserial 的讀寫權限授予 simon
alvindb=# \c alvindb alvin;
You are now connected to database "alvindb" as user "alvin".
alvindb=> \dp+ tb_test_bigserial
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------------+-------+-------------------+-------------------+----------
alvin | tb_test_bigserial | table | | |
(1 row)
alvindb=> GRANT USAGE ON SCHEMA alvin TO "simon";
GRANT
alvindb=> GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON alvin.tb_test_bigserial TO "simon";
GRANT
alvindb=> \dp+ tb_test_bigserial
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------------+-------+---------------------+-------------------+----------
alvin | tb_test_bigserial | table | alvin=arwdDxt/alvin+| |
| | | simon=arwdD/alvin | |
(1 row)
通過用戶 simon 插入資料
alvindb=> \c alvindb simon
You are now connected to database "alvindb" as user "simon".
alvindb=> SET search_path TO alvin;
SET
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
ERROR: permission denied for sequence tb_test_bigserial_test_id_seq
此時插入資料提示沒有 sequence tb_test_bigserial_test_id_seq 的權限，
現在將 sequence tb_test_bigserial_test_id_seq 也授權一下:
alvindb=> \c alvindb alvin
You are now connected to database "alvindb" as user "alvin".
alvindb=> GRANT ALL ON SEQUENCE tb_test_bigserial_test_id_seq TO "simon";
GRANT
alvindb=> \dp+ tb_test_bigserial
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------------+-------+---------------------+-------------------+----------
alvin | tb_test_bigserial | table | alvin=arwdDxt/alvin+| |
| | | simon=arwdD/alvin | |
(1 row)
alvindb=> \dp+ tb_test_bigserial_test_id_seq
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------------------------+----------+-------------------+-------------------+----------
alvin | tb_test_bigserial_test_id_seq | sequence | alvin=rwU/alvin +| |
| | | simon=rwU/alvin | |
(1 row)
alvindb=> INSERT INTO tb_test_bigserial(create_time) VALUES (DEFAULT);
INSERT 0 1
將 sequence 的讀寫權限授予 simon 后，simon 就可以成功向表 tb_test_bigserial 插入資料了，
可以看出，要對其他用戶授予讀寫權限，不僅要授予表的權限，還需要授予對應 sequence 的權限，
總結
sequence 看起來很簡單，但如果使用姿勢不對，很可能造成 MI (Major Incident)，
對于以下常見的有關 sequence 的問題，需要多留意，
-
integer vs bigint
對于交易型的表，log 表，history 表等較大的表，主鍵 sequence 需要用 bigint，否則可能遇到如下錯誤：
ERROR: nextval: reached maximum value of sequence "tb_test_sequence_test_id_seq" (2147483647)其實，不止資料庫需要考慮這個問題，應用程式中也需要考慮，
存盤主鍵值的變數等需要用相應的型別，如 Java 中對應的需要使用 long 型別而不是 int 型別，
開發和測驗同學可能都需要排查這個問題，以防止將來生產出現不必要的 MI，
開發可以直接看代碼或查看表的DDL，測驗的話，可以通過 setval 來修改當前 sequence 的值進行測驗，
甚至有強制主鍵 id 必須用 bigint 的資料庫規范來避免這個問題，
-
sequence 'Owned by'
通過 rename 方式復制表時，需要注意同時修改 sequence 的 'Owned by'，
否則，新表主鍵 default 中的 sequence 在 drop 老表時有可能被級聯 drop 掉，
-
sequence nextval
當涉及到表或 sequence 的遷移，或需要重新創建 sequence，需要注意重新設定 sequence 值，
-
sequence cache
當 sequence cache 大于1時，需要注意排序時不要用 sequence 的列，可以考慮根據時間的列排序，
-
sequence 權限
在一些情況下，不但要授予相關用戶表的權限，也需要注意 sequence 的權限，
公眾號
關注 DBA Daily 公眾號，第一時間收到文章的更新，
通過一線 DBA 的日常作業，學習實用資料庫技術干貨！
公眾號優質文章推薦
PostgreSQL VACUUM 之深入淺出
華山論劍之 PostgreSQL sequence
[PG Upgrade Series] Extract Epoch Trap
[PG Upgrade Series] Toast Dump Error
GitLab supports only PostgreSQL now
MySQL or PostgreSQL?
PostgreSQL hstore Insight
ReIndex 失敗原因調查
PG 資料匯入 Hive 亂碼問題調查
PostGIS 擴展創建失敗原因調查
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/440466.html
標籤：其他
上一篇：MySQL存盤引擎
下一篇：redis主從復制