PostgreSQL12.5中分割區表的一些操作範例

2022-08-12 14:00:44

1、建立一個有DEFAULT的分割區表

1、先建立主表

create table tbl_log
(
    id          serial,
    create_time timestamp(0) without time zone,
    remark      char(1)
) PARTITION BY RANGE (create_time);
#因為是serial型別，自增的所以會自動建立一個序列
postgres=# d
                   List of relations
 Schema |      Name      |       Type        |  Owner   
--------+----------------+-------------------+----------
 public | tbl_log        | partitioned table | postgres
 public | tbl_log_id_seq | sequence          | postgres
(7 rows)

2、如果沒有建立分割區就直接插入資料會報錯

postgres=# INSERT INTO tbl_log(id, create_time, remark) VALUES (1, '2018-02-01', 'a');
ERROR:  no partition of relation "tbl_log" found for row
DETAIL:  Partition key of the failing row contains (create_time) = (2018-02-01 00:00:00).
postgres=#

3、建立分割區

#包括左邊1.1，不包括2.1
CREATE TABLE tbl_log_p201801 PARTITION OF tbl_log FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');
CREATE TABLE tbl_log_p201802 PARTITION OF tbl_log FOR VALUES FROM ('2018-02-01') TO ('2018-03-01');
CREATE TABLE tbl_log_p201803 PARTITION OF tbl_log FOR VALUES FROM ('2018-03-01') TO ('2018-04-01');
CREATE TABLE tbl_log_default PARTITION OF tbl_log DEFAULT;
INSERT INTO tbl_log(id, create_time, remark) VALUES (1, '2018-02-01', 'a');
INSERT INTO tbl_log(id, create_time, remark) VALUES (2, '2018-03-01', 'b');
INSERT INTO tbl_log(id, create_time, remark) VALUES (3, '2018-04-01', 'd');
INSERT INTO tbl_log(id, create_time, remark) VALUES (4, '2020-07-01', 'c');

4、檢視分割區情況

postgres=# select * from tbl_log;
 id |     create_time     | remark 
----+---------------------+--------
  1 | 2018-02-01 00:00:00 | a
  2 | 2018-03-01 00:00:00 | b
  3 | 2018-04-01 00:00:00 | d
  4 | 2020-07-01 00:00:00 | c
(4 rows)
postgres=# select * from tbl_log_p201801;
 id | create_time | remark 
----+-------------+--------
(0 rows)
postgres=# select * from tbl_log_p201802;
 id |     create_time     | remark 
----+---------------------+--------
  1 | 2018-02-01 00:00:00 | a
(1 row)
postgres=# select * from tbl_log_p201803;
 id |     create_time     | remark 
----+---------------------+--------
  2 | 2018-03-01 00:00:00 | b
(1 row)
                      
postgres=# select * from tbl_log_default; 
 id |     create_time     | remark 
----+---------------------+--------
  3 | 2018-04-01 00:00:00 | d
  4 | 2020-07-01 00:00:00 | c
(2 rows)
postgres=#

2、有default 分割區，再加分割區

因為有default 分割區，再加分割區，所以會報錯

postgres=# CREATE TABLE tbl_log_p201804 PARTITION OF tbl_log FOR VALUES FROM ('2018-04-01') TO ('2018-05-01');
ERROR:  updated partition constraint for default partition "tbl_log_default" would be violated by some row

解決辦法：

以上新增分割區報錯，需要解綁default分割區，之後再新增，如下

1、解綁Default分割區

postgres=# ALTER TABLE tbl_log DETACH PARTITION tbl_log_default;
ALTER TABLE

2、建立想要的分割區

postgres=# CREATE TABLE tbl_log_p201804 PARTITION OF tbl_log FOR VALUES FROM ('2018-04-01') TO ('2018-05-01');
CREATE TABLE

3、分割區建立成功，分割區建立之後需把DEFAULT分割區連線。

連線DEFAULT分割區報錯，如下:

