From: Euler Taveira <euler@eulerto.com>
To: gparc@online.fr
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition
Date: Tue, 26 Mar 2024 15:18:49 -0300
Message-ID: <0370353a-56a3-4fb7-aa12-46422d05b2b5@app.fastmail.com> (raw)
In-Reply-To: <18402-23452305346476d6@postgresql.org>
References: <18402-23452305346476d6@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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: euler@eulerto.com, gparc@online.fr, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18402: Attaching a new partition doesn't reuse the prebuilt index on said partition
In-Reply-To: <0370353a-56a3-4fb7-aa12-46422d05b2b5@app.fastmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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