PostgreSQL 使用现有行和引用完整性创建表分区

Question

我正在尝试使用 Postgresql 10.8 中的现有行对表进行分区。结构是这样的:

我正在尝试创建表 Item 的分区，它大约有 5mill 行。我使用这些命令创建分区:

CREATE TABLE item_1 (CHECK (id >0 AND id <1000001)) INHERITS (item);
CREATE TABLE item_2 (CHECK (id >1000000 AND id <2000001)) INHERITS (item);
...

然后是规则:

 CREATE RULE item_1_rule AS ON INSERT TO item WHERE (id >0 AND id <1000001) DO INSTEAD INSERT INTO item_1 VALUES (NEW.*);
 CREATE RULE item_2_rule AS ON INSERT TO item WHERE (id >1000000 AND id <2000001) DO INSTEAD INSERT INTO item_2 VALUES (NEW.*);
...

然后迁移到分区表:

INSERT INTO item_1 SELECT * FROM item WHERE (id >0 AND id <1000001);
INSERT INTO item_2 SELECT * FROM item WHERE (id >1000000 AND id <2000001);
...

最后我尝试删除 Item 表中的所有行:

DELETE FROM ONLY item;

但我收到此错误:

ERROR: update or delete on table "item" violates foreign key constraint >"item_audit_item_id_fkey" on table "item_audit" SQL state: 23503 Detail: Key (id)=(1) is still referenced from table "item_audit".

那么是否可以从 main 表 Item 中删除数据，只保留分区表上的行？还有其他替代方法可以以不同的方式进行分区吗？

Answer 1

如果您使用继承分区，您将无法让外键指向分区表。您必须先删除约束，然后才能删除行。

我建议使用声明性分区。如果升级到 v11 或更高版本，您可以有一个指向分区表的外键，但是由于分区表上的主键必须包含分区键，因此您必须添加该外键列也指向外键。

由于分区对于批量数据删除最有用，因此以相同的方式对 item_audit 进行分区可能是有意义的。