How to attach partition with primary key

[Philipp Faster <phfaster@xxxxxxxxx>]

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 5^th 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