postgres=# ALTER TABLE tbl_log ATTACH PARTITION tbl_log_default DEFAULT;
ERROR:  partition constraint is violated by some row
postgres=# INSERT INTO tbl_log_p201804 SELECT * FROM tbl_log_default;
ERROR:  new row for relation "tbl_log_p201804" violates partition constraint
DETAIL:  Failing row contains (4, 2020-07-01 00:00:00, c).

因為tbl_log_default分割區內有2018-04-01的資料，把這個資料從tbl_log_default中匯出到對應的分割區，並清理tbl_log_default中的對應的資料

postgres=# INSERT INTO tbl_log_p201804 SELECT * FROM tbl_log_default where create_time>='2018-04-01' and create_time<'2018-05-01';
INSERT 0 1
postgres=# delete from tbl_log_default where create_time>='2018-04-01' and create_time<'2018-05-01';
DELETE 1

4、再次連線DEFAULT分割區成功

postgres=# ALTER TABLE tbl_log ATTACH PARTITION tbl_log_default DEFAULT;
ALTER TABLE

3、沒有default的分割區

建立沒有default的分割區，當插入的資料超過規劃好的分割區的時候會報錯

1、建立1月份分割區

create table tbl_log2
(
    id          serial,
    create_time timestamp(0) without time zone,
    remark      char(1)
) PARTITION BY RANGE (create_time);
CREATE TABLE tbl_log2_p201801 PARTITION OF tbl_log2 FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');

插入2月的資料就會報錯

postgres=# INSERT INTO tbl_log2(id, create_time, remark) VALUES (1, '2018-01-01', 'a');
INSERT 0 1
postgres=# INSERT INTO tbl_log2(id, create_time, remark) VALUES (1, '2018-02-01', 'a');
ERROR:  no partition of relation "tbl_log2" found for row
DETAIL:  Partition key of the failing row contains (create_time) = (2018-02-01 00:00:00).

4、給分割區表ddl

4.1、在原來沒有主鍵的分割區表加主鍵

結論：

1、在主表加主鍵，主鍵為僅僅想要的主鍵，會報錯，需要用想要的主鍵+分割區鍵組合為主鍵

2、分割區表可以單獨新增主鍵

1.1、在主表加主鍵，主鍵為僅僅想要的主鍵，報錯如下 must include all partitioning columns

postgres=# alter table tbl_log add primary key(id);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "tbl_log" lacks column "create_time" which is part of the partition key.
postgres=# alter table tbl_log add primary key(id)

1.2、在主表新增主鍵需要是想要的主鍵+分割區鍵

postgres=# alter table tbl_log add primary key (id,create_time);
ALTER TABLE
postgres=# d tbl_log
                                    Partitioned table "public.tbl_log"
   Column    |              Type              | Collation | Nullable |               Default               
-------------+--------------------------------+-----------+----------+-------------------------------------
 id          | integer                        |           | not null | nextval('tbl_log_id_seq'::regclass)
 create_time | timestamp(0) without time zone |           | not null | 
 remark      | character(1)                   |           |          | 
 name        | character varying(2)           |           |          | 
Partition key: RANGE (create_time)
Indexes:
    "tbl_log_pkey" PRIMARY KEY, btree (id, create_time)
Number of partitions: 5 (Use d+ to list them.)
postgres=# d tbl_log_p201801
                                      Table "public.tbl_log_p201801"
   Column    |              Type              | Collation | Nullable |               Default               
-------------+--------------------------------+-----------+----------+-------------------------------------
 id          | integer                        |           | not null | nextval('tbl_log_id_seq'::regclass)
 create_time | timestamp(0) without time zone |           | not null | 
 remark      | character(1)                   |           |          | 
 name        | character varying(2)           |           |          | 
Partition of: tbl_log FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2018-02-01 00:00:00')
Indexes:
    "tbl_log_p201801_pkey" PRIMARY KEY, btree (id, create_time)

1.3、可以給分割區表單獨新增主鍵

