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 1sJRSa-004tOb-Fd for pgsql-general@arkaria.postgresql.org; Tue, 18 Jun 2024 05:32:48 +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 1sJRSY-007sl5-1w for pgsql-general@arkaria.postgresql.org; Tue, 18 Jun 2024 05:32:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sJRSX-007skx-Ej for pgsql-general@lists.postgresql.org; Tue, 18 Jun 2024 05:32:46 +0000 Received: from mail-ej1-x633.google.com ([2a00:1450:4864:20::633]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sJRSU-001rR6-QB for pgsql-general@lists.postgresql.org; Tue, 18 Jun 2024 05:32:45 +0000 Received: by mail-ej1-x633.google.com with SMTP id a640c23a62f3a-a6f11a2d18aso650959366b.2 for ; Mon, 17 Jun 2024 22:32:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718688761; x=1719293561; darn=lists.postgresql.org; h=mime-version:content-language:accept-language:message-id:date :thread-index:thread-topic:subject:to:from:from:to:cc:subject:date :message-id:reply-to; bh=0O7YcnF02hkcQxjFpFmUkQFTu42nZhiJ6uZg//ki8OI=; b=mMcgjP1ORNHarC55ux8s7wQisLndoeU2qIzyR39J3Pe5dHxZsPSOWpX9nDvvkdL+tp CyorCa7NvS2QXTpagJUh96JUWJrz1J4/DA4DfAUHbbnK2gKUeCptogno2lm/ZXN8Hzzi btszFVq8ioRfXYb5nPfGaN5Mo7j5QJ+axzP9/DjAK4Orv2G69YDJd2X05o/O8LUH1vRB n9OoNF52hme2I9b39WVe2lajCN2swyeuAo/lB+PMqhp/Lwx7z5y8R5QHKWtcE6XyEiui vAjd19uPoWJhhIzqqvKYnpQnf7VjUMqlaQJM08+ncWuk43F4gSveIEtCpi31g5DbrThK S7TA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718688761; x=1719293561; h=mime-version:content-language:accept-language:message-id:date :thread-index:thread-topic:subject:to:from:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=0O7YcnF02hkcQxjFpFmUkQFTu42nZhiJ6uZg//ki8OI=; b=S8twaOEBrBxXyNSr+3OWTSEFjKlnKxUVH7P3bsHOPoVMHMJQ9YGkGxl2Fulwk12MYA aewkutDqLDl0ezOIrgmhL0MVfaeuD35TglR8Yah6EAPD7q029TU6M5l0kKDzQB1NfNYs x+LtM5BkTdIaEWsyeaCA1VCv8u+hUnIElej9mJw5j4esjzCqMXvRRhnZ64l5pvifdQh/ 34PiCjG5BRVLs1/Izp3jbwp490Cm6Y4uyyBFDts3PtuLgVYTFklSwSGeG3I+3nPg3ZXw Z8ZA6M+iJ1JGzEmRgdaCpD2sdjmlBflvTOZ52PGcqtvOyRSvPXq21fJRs4vqmsSBZjUR i3Vg== X-Gm-Message-State: AOJu0YzX/Nl0hj6WC8aFJSIs2fe59cWYbXdqUvsoDRZyxKIedxMWKoBE K+HScwWWgusB1WdAExIb7IiFuuxjNyl2Yf4shmdy8+kcX3MVLdpU128IAUlnCToupg== X-Google-Smtp-Source: AGHT+IGL21+oavCoqD57xSwdTDWVo3dU9a+xj8CHcW4I9XeegIRcHYxNA8cLWfrlEwi3uRsRBl2i5w== X-Received: by 2002:a17:906:228c:b0:a6e:f7ee:b1fa with SMTP id a640c23a62f3a-a6f60de6110mr687285166b.72.1718688760658; Mon, 17 Jun 2024 22:32:40 -0700 (PDT) Received: from AM9P192MB1329.EURP192.PROD.OUTLOOK.COM ([2603:1026:c03:6087::5]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-a6f56db61ebsm580696666b.56.2024.06.17.22.32.40 for (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 17 Jun 2024 22:32:40 -0700 (PDT) From: Philipp Faster To: "pgsql-general@lists.postgresql.org" Subject: How to attach partition with primary key Thread-Topic: How to attach partition with primary key Thread-Index: AQHawT+fXOzc0vMxGEC6/Fqhhle0jw== X-MS-Exchange-MessageSentRepresentingType: 1 Date: Tue, 18 Jun 2024 05:32:39 +0000 Message-ID: Accept-Language: en-US Content-Language: en-GB X-MS-Has-Attach: X-MS-Exchange-Organization-SCL: -1 X-MS-TNEF-Correlator: X-MS-Exchange-Organization-RecordReviewCfmType: 0 Content-Type: multipart/alternative; boundary="_000_AM9P192MB1329025AE9E74E5B7306E93BF4CE2AM9P192MB1329EURP_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_AM9P192MB1329025AE9E74E5B7306E93BF4CE2AM9P192MB1329EURP_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable 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" de= sc); 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. A= nd then each day of the month I create a partition for a day. ``` CREATE TABLE "Transactions_20240618" (LIKE "Transactions_20240617" INCLUDIN= G DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES); ALTER TABLE "Transactions_202406_parted" ATTACH PARTITION "Transactions_202= 40618" 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" INCLU= DING 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">=3D'2024-04-01' and "createdAt"<'2024-05-01'); alter table "Transactions" detach partition "Transactions_202404_parted"; alter table "Transactions" attach partition "Transactions_202404" for value= s from ('2024-04-01') TO ('2024-05-01'); alter table "Transactions_202404" drop constraint "Transactions_202404_chec= k"; ``` At the pre-last row, when I try to attach the newly created partition, Post= greSQL blames me for trying to create a second primary key on table "Transa= ctions_202404": ``` [42P16] ERROR: multiple primary keys for table "Transactions_202404" are no= t allowed ``` As I understand, PostgreSQL refuses to use existing primary key for some re= ason and tries to create its own as a children of "Transactions" table's pr= imary 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 child= ren of main PK, then it works, check: ``` CREATE TABLE "Transactions_202404" (LIKE "Transactions_202404_parted" INCLU= DING 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">=3D'2024-04-01' and "createdAt"<'2024-05-01'); alter table "Transactions" detach partition "Transactions_202404_parted"; alter table "Transactions" attach partition "Transactions_202404" for value= s from ('2024-04-01') TO ('2024-05-01'); -- start of PK fix create unique index concurrently "Transactions_202404_pkey" on "Transaction= s_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_c= reatedAt_key"; -- drop the unnecessary unique key alter table "Transactions" attach partition "Transactions_202404" for value= s from ('2024-04-01') TO ('2024-05-01'); -- end of fix alter table "Transactions_202404" drop constraint "Transactions_202404_chec= k"; ``` 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_inheri= ts). Why sometimes? When I tried to do that again with partition for 5th mo= nth, it failed to use unique constraint to attach to the main table and sta= rted creating index, exclusive-blocking whole main table. I don=92t 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 --_000_AM9P192MB1329025AE9E74E5B7306E93BF4CE2AM9P192MB1329EURP_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable

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     &= nbsp;    bigserial       =             &nb= sp;            =             &nb= sp;          not null,

    uid     =     uuid        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p; not null,

    type     = ;   varchar(255)        &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;      not null,

    amount    &nb= sp; numeric(26, 10)         &n= bsp;            = ;            &n= bsp;            = ;  not null,

    "createdAt" timestamp(3= ) default CURRENT_TIMESTAMP        =             &nb= sp;     not null,

    primary key (id, "createdAt&= quot;)

) partition by RANGE ("createdAt")

 

create index "Transactions_createdAt_idx" = on "Transactions" ("createdAt" desc);

create index "Transactions_type_idx" on &q= uot;Transactions" (type);

create index "Transactions_uid_idx" on &qu= ot;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 pa= rtition for a day.

```

CREATE TABLE "Transactions_20240618" (LIKE= "Transactions_20240617" INCLUDING DEFAULTS INCLUDING CONSTRAINTS= INCLUDING INDEXES);

ALTER TABLE "Transactions_202406_parted" A= TTACH 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 transacti= ons and drop the partitioned month to re= duce number of partitions.

 

I'm trying to use the following script:

 

```

CREATE TABLE "Transactions_202404" (LIKE &= quot;Transactions_202404_parted" INCLUDING DEFAULTS);

INSERT INTO "Transactions_202404" SELECT *= FROM "Transactions_202404_parted";

alter table "Transactions_202404" add prim= ary 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 cons= traint "Transactions_202404_check" check ("createdAt"&g= t;=3D'2024-04-01' and "createdAt"<'2024-05-01');

alter table "Transactions" detach partitio= n "Transactions_202404_parted";

alter table "Transactions" attach partitio= n "Transactions_202404" for values from ('2024-04-01') TO ('2024-= 05-01');

alter table "Transactions_202404" drop con= straint "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 prima= ry 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 &q= uot;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 o= n 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 Pos= tgres takes PK as children of main PK, then it works, check:

 

```

CREATE TABLE "Transactions_202404" (LIKE &= quot;Transactions_202404_parted" INCLUDING DEFAULTS);

INSERT INTO "Transactions_202404" SELECT *= FROM "Transactions_202404_parted";

alter table "Transactions_202404" add uniq= ue (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 cons= traint "Transactions_202404_check" check ("createdAt"&g= t;=3D'2024-04-01' and "createdAt"<'2024-05-01');

alter table "Transactions" detach partitio= n "Transactions_202404_parted";

alter table "Transactions" attach partitio= n "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&q= uot;);

alter table "Transactions_202404" add prim= ary key using index "Transactions_202404_pkey";

alter table "Transactions" detach partitio= n "Transactions_202404";

alter table "Transactions_202404" drop con= straint "Transactions_202404_id_createdAt_key"; -- drop the unnec= essary unique key

alter table "Transactions" attach partitio= n "Transactions_202404" for values from ('2024-04-01') TO ('2024-= 05-01');

-- end of fix

alter table "Transactions_202404" drop con= straint "Transactions_202404_check";

```

 

When I connect the partition wi= th unique key for the first time, PostgreSQL (sometimes) finds it as childr= en of main table PK (I see that in pg_inherits). Why sometimes? When I trie= d 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=92t really understand why it stopped to use unique constraint in the= second attempt.

 

What am I doing wrong? Could someone with a good kno= wledge of how it works tell me how can I attach a partition to the main tab= le without creating an unique index twice?

 

Thank you in advance!

Philipp

--_000_AM9P192MB1329025AE9E74E5B7306E93BF4CE2AM9P192MB1329EURP_--