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

From: Dmitry Koval <[email-masked]>
Date: Wed, 22 Jan 2025 20:51:38 +0300

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]). 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

-- 
With best regards,
Dmitry Koval

Postgres Professional: http://postgrespro.com