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