Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sJZMh-005c2o-2y for pgsql-general@arkaria.postgresql.org; Tue, 18 Jun 2024 13:59:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sJZMe-00DcSJ-Oj for pgsql-general@arkaria.postgresql.org; Tue, 18 Jun 2024 13:59:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sJZMe-00DcS5-Aa for pgsql-general@lists.postgresql.org; Tue, 18 Jun 2024 13:59:13 +0000 Received: from mail-ua1-x934.google.com ([2607:f8b0:4864:20::934]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sJZMb-002IKr-LQ for pgsql-general@lists.postgresql.org; Tue, 18 Jun 2024 13:59:12 +0000 Received: by mail-ua1-x934.google.com with SMTP id a1e0cc1a2514c-80b755c6c06so1860105241.2 for ; Tue, 18 Jun 2024 06:59:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718719147; x=1719323947; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=AUhOqeH9hI/frpaDNCV7NPmM2U/Bo/5CFxOrhIHVxbc=; b=Y/jZiGk4pj09tI7SMwySI+cKVBwjPTWznAkLEIOa9Tf/sYgGtzgDIBc3+/V0sSXeS5 caIJy92MpCrle5wtGRIjRmeVdwK0w5qs7QHiN9fWeNhlTjRsr3U1PiD1vgeez10yVkJd PS9ZBMrN4hRCKWcV8IUbYpSz9y2z6nlV5yBXw7j2oVWEUTHXQmH3pS2kSP8v7oCS/+z+ IrQbWzxSGoxm206RXOq0WGfwWotElV52TYZhABIKpv3gyX8AKLypr/wP9nJcg6jlBDe1 F5pyWSDVmoSmMrd+EXe+3z3Gx9xfFv1D439yP0UtZ2s4uScL0dk293q/xNRFf+qqLGpE dXKQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718719147; x=1719323947; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=AUhOqeH9hI/frpaDNCV7NPmM2U/Bo/5CFxOrhIHVxbc=; b=TZEqiPwGC+m1VcYivxob7Bc/7qzKC07VH+/dJz/h5ZLqWHfI56QplInjo14MlSTpeR i1g2BKnGWJWa/BajY9sxbqZGilaauwwVRRJpfAeEUPW8ZkVnI9g3j3Kbp1exXCkuPALf GOhUBG1TwlxDhEqR2RfC1wxRmz3l4prw018kZYPXA5jqFjCaKMu6RyDM9aaJWnM9GFGO 2qHWDKY2QiIvF9zPojVsAZuRiWBgeN+Z0KVLHRTzYUXumyBzn4Fuwv4sVUYxBAXaKjpC aGRkgZXQ7TVO68y1Uyj/cAF0m3N8GPQVBVEqiFiLF2ffIF50kgCp3Gq3uLLHGct4FNjg +StQ== X-Gm-Message-State: AOJu0YyBUju33GEVnHlM9zbFn6LG4NrzdE3fPpnJ4NDGacOr/jF9pMdj K2vhFjgOXDDgNTSML7ZEN+0VKRPHPXEfr/BzFxWKzDTsH22fLrO82kZynnGleAgJy9/dXQBT6yd BuUHEQe2O5uPvaFKKItJv2Fs/v6I= X-Google-Smtp-Source: AGHT+IG1acTQ6VBHD2mQDHLzpk3FULVMsfsxoCKYX25coen7hUi3+nCBGcOJFVz8VN8mrrYwDfzhyaEnJDcgyN9tnjE= X-Received: by 2002:a05:6122:46a1:b0:4ec:f4e0:c00c with SMTP id 71dfb90a1353d-4ee3e08d5e9mr11771348e0c.4.1718719146987; Tue, 18 Jun 2024 06:59:06 -0700 (PDT) MIME-Version: 1.0 References: <202406180946.d2f3oktuoyps@alvherre.pgsql> In-Reply-To: <202406180946.d2f3oktuoyps@alvherre.pgsql> From: Philipp Faster Date: Tue, 18 Jun 2024 20:58:55 +0700 Message-ID: Subject: Re: How to attach partition with primary key To: Alvaro Herrera Cc: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000399a2f061b2a7cc9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000399a2f061b2a7cc9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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">=3D'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=E2=80=AFPM Alvaro Herrera 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. > > -- > =C3=81lvaro Herrera PostgreSQL Developer =E2=80=94 > https://www.EnterpriseDB.com/ > --000000000000399a2f061b2a7cc9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hey Alvaro and everyone,

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

<= /div>
I've done a bad job describing my issue in the=C2=A0first pla= ce: I left out a key definition statement that I thought doesn't play a= ny role in the issue: another unique index on the same columns as PK.
My actual script was:

```sql
CREATE TABLE "Transactions_20= 2405" (LIKE "Transactions" INCLUDING DEFAULTS);
INSERT IN= TO "Transactions_202405" SELECT * FROM "Transactions_202405_= parted";
alter table "Transactions_202405" add primary ke= y (id, "createdAt");
alter table "Transactions_202405&quo= t; add foreign key (uid) references "User" on update cascade on d= elete restrict;
create index "Transactions_202405_createdAt_idx&quo= t; on "Transactions_202405" ("createdAt" desc);
crea= te index "Transactions_202405_type_idx" on "Transactions_202= 405" (type);
create index "Transactions_202405_uid_idx" o= n "Transactions_202405" (uid);
create unique index "Trans= actions_202405_id_createdAt_idx" on "Transactions_202405" (i= d desc, "createdAt" desc);
alter table "Transactions_2024= 05" add constraint "Transactions_202405_check" check ("= createdAt">=3D'2024-05-01' and "createdAt"<= 9;2024-06-01');
alter table "Transactions" attach partitio= n "Transactions_202405" for values from ('2024-05-01') TO= ('2024-06-01');
alter table "Transactions_202405" dro= p 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. Thi= s index is defined on all partitions and the main partitioned table.
The solution I found is the following: to move `alter table ... add primar= y key ...` statement after the unique index definition. After that it start= s to work like a charm. I tried all combinations of row order, but only whe= n I move this line after the unique index - it starts working.

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

I'v= e found a minimal reproducible example:

```sql
create table "= ;Transactions"
(
=C2=A0 =C2=A0 id =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0bigserial =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 not null,
=C2=A0= =C2=A0 uid =C2=A0 =C2=A0 =C2=A0 =C2=A0 uuid =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0not null,
=C2=A0 =C2=A0 type =C2=A0 = =C2=A0 =C2=A0 =C2=A0varchar(255) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0not null,=
=C2=A0 =C2=A0 amount =C2=A0 =C2=A0 =C2=A0numeric(26, 10) =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 not null,
=C2=A0 =C2=A0 "createdAt" timestamp(3) defaul= t CURRENT_TIMESTAMP =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0not null
) partition by RANGE ("= createdAt");
create unique index "Transactions_id_createdAt_id= x" on "Transactions" (id desc, "createdAt" desc);<= br>alter table "Transactions" add primary key (id, "createdA= t");

create table "Transactions_202403" (LIKE "T= ransactions" INCLUDING DEFAULTS);
alter table "Transactions_20= 2403" add primary key (id, "createdAt");
create unique in= dex "Transactions_202403_id_createdAt_idx" on "Transactions_= 202403" (id desc, "createdAt" desc);
alter table "Tr= ansactions" attach partition "Transactions_202403" for value= s from ('2024-03-01') to ('2024-04-01');
```

If I= change the order of PK and unique index statements either in the first blo= ck or second - the script breaks. Seems like PostgreSQL requires you to def= ine constraints and indexes in exactly the same order as the partitioned ta= ble. Sounds buggy, but kinda logical.

Thank yo= u and sorry for the incorrect question definition.

On Tue, Jun 18, 202= 4 at 4:46=E2=80=AFPM Alvaro Herrera <alvherre@alvh.no-ip.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.=C2=A0 Your case sounds like the primary key in the partitioned table<= br> has some slight definition difference from the stock one, which makes
the one you create in the partition not an exact match.=C2=A0 I'd sugge= st to
look at what "pg_dump -s" emits as definition of the table with a=
magnifying glass to search for such differences.

--
=C3=81lvaro Herrera=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0PostgreSQL Developer= =C2=A0 =E2=80=94=C2=A0 https://www.EnterpriseDB.com/
--000000000000399a2f061b2a7cc9--