PostgreSQL 11 新特性之跨分区移动数据

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目录

在 PostgreSQL 10 中，如果 UPDATE 语句修改了分区字段的值，导致数据需要移动到其他分区时，语句将会失败。

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

   
以下语句更新分区字段 c1，导致记录（c1 = 50）需要移动到分区 rtable200；不过语句执行失败。

-- 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 能够正确处理更新分区字段的操作：

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

根据提交记录，这种 UPDATE 语句实际上分为两步执行：从旧的分区中 DELETE 相应记录，在新的分区中INSERT 相应记录。对于并发场景，这种方式可能会产生意料之外的行为。官方已经记录该问题，并且等待提交补丁。

另外，跨分区移动数据的 UPDATE 语句将会导致触发器的执行顺序更加复杂，相关信息可以参考“PostgreSQL 11 新特性之分区表行级触发器”。

官方文档：Table Partitioning