我正在努力将分区附加到具有主键的表。
我有一个分区表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";
我做错了什么? 有谁知道它的工作原理吗? 可以告诉我如何将分区附加到主表而无需创建两次唯一索引吗?