我正在努力将分区附加到具有主键的表。
我有一个分区表Transactions
:
create table "Transactions"
(
id bigserial not null,
uid uuid not null,
type varchar(255) not null,
amount numeric(26, 10) not null,
"createdAt" timestamp(3) default CURRENT_TIMESTAMP not null,
primary key (id, "createdAt")
) partition by RANGE ("createdAt")
create index "Transactions_createdAt_idx" on "Transactions" ("createdAt" desc);
create index "Transactions_type_idx" on "Transactions" (type);
create index "Transactions_uid_idx" on "Transactions" (uid);
我每个月都会创建一个新分区,它本身就是一个分区表。每天我都会为一天创建一个分区。
CREATE TABLE "Transactions_20240618" (LIKE "Transactions_20240617" INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
ALTER TABLE "Transactions_202406_parted" ATTACH PARTITION "Transactions_20240618" FOR VALUES FROM ('2024-06-18') TO ('2024-06-19');
在下个月开始时,我想删除分区月份并为该月份创建一个普通分区以减少分区数量。
我正在尝试使用以下脚本:
CREATE TABLE "Transactions_202404" (LIKE "Transactions_202404_parted" INCLUDING DEFAULTS);
INSERT INTO "Transactions_202404" SELECT * FROM "Transactions_202404_parted";
alter table "Transactions_202404" add primary key (id, "createdAt");
create index "Transactions_202404_createdAt_idx" on "Transactions_202404" ("createdAt" desc);
create index "Transactions_202404_type_idx" on "Transactions_202404" (type);
create index "Transactions_202404_uid_idx" on "Transactions_202404" (uid);
alter table "Transactions_202404" add constraint "Transactions_202404_check" check ("createdAt">='2024-04-01' and "createdAt"<'2024-05-01');
alter table "Transactions" detach partition "Transactions_202404_parted";
alter table "Transactions" attach partition "Transactions_202404" for values from ('2024-04-01') TO ('2024-05-01');
alter table "Transactions_202404" drop constraint "Transactions_202404_check";
在最后一行之前,当我尝试连接新创建的分区时,PostgreSQL 指责我试图在表“Transactions_202404”上创建第二个主键:
[42P16] ERROR: multiple primary keys for table "Transactions_202404" are not allowed
据我了解,PostgreSQL 由于某种原因拒绝使用现有的主键,并尝试创建自己的主键作为“Transactions”表主键的子键。
如果我尝试为新分区创建一个唯一键,然后将其连接到主表,那么它可以工作,但是在新分区上缺少 PK。
问题是,如果我使用唯一键执行所有步骤,然后在已附加的表上创建一个 PK,然后重新附加它,以便 Postgres 将 PK 作为主 PK 的子项,那么它就可以工作,请检查:
CREATE TABLE "Transactions_202404" (LIKE "Transactions_202404_parted" INCLUDING DEFAULTS);
INSERT INTO "Transactions_202404" SELECT * FROM "Transactions_202404_parted";
alter table "Transactions_202404" add unique (id, "createdAt");
create index "Transactions_202404_createdAt_idx" on "Transactions_202404" ("createdAt" desc);
create index "Transactions_202404_type_idx" on "Transactions_202404" (type);
create index "Transactions_202404_uid_idx" on "Transactions_202404" (uid);
alter table "Transactions_202404" add constraint "Transactions_202404_check" check ("createdAt">='2024-04-01' and "createdAt"<'2024-05-01');
alter table "Transactions" detach partition "Transactions_202404_parted";
alter table "Transactions" attach partition "Transactions_202404" for values from ('2024-04-01') TO ('2024-05-01');
-- start of PK fix
create unique index concurrently "Transactions_202404_pkey" on "Transactions_202404" (id, "createdAt");
alter table "Transactions_202404" add primary key using index "Transactions_202404_pkey";
alter table "Transactions" detach partition "Transactions_202404";
alter table "Transactions_202404" drop constraint "Transactions_202404_id_createdAt_key"; -- drop the unnecessary unique key
alter table "Transactions" attach partition "Transactions_202404" for values from ('2024-04-01') TO ('2024-05-01');
-- end of fix
alter table "Transactions_202404" drop constraint "Transactions_202404_check";
我做错了什么? 有谁知道它的工作原理吗? 可以告诉我如何将分区附加到主表而无需创建两次唯一索引吗?
因此,我花了一天时间尝试定义一个解决方案,最后通过尝试不同的语句组合找到了解决方案,现在我要回答我自己的问题了。
在我最初的问题中,我遗漏了一个我认为在问题中不起任何作用的关键定义语句:与 PK 相同的列上的另一个唯一索引。
我的实际脚本是:
因此,正如您在第 8 行看到的,还有另一个唯一索引的定义。我省略了它,因为我想为这个问题提供最少的代码。此索引在所有分区和主分区表上定义。
我找到的解决方案如下:将
alter table ... add primary key ...
语句移动到唯一索引定义之后。之后,它开始像魔法一样工作。我尝试了所有行顺序的组合,但只有当我将此行移动到唯一索引之后时 - 它才开始工作。有趣的是,如果我在唯一索引之前定义 PK,然后删除它并在唯一索引之后重新定义,那么代码仍然不起作用。这种行为看起来像是 PostgreSQL 方面的一个错误……
我发现了一个最小的可重现示例:
如果我更改第一个块或第二个块中的 PK 和唯一索引语句的顺序 - 脚本就会中断。似乎 PostgreSQL 要求您按照与分区表完全相同的顺序定义约束和索引。听起来有点问题,但有点合乎逻辑。
抱歉打扰了,感谢您的阅读 :D