1

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 with is a partitioned table itself. Each day 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 drop partitioned month and create a normal partition for that 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";

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?

Improve this question
5
  • You are not saying how "Transactions_20240617" is created. Perhaps it already has a primary key? Then it would not be the ALTER TABLE ... ATTACH PARTITION that fails, but creating a second primary key. Commented Jun 18, 2024 at 6:16
  • @LaurenzAlbe hm, "Transactions_20240617" is created using LIKE statement for the previous day partition and etc etc. Initial day partition was created using LIKE statement from table that had the exact schema above without any indexes or keys. But later on, primary keys and indexes listed above were added. But I don't understand how does it make sense? Even if "Transactions_20240617" has PK, why would it make any difference, if LIKE statement doesn't include indexes and constraints? (ps. the migration fails exactly on ATTACH PARTITION statement). Commented Jun 18, 2024 at 6:55
  • 1
    When I introspect schema right before ATTACH statement, the table has exactly one PK that I've created on 3rd line. Commented Jun 18, 2024 at 6:57
  • I tried your example (substituting "Transactions" for the unknown "Transactions_202404_parted"), and I didn't get an error. So I cannot reproduce that. Commented Jun 18, 2024 at 11:33
  • So... @LaurenzAlbe my bad, I've done a bad job defining the question: I found a way to solve it, but the thing is, I didn't provide an important detail to the question itself - I had another index on my table: unique index (id desc, "createdAt" desc); I didn't provide it since I thought it doesn't play any role and so I can reduce size of provided code to minimum example. I was wrong - this was playing the key role. I will provide the solution I found in an answer rn Commented Jun 18, 2024 at 13:16

1 Answer 1

Reset to default
0

So, I'm going to answer my own question after I lost a day trying to define a solution and finally I found it just by trying different combinations of statements.

In my initial question, 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:

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 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 rows order, but only when I move this line after unique index - it starts working.

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

I've found a minimal reproducible example:

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 order of PK and unique index statements either in first block or second - the script breaks. Seems like PostgreSQL requires you to define constraints and indexes in exactly same order as the partitioned table. Sounds buggy, but kinda logical.

Sorry for noise, thank you for reading :D

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.