Re: How to attach partition with primary key

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.

On Tue, Jun 18, 2024 at 4:46 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
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/
>