public inbox for [email protected]
help / color / mirror / Atom feedFrom: Philipp Faster <[email protected]>
To: [email protected] <[email protected]>
Subject: How to attach partition with primary key
Date: Tue, 18 Jun 2024 05:32:39 +0000
Message-ID: <AM9P192MB1329025AE9E74E5B7306E93BF4CE2@AM9P192MB1329.EURP192.PROD.OUTLOOK.COM> (raw)
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
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: How to attach partition with primary key
In-Reply-To: <AM9P192MB1329025AE9E74E5B7306E93BF4CE2@AM9P192MB1329.EURP192.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox