PG 分区表的缺陷

简介

好久没发文，是最近我实在不知道写点啥。随着国产化进程，很多 oracle 都在进行迁移，最近遇到了一个分区表迁移之后唯一性的问题。oracle 数据库中创建主键或者唯一索引，不需要引用分区键，但是 PG 就不行，PG 创建主键或者唯一键需要带上分区键，这样一来其唯一性就受到了破坏。

PG 创建测试语句

 postgres=# CREATE TABLE hash_sales (
postgres(#     id         INT,
postgres(#     sale_date  DATE,
postgres(#     amount     NUMERIC,
postgres(#     PRIMARY KEY (id)  -- 主键必须包含分区键
postgres(# ) PARTITION BY hash(sale_date );
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "hash_sales" lacks column "sale_date" which is part of the partition key.
postgres=#

报错信息：
ERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: PRIMARY KEY constraint on table "hash_sales" lacks column "sale_date" which is part of the partition key.
创建唯一约束也是如此，需要带上分区键。
这里我们使用 AI 看看 AI 的建议

AI 的回答也是不太让人满意。如果不能实现 PRIMARY KEY (id) ，那么整体的业务属性都将受到破坏
在分区表中创建 UNIQUE 约束，同样也是需要带上分区键的
报错信息：

postgres=# CREATE TABLE hash_sales ( postgres(# id INT, postgres(# sale_date DATE, postgres(# amount NUMERIC, postgres(# UNIQUE (id) postgres(# ) PARTITION BY hash(sale_date); ERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: UNIQUE constraint on table "hash_sales" lacks column "sale_date" which is part of the partition key. postgres=#
其方法 3 在子表加主键。这个方法显然是无法满足的，并不能保证 ID 的全局唯一性。

下面是其他 AI 的回答

以上回答的 1、4 是不满足 ID 的全局唯一的，这里说的 2、3 方法。
创建触发器，相当于用 PG10 版本以前的继承式分区的方法，使用触发器显然会较大影响到其性能。看是可行实际维护却并不是那么方便。
第三个回答，使用一个小表，这个应用层的框架就要跟着一起，就像在架构上增加了一个布隆过滤器。
这里 AI 还回答了使用外键约束，同事一看能创建成功，感觉可以用了。实际外键只能保证其值存在的合理性，并不能保证其值存在的全局唯一性。

postgres=# CREATE TABLE id_tbale (
postgres(# id varchar(80),
postgres(# location point,
postgres(# primary key(id)
postgres(# );
CREATE TABLEpostgres=# CREATE TABLE hash_sales (
postgres(# id varchar(80) REFERENCES public.id_tbale(id),
postgres(# sale_date DATE,
postgres(# amount NUMERIC
postgres(# ) PARTITION BY hash(sale_date);
CREATE TABLE 

目前测试在 PG16、15 均存在这个问题。目前postgresql17\18的released中也并没有对这个问题进行调整。