PostgreSQL 11 新特性之跨分區(qū)移動(dòng)數(shù)據(jù)

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

在 PostgreSQL 10 中，如果 UPDATE 語(yǔ)句修改了分區(qū)字段的值，導(dǎo)致數(shù)據(jù)需要移動(dòng)到其他分區(qū)時(shí)，語(yǔ)句將會(huì)失敗。

-- PostgreSQL 10
CREATE TABLE rtable(c1 INT, c2 VARCHAR(10)) PARTITION BY RANGE(c1);
CREATE TABLE rtable100 PARTITION OF rtable FOR VALUES FROM (1) TO (100);
CREATE TABLE rtable200 PARTITION OF rtable FOR VALUES FROM (101) TO (200);
INSERT INTO rtable(c1, c2) VALUES (50, 'val50');
SELECT * FROM rtable100;
 c1 |  c2   
----+-------
 50 | val50
(1 row)

   
以下語(yǔ)句更新分區(qū)字段 c1，導(dǎo)致記錄（c1 = 50）需要移動(dòng)到分區(qū) rtable200；不過(guò)語(yǔ)句執(zhí)行失敗。

-- PostgreSQL 10
UPDATE rtable
postgres-# SET c1 = c1 + 100
postgres-# WHERE c1 = 50;
ERROR:  new row for relation "rtable100" violates partition constraint
DETAIL:  Failing row contains (150, val50).

PostgreSQL 11 能夠正確處理更新分區(qū)字段的操作：

-- PostgreSQL 11
CREATE TABLE rtable(c1 INT, c2 VARCHAR(10)) PARTITION BY RANGE(c1);
CREATE TABLE rtable100 PARTITION OF rtable FOR VALUES FROM (1) TO (100);
CREATE TABLE rtable200 PARTITION OF rtable FOR VALUES FROM (101) TO (200);
INSERT INTO rtable(c1, c2) VALUES (50, 'val50');
SELECT * FROM rtable100;
 c1 |  c2   
----+-------
 50 | val50
(1 row)

UPDATE rtable
   SET c1 = c1 + 100
 WHERE c1 = 50;
SELECT * FROM rtable200;
 c1  |  c2   
-----+-------
 150 | val50
(1 row)

根據(jù)提交記錄，這種 UPDATE 語(yǔ)句實(shí)際上分為兩步執(zhí)行：從舊的分區(qū)中 DELETE 相應(yīng)記錄，在新的分區(qū)中INSERT 相應(yīng)記錄。對(duì)于并發(fā)場(chǎng)景，這種方式可能會(huì)產(chǎn)生意料之外的行為。官方已經(jīng)記錄該問(wèn)題，并且等待提交補(bǔ)丁。

另外，跨分區(qū)移動(dòng)數(shù)據(jù)的 UPDATE 語(yǔ)句將會(huì)導(dǎo)致觸發(fā)器的執(zhí)行順序更加復(fù)雜，相關(guān)信息可以參考“PostgreSQL 11 新特性之分區(qū)表行級(jí)觸發(fā)器”。

官方文檔：Table Partitioning