Re: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition - Mailing list pgsql-bugs
From | gparc@free.fr |
---|---|
Subject | Re: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition |
Date | |
Msg-id | 621556601.826991929.1711458551936.JavaMail.zimbra@free.fr Whole thread Raw |
In response to | BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition (PG Bug reporting form <noreply@postgresql.org>) |
List | pgsql-bugs |
Hello, is there a week retention before this report is delivered to the mailing list ? Regards Gilles ----- Mail original ----- > De: "PG Bug reporting form" <noreply@postgresql.org> > À: "pgsql-bugs" <pgsql-bugs@lists.postgresql.org> > Cc: "gparc" <gparc@online.fr> > Envoyé: Mercredi 20 Mars 2024 15:29:14 > Objet: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition > The following bug has been logged on the website: > > Bug reference: 18402 > Logged by: Gilles PARC > Email address: gparc@online.fr > PostgreSQL version: 16.2 > Operating system: Linux > Description: > > Example : > > First I create a list partitioned table and fill it > CREATE TABLE master > ( > id integer NOT NULL, > millesime varchar(4) NOT NULL, > CONSTRAINT master_pk PRIMARY KEY (id, millesime) > ) partition by list (millesime); > > CREATE TABLE master_2022 PARTITION OF master FOR VALUES IN ('2022'); > CREATE TABLE master_2023 PARTITION OF master FOR VALUES IN ('2023'); > CREATE TABLE master_2024 PARTITION OF master FOR VALUES IN ('2024'); > CREATE TABLE master_2025 PARTITION OF master FOR VALUES IN ('2025'); > > DO > $do$ > BEGIN > FOR i IN 2022..2025 LOOP > INSERT INTO master VALUES (generate_series(1,100000), i::varchar); > END LOOP; > END > $do$; > > I now list the created indexes: > \diS+ master* > List of relations > Schema | Name | Type | Owner | Table | > Persistence | Access method | Size | Description > --------+------------------+-------------------+----------+-------------+-------------+---------------+---------+------------- > public | master_2022_pkey | index | postgres | master_2022 | > permanent | btree | 3088 kB | > public | master_2023_pkey | index | postgres | master_2023 | > permanent | btree | 3088 kB | > public | master_2024_pkey | index | postgres | master_2024 | > permanent | btree | 3088 kB | > public | master_2025_pkey | index | postgres | master_2025 | > permanent | btree | 3088 kB | > public | master_pk | partitioned index | postgres | master | > permanent | btree | 0 bytes | > (5 rows) > > > Now I prepare a new 2026 partition with correct constraints and indexes and > attach it : > > CREATE TABLE master_2026 (LIKE master INCLUDING DEFAULTS INCLUDING > CONSTRAINTS); > ALTER TABLE master_2026 ADD CONSTRAINT master_2026_ck CHECK (millesime = > '2026'); > DO > $do$ > BEGIN > INSERT INTO master_2026 VALUES (generate_series(1,100000), '2026'); > END > $do$; > CREATE UNIQUE INDEX CONCURRENTLY master_2026_pkey ON master_2026 (id, > millesime); > ALTER TABLE master ATTACH PARTITION master_2026 FOR VALUES IN ('2026'); > ALTER TABLE master_2026 DROP CONSTRAINT master_2026_ck; > > But now, when I list the indexes again, I see that my unique index above is > not considered to use > and a new one is created (master_2026_pkey1). > \diS+ master* > Schema | Name | Type | Owner | Table | > Persistence | Access method | Size | Description > --------+-------------------+-------------------+----------+-------------+-------------+---------------+---------+------------- > public | master_2022_pkey | index | postgres | master_2022 | > permanent | btree | 3088 kB | > public | master_2023_pkey | index | postgres | master_2023 | > permanent | btree | 3088 kB | > public | master_2024_pkey | index | postgres | master_2024 | > permanent | btree | 3088 kB | > public | master_2025_pkey | index | postgres | master_2025 | > permanent | btree | 3088 kB | > public | master_2026_pkey | index | postgres | master_2026 | > permanent | btree | 3104 kB | > public | master_2026_pkey1 | index | postgres | master_2026 | > permanent | btree | 3104 kB | > public | master_pk | partitioned index | postgres | master | > permanent | btree | 0 bytes | > (7 rows) > > why is it so ? > > Bug or Feature ? > Regards > Gilles
pgsql-bugs by date: