PostgreSQL范围分区表数据同时存在于父表与子表问题咨询

你遇到的异常是额外添加的INSERT RULE覆盖了PostgreSQL声明式分区的默认路由逻辑导致的，具体原因如下：

• PostgreSQL自带的声明式分区表本身会自动根据分区键created_date的值将插入数据路由到对应分区，不需要额外创建RULE做重定向
• 你创建的RULE强制将所有插入父表的请求全部转发到transactions_y2021s02分区，无论created_date的值是否匹配该分区的范围，导致所有测试数据都写入了这一个分区
• 父表transactions本身是逻辑虚表，不存储实际数据，查询父表默认会返回所有子分区的合并结果，因此当所有数据都存在transactions_y2021s02时，查询父表和该子表的结果完全一致

1. 首先删除冲突的INSERT RULE

DROP RULE IF EXISTS transactions_insert_y2021s02 ON transactions;

2. 清空之前错误写入分区的测试数据

TRUNCATE TABLE transactions_y2021s02;

3. 插入测试数据时显式指定created_date值匹配你创建的分区范围（如果直接用默认值now()，当前时间不在2021年区间的话会因为找不到对应分区报错）

INSERT INTO transactions (amount , value, created_date)
VALUES 
(1,'xxxxxxxx', '2021-03-01 10:00:00+08'),
(2,'yyyyyyyyy', '2021-08-01 14:00:00+08'),
(3,'zzzzzzz', '2021-10-01 16:00:00+08');

4. 验证分区路由结果

• 父表查询返回全量3条数据属于正常逻辑，本身就是所有分区的聚合视图
• 分别查询两个子分区验证路由是否正确：
  • SELECT * FROM transactions_y2021s01 仅返回2021年上半年的1条数据
  • SELECT * FROM transactions_y2021s02 仅返回2021年下半年的2条数据

补充：如果需要支持插入当前时间的业务数据，需要提前创建对应时间范围的分区，否则插入时会触发no partition of relation "transactions" found for row错误。

内容的提问来源于stack exchange，提问作者Code Geas Coder