Re: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition

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-L18185
[2] https://www.postgresql.org/docs/16/sql-altertable.html

--
Euler Taveira
EDB https://www.enterprisedb.com/