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

From: Dmitry Koval <d(dot)koval(at)postgrespro(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Invalid index on partitioned table - is this a bug or feature?
Date: 2025-01-22 17:51:38
Message-ID: edab4fa6-9292-4f9a-9d96-3f8e38096832@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Álvaro Herrera 2025-01-22 17:57:11 Re: Invalid index on partitioned table - is this a bug or feature?
Previous Message Paul Ramsey 2025-01-22 17:50:18 Re: Converting pqsignal to void return