postgres=# alter table tbl_log_p201801 add primary key (id);
ALTER TABLE
postgres=# d tbl_log_p201801
                                      Table "public.tbl_log_p201801"
   Column    |              Type              | Collation | Nullable |               Default               
-------------+--------------------------------+-----------+----------+-------------------------------------
 id          | integer                        |           | not null | nextval('tbl_log_id_seq'::regclass)
 create_time | timestamp(0) without time zone |           |          | 
 remark      | character(1)                   |           |          | 
 name        | character varying(2)           |           |          | 
Partition of: tbl_log FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2018-02-01 00:00:00')
Indexes:
    "tbl_log_p201801_pkey" PRIMARY KEY, btree (id)
postgres=#

4.2、建立分割區表時，就指定主鍵

主鍵不包括分割區鍵，報錯提示must include all partitioning columns

create table tbl_log2
(
    id          int,
    create_time timestamp(0) without time zone,
    remark      char(1),
    primary key (id)
);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "tbl_log2" lacks column "create_time" which is part of the partition key.

修改語句，新增分割區鍵也為主鍵，建立成功

create table tbl_log2
(
    id          int,
    create_time timestamp(0) without time zone,
    remark      char(1),
    primary key (id,create_time)
) PARTITION BY RANGE (create_time);
CREATE TABLE

4.3、分割區表加欄位，修改欄位

1、加欄位，可以成功新增，在主表加欄位，分割區表會自動新增

postgres=# alter table tbl_log add name varchar(2);
ALTER TABLE
postgres=# d tbl_log;
                                    Partitioned table "public.tbl_log"
   Column    |              Type              | Collation | Nullable |               Default               
-------------+--------------------------------+-----------+----------+-------------------------------------
 id          | integer                        |           | not null | nextval('tbl_log_id_seq'::regclass)
 create_time | timestamp(0) without time zone |           |          | 
 remark      | character(1)                   |           |          | 
 name        | character varying(2)           |           |          | 
Partition key: RANGE (create_time)
Number of partitions: 5 (Use d+ to list them.)
postgres=# d tbl_log_p201801;                     
                                      Table "public.tbl_log_p201801"
   Column    |              Type              | Collation | Nullable |               Default               
-------------+--------------------------------+-----------+----------+-------------------------------------
 id          | integer                        |           | not null | nextval('tbl_log_id_seq'::regclass)
 create_time | timestamp(0) without time zone |           |          | 
 remark      | character(1)                   |           |          | 
 name        | character varying(2)           |           |          | 
Partition of: tbl_log FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2018-02-01 00:00:00')

2、直接在分割區表加欄位會報錯

postgres=# alter table tbl_log_p201801 add name2 varchar(2);
ERROR:  cannot add column to a partition

3、修改欄位

postgres=# alter table tbl_log  alter column remark type varchar(10);
ALTER TABLE
postgres=# d tbl_log;
                                    Partitioned table "public.tbl_log"
   Column    |              Type              | Collation | Nullable |               Default               
-------------+--------------------------------+-----------+----------+-------------------------------------
 id          | integer                        |           | not null | nextval('tbl_log_id_seq'::regclass)
 create_time | timestamp(0) without time zone |           | not null | 
 remark      | character varying(10)          |           |          | 
 name        | character varying(2)           |           |          | 
Partition key: RANGE (create_time)
Indexes:
    "tbl_log_pkey" PRIMARY KEY, btree (id, create_time)
Number of partitions: 5 (Use d+ to list them.)
postgres=# d tbl_log_p201801
                                      Table "public.tbl_log_p201801"
   Column    |              Type              | Collation | Nullable |               Default               
-------------+--------------------------------+-----------+----------+-------------------------------------
 id          | integer                        |           | not null | nextval('tbl_log_id_seq'::regclass)
 create_time | timestamp(0) without time zone |           | not null | 
 remark      | character varying(10)          |           |          | 
 name        | character varying(2)           |           |          | 
Partition of: tbl_log FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2018-02-01 00:00:00')
Indexes:
    "tbl_log_p201801_pkey" PRIMARY KEY, btree (id, create_time)
postgres=#