Subject: How to attach partition with primary key

From: Philipp Faster <[email-masked]>
Date: Tue, 18 Jun 2024 05:32:39 +0000

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

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 18 Jun 2024 11:46:27 +0200

On 2024-Jun-18, Philipp Faster wrote:

> 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.

Yeah.  Your case sounds like the primary key in the partitioned table
has some slight definition difference from the stock one, which makes
the one you create in the partition not an exact match.  I'd suggest to
look at what "pg_dump -s" emits as definition of the table with a
magnifying glass to search for such differences.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

---

From: Philipp Faster <[email-masked]>
Date: Tue, 18 Jun 2024 20:58:55 +0700

Hey Alvaro and everyone,

Thank you for replying! I've checked `pg_dump -s`, but it didn't help - the
definition here was exactly the same (except order, but I didn't notice it
since in my actual schema there are thousands of rows).

I've done a bad job describing my issue in the first place: I left out a
key definition statement that I thought doesn't play any role in the issue:
another unique index on the same columns as PK.

My actual script was:

```sql
CREATE TABLE "Transactions_202405" (LIKE "Transactions" INCLUDING DEFAULTS);
INSERT INTO "Transactions_202405" SELECT * FROM
"Transactions_202405_parted";
alter table "Transactions_202405" add primary key (id, "createdAt");
alter table "Transactions_202405" add foreign key (uid) references "User"
on update cascade on delete restrict;
create index "Transactions_202405_createdAt_idx" on "Transactions_202405"
("createdAt" desc);
create index "Transactions_202405_type_idx" on "Transactions_202405" (type);
create index "Transactions_202405_uid_idx" on "Transactions_202405" (uid);
create unique index "Transactions_202405_id_createdAt_idx" on
"Transactions_202405" (id desc, "createdAt" desc);
alter table "Transactions_202405" add constraint
"Transactions_202405_check" check ("createdAt">='2024-05-01' and
"createdAt"<'2024-06-01');
alter table "Transactions" attach partition "Transactions_202405" for
values from ('2024-05-01') TO ('2024-06-01');
alter table "Transactions_202405" drop constraint
"Transactions_202405_check";
```

So as you can see on the 8th line, there is a definition of another unique
index. I left it out since I wanted to provide the minimal code for the
problem. This index is defined on all partitions and the main partitioned
table.

The solution I found is the following: to move `alter table ... add primary
key ...` statement after the unique index definition. After that it starts
to work like a charm. I tried all combinations of row order, but only when
I move this line after the unique index - it starts working.

Interesting thing is that if I define PK before the unique index and then
drop it and redefine after the unique index, then the code still doesn't
work. This behaviour smells like a bug on the PostgreSQL side...

I've found a minimal reproducible example:

```sql
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
) partition by RANGE ("createdAt");
create unique index "Transactions_id_createdAt_idx" on "Transactions" (id
desc, "createdAt" desc);
alter table "Transactions" add primary key (id, "createdAt");

create table "Transactions_202403" (LIKE "Transactions" INCLUDING DEFAULTS);
alter table "Transactions_202403" add primary key (id, "createdAt");
create unique index "Transactions_202403_id_createdAt_idx" on
"Transactions_202403" (id desc, "createdAt" desc);
alter table "Transactions" attach partition "Transactions_202403" for
values from ('2024-03-01') to ('2024-04-01');
```

If I change the order of PK and unique index statements either in the first
block or second - the script breaks. Seems like PostgreSQL requires you to
define constraints and indexes in exactly the same order as the partitioned
table. Sounds buggy, but kinda logical.

Thank you and sorry for the incorrect question definition.