pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition
3+ messages / 3 participants
[nested] [flat]
* BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition
@ 2024-03-20 14:29 PG Bug reporting form <noreply@postgresql.org>
2024-03-26 13:09 ` Re: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition gparc@free.fr
2024-03-26 18:18 ` Re: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition Euler Taveira <euler@eulerto.com>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2024-03-20 14:29 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: gparc@online.fr
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition
2024-03-20 14:29 BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition PG Bug reporting form <noreply@postgresql.org>
@ 2024-03-26 13:09 ` gparc@free.fr
1 sibling, 0 replies; 3+ messages in thread
From: gparc@free.fr @ 2024-03-26 13:09 UTC (permalink / raw)
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition
2024-03-20 14:29 BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition PG Bug reporting form <noreply@postgresql.org>
@ 2024-03-26 18:18 ` Euler Taveira <euler@eulerto.com>
1 sibling, 0 replies; 3+ messages in thread
From: Euler Taveira @ 2024-03-26 18:18 UTC (permalink / raw)
To: gparc@online.fr; pgsql-bugs@lists.postgresql.org
On Wed, Mar 20, 2024, at 11:29 AM, PG Bug reporting form wrote:
> 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');
You don't add a primary key or unique constraint here. Hence, the ALTER TABLE ..
ATTACH PARTITION doesn't consider the master_2026_pkey as a candidate for
primary key index. Add the following command and your index will be used.
ALTER TABLE master_2026
ADD CONSTRAINT master_2026_pk PRIMARY KEY USING INDEX master_2026_pkey;
See [1]. Documentation [2] says
UNIQUE and PRIMARY KEY constraints from the parent table will be created in the
partition, if they don't already exist.
[1] https://github.com/postgres/postgres/blob/REL_16_STABLE/src/backend/commands/tablecmds.c#L18165-L181...
[2] https://www.postgresql.org/docs/16/sql-altertable.html
--
Euler Taveira
EDB https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-03-26 18:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-20 14:29 BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition PG Bug reporting form <noreply@postgresql.org>
2024-03-26 13:09 ` gparc@free.fr
2024-03-26 18:18 ` Euler Taveira <euler@eulerto.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox