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.
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.
On Tue, Jun 18, 2024 at 4:46 PM Alvaro Herrera <alvherre@xxxxxxxxxxxxxx> wrote:
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/
