Invalid index on partitioned table - is this a bug or feature?

Started by Dmitry Koval12 months ago3 messages
#1Dmitry Koval
d.koval@postgrespro.ru

Hi!
If you create an index on a partitioned table using a method that uses
pg_dump:

(a) "ALTER TABLE ONLY ... ADD CONSTRAINT .. PRIMARY KEY ..."
or
(b) "CREATE INDEX ... ON ONLY ..."

then the index for the partitioned table is created with the INVALID
flag.This can be verified using an example (on "master" branch):

--------------------------------------
-- The standard way to create indexes.
--
CREATE TABLE t_int (i int PRIMARY KEY, v int, x int) PARTITION BY RANGE (i);
CREATE TABLE t_int_1 PARTITION OF t_int FOR VALUES FROM (1) TO (100);
CREATE INDEX t_int_v ON t_int (v);

-- VALID indexes
--Indexes:
-- "t_int_pkey" PRIMARY KEY, btree (i)
-- "t_int_v" btree (v)
\d+ t_int

DROP TABLE t_int;

---------------------------------
-- pg_dump way to create indexes.
--
CREATE TABLE t_int (i int NOT NULL, v int, x int) PARTITION BY RANGE (i);
CREATE TABLE t_int_1 PARTITION OF t_int FOR VALUES FROM (1) TO (100);

ALTER TABLE ONLY public.t_int ADD CONSTRAINT t_int_pkey PRIMARY KEY (i);
ALTER TABLE ONLY public.t_int_1 ADD CONSTRAINT t_int_1_pkey PRIMARY KEY (i);

CREATE INDEX t_int_v ON ONLY public.t_int USING btree (v);
CREATE INDEX t_int_1_v_idx ON public.t_int_1 USING btree (v);

-- INVALID indexes
--Indexes:
-- "t_int_pkey" PRIMARY KEY, btree (i) INVALID
-- "t_int_v" btree (v) INVALID
\d+ t_int

DROP TABLE t_int;
---------------------------------

I was unable to change the index flag INVALID -> VALID using the REINDEX
command. I understand that setting the INVALID flag might be correct
(see comment above [1]https://github.com/postgres/postgres/blob/master/src/backend/commands/indexcmds.c#L1211). But the INVALID flag without the possibility of
changing to VALID looks strange anyway.
This is also the reason why some functions may fail, for example
RelationGetPrimaryKeyIndex() returns InvalidOid for INVALID PK.

Is this a bug or a feature?
Should this be corrected?

Links.
[1]: https://github.com/postgres/postgres/blob/master/src/backend/commands/indexcmds.c#L1211
https://github.com/postgres/postgres/blob/master/src/backend/commands/indexcmds.c#L1211

--
With best regards,
Dmitry Koval

Postgres Professional: http://postgrespro.com

#2Álvaro Herrera
alvherre@alvh.no-ip.org
In reply to: Dmitry Koval (#1)
Re: Invalid index on partitioned table - is this a bug or feature?

On 2025-Jan-22, Dmitry Koval wrote:

Hi!
If you create an index on a partitioned table using a method that uses
pg_dump:

(a) "ALTER TABLE ONLY ... ADD CONSTRAINT .. PRIMARY KEY ..."
or
(b) "CREATE INDEX ... ON ONLY ..."

then the index for the partitioned table is created with the INVALID
flag.

Yes. You need to attach child indexes on all partitions so that this
index becomes valid.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/

#3Dmitry Koval
d.koval@postgrespro.ru
In reply to: Álvaro Herrera (#2)
Re: Invalid index on partitioned table - is this a bug or feature?

22.01.2025 20:57, Álvaro Herrera пишет:

Yes. You need to attach child indexes on all partitions so that this
index becomes valid.

Thanks!

--
With best regards,
Dmitry Koval

Postgres Professional: http://postgrespro.com