Hello everyone!
Using Postgres v15.5. I'm struggling to attach a partition to a table with a
primary key.
I have a partitioned table `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);
```
I create a new partition every month which is a partitioned table itself. And
then each day of the month I create a partition for a day.
```
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');
```
In the beginning of the next month, I want to create a normal partition for
that month, copy all transactions and drop the partitioned month to reduce
number of partitions.
I'm trying to use the following script:
```
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";
```
At the pre-last row, when I try to attach the newly created partition,
PostgreSQL blames me for trying to create a second primary key on table
"Transactions_202404":
```
[42P16] ERROR: multiple primary keys for table "Transactions_202404" are not
allowed
```
As I understand, PostgreSQL refuses to use existing primary key for some reason
and tries to create its own as a children of "Transactions" table's primary key.
If I try to create a UNIQUE key for my new partition and then connect it to the
main table, then it works, but I'm missing PK on the new partition.
The thing is, if I do all the steps with unique key and then create a PK on the
already attached table, then reattach it so Postgres takes PK as children of
main PK, then it works, check:
```
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";
```
When I connect the partition with unique key for the first time, PostgreSQL
(sometimes) finds it as children of main table PK (I see that in pg_inherits).
Why sometimes? When I tried to do that again with partition for 5th month, it
failed to use unique constraint to attach to the main table and started
creating index, exclusive-blocking whole main table. I don’t really understand
why it stopped to use unique constraint in the second attempt.
What am I doing wrong? Could someone with a good knowledge of how it works tell
me how can I attach a partition to the main table without creating an unique
index twice?
Thank you in advance!
Philipp
