目的
調查分區表是否支援sequence。即在分區表中,如果引用了sequence的話,是否能夠保證每次insert新的record後,sequence自增。
結論
支援
測試環境
centos 7 2004, postgresql 12.4
實驗過程
建立sequence
test=# create sequence myseq minvalue 0;
CREATE SEQUENCE
建立分區表
test=# create table parttab (w_id integer, seq_field integer default nextval('myseq'), info text) partition by hash (w_id);
CREATE TABLE
test=# \d parttab
Partitioned table "public.parttab"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+----------------------------
w_id | integer | | |
seq_field | integer | | | nextval('myseq'::regclass)
info | text | | |
Partition key: HASH (w_id)
Number of partitions: 0
建立分區表
test=# create table parttab0 partition of parttab for values WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE
test=# create table parttab1 partition of parttab for values WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE
test=# create table parttab2 partition of parttab for values WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE
test=# create table parttab3 partition of parttab for values WITH (MODULUS 4, REMAINDER 3);
CREATE TABLE
插入資料
insert into parttab(w_id, info)
select
s,
'info ' || s
from generate_series(0,50) as s;
效果等同:
insert into parttab(w_id, info) values(0, 'info 0');
insert into parttab(w_id, info) values(1, 'info 1');
...
insert into parttab(w_id, info) values(50, 'info 50');
檢視資料
插入資料是以w_id為序列順序添加的(0-50),可以看到對應的seq_field也是随w_id遞增,說明對于分區表,能保證其中的sequence和非分區表一樣正常遞增。
test=# select * from parttab order by w_id;
w_id | seq_field | info
------+-----------+--------
0 | 0 | info 0
1 | 1 | info 1
2 | 2 | info 2
3 | 3 | info 3
4 | 4 | info 4
5 | 5 | info 5
6 | 6 | info 6
...
49 | 49 | info 49
50 | 50 | info 50
(51 rows)
參考資料
https://developer.aliyun.com/article/